Take the 2-minute tour ×

I am having trouble retrieving results from my datareader in visual studio 2008. I have several stored Procs in the same database. I am able to retrieve values from those that dont receive input parameters. However, when i use the executreReader() method on a stored proc with input parameters i get an empty datareader. Upon examining the result collection the message "IEnumerable returned no results" appears. I am baffled as I can execute the stored procs within sql server and return result sets. I was previously able to retrieve rows from these stored procedures within Visual Studio but apparently it just stopped working one day.

I have tried using a dataadapter to fill a dataset with my results and using the executereader() method to get a sqldatareader and Still I get no results. No exceptions are thrown either. My parameters are all named properly but I should be able to call these stored procs with no parameters and have that return an unfiltered result set. The code im currently using is the following:

string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"]

SqlConnection connCactus = new SqlConnection(connStr);
SqlCommand cmdPopulateFilterDropDowns = new SqlCommand( "dbo.MyStoredProc",
SqlDataReader rdrFilterSearch = null;
cmdPopulateFilterDropDowns.CommandType = CommandType.StoredProcedure;

rdrFilterSearch = cmdPopulateFilterDropDowns

return (rdrFilterSearch);

Please Help!

share|improve this question
If you put a breakpoint on the executereader line and step over, is the reader still empty? –  JamesSugrue Dec 16 '08 at 0:22
Also, it might help if you post the SP too? –  JamesSugrue Dec 16 '08 at 0:23
If I put a breakpoint and hover over the datareader, expanding the resultsview it says "ENumeration yielded no results" –  Anonymous Dec 16 '08 at 0:29
Just to cover all the bases, you don't have any funny permissions set up on the DB, do you (although that should throw an error anyway)? Also - you are connecting to the right DB in your connection string in Web.Config? Pays to check the obvious stuff first. –  Phil.Wheeler Dec 16 '08 at 0:31
Yes the permissions are fine. Im using the same connection string for the entire web app. Some sp's return results others do not. I can retrieve results from all stored procs that do not accept input parameters and I cannot retrieve results from those that do. –  Anonymous Dec 16 '08 at 0:56

8 Answers 8

Are you ever adding the parameters to the SqlCommand's Parameter collection? You mentioned that the ones that aren't working are the ones that take input params, yet in your code you don't have anything like this:

share|improve this answer

Run a SQL server trace against the server you think you're executing your command against. What's actually being sent to the server? I think you'll find your smoking-gun clue there.

BFree has a good point, to check AddWtihValue() vs. Add(). Also make sure that if you're explicitly instantiating SqlParameter objects, the same problem you'll have with .Add() can occur. Note that there's a design flaw withe Parameters.Add() when you pass values of (int)0 into it -- they get taken as an enum instead of a value...

share|improve this answer

this may seem obvious but please tell me you are calling the .Read() method? I hate to pose such a question but sometimes it is the most obvious things we forget when we are driving ourselves mad over an issue.

share|improve this answer
The above code returns a reader, I use the .read() method in the calling code. –  Anonymous Dec 16 '08 at 18:00

Stupid mistake on my part. After a few hours truggling with this i realized that the stored procedures I was using return multiple result sets of which the first result set was always empty. Hence the lack of results.

share|improve this answer
Sometimes, the stupid answers are the best ones. I was having a similar problem. –  Xynariz Feb 10 at 21:38

I had a similar, but not identical, problem that was driving me nuts, so I'll throw this in in case it helps someone along the line.

I could run the SQL, or call a stored procedure with the same SQL, on SQL Server using Management Studio, and get back a populated resultset. But when I used the identical SQL inline, or called the sproc containing it, from my Visual Studio code for an ASP.Net web application project, I got an empty DataReader.

How could the same identical SQL return a resultset in Management Studio, but an empty DataReader in Visual Studio? The answer was that I was running as a different user with different permissions in the two tools. In Management Studio, I was running as a developer in a specific SQL role with a lot of permissions. But in Visual Studio, I was running as a service account which had not been given permissions on the tables I was querying.

Actually, my problem was one step more complicated. Everything was fine until I altered the SQL/sproc and added a table JOIN to one more table, and that table lacked the necessary service account permissions. But the principle is the same: examine all of the permissions for the user making the database call.

share|improve this answer

Setting the parameterdirection on the sqlParam should solve the problem.

mySqlParam.Direction = ParameterDirection.ReturnValue;

If you expect more than one value to be returned, just add the param and set it's direction:

    SqlParameter mySqlParam = new SqlParameter();
    mySqlParam.ParameterName = "@ID";
    mySqlParam.SqlDbType = SqlDbType.int;
    mySqlParam.Direction = ParameterDirection.ReturnValue;

    SqlParameter mySqlParam = new SqlParameter();
    mySqlParam.ParameterName = "@Name";
    mySqlParam.SqlDbType = SqlDbType.NVarChar;
    mySqlParam.Direction = ParameterDirection.ReturnValue;

    SqlParameter mySqlParam = new SqlParameter();
    mySqlParam.ParameterName = "@Address";
    mySqlParam.SqlDbType = SqlDbType.NVarChar;
    mySqlParam.Direction = ParameterDirection.ReturnValue;

The mySqlParam.ParameterName doesn't have to be exactly like the name in the stored procedure.

You can then read the values like this (not complete example):


the value in dataReader[""].ToString(); has to match the coloumname from the stored procedure.

share|improve this answer

When do you get the "IEnumerable returned no results" error? Could you show an example of how you're accessing the DataReader?

What happens if you set a breakpoint before the return, and run


in the immediate window?

share|improve this answer



Happy coding

share|improve this answer

Your Answer

