Datareader returns no results in VS yet Stored Procedure returns multiple result sets in Sql Server

Question

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"]
                                     .ConnectionString;

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

connCactus.Open();
rdrFilterSearch = cmdPopulateFilterDropDowns
                      .ExecuteReader(CommandBehavior.CloseConnection);

return (rdrFilterSearch);

Please Help!

Answer 1

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

rdrFilterSearch.GetString(0);

in the immediate window?

Answer 2

I dont get an error, but If I Place a breakpoint and look at the autos window I can drill down into the reader once it is returned and when expanding the results view I get the IEnumerable yielded no results" Message. If I don't trace into it it simply returns no results and Im left with an empty grid.

Setting the breakpoint and adding the code above (rdrfilterSearch.GetString(0) throws an exception"Attempting to read data when no data is present"

Answer 3

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:

cmdPopulateFilterDropDowns.Parameters.AddWithValue(...);

Answer 4

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...

Answer 5

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.

Answer 6

Interestingly enough when I run a sql trace the stored proc never gets executed. Instead it calls exec sp_reset_connection. Yet for another stored proc the tI hit right before that(one without input parameters) it shows that stored procedure in the trace. Im very baffled.

I have not tried addwithvalue() I will try that but it looks like im not returning anything from the sql side so im not sure it will make a difference.

As for adding parameter objects, I ahve 2 versions of the same method one where I added parameter objects and then the code above. Neither returns results.

Answer 7

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.

Answer 8

We are facing the same problem.

As you said, we also have multiple result sets. But the first resultset is not empty.

Still we are not able to read the data from the reader. As it returns Reader.Read() = false;

Please provide any input to resolve this.

Answer 9

So... what's the solution???

Answer 10

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):

int.Parse(dataReader["ID"]); dataReader["name"].ToString(); dataReader["address"].ToString();

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

Answer 11

I have simillar prob. Make sure in your store proc if you return any computed column, then use isnull for null value.

Answer 12

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.

Answer 13

I seem to have the same problem. On the page I have a listbox which is populated with SqlDataSource with select command = SelectCommand="SELECT [id], [ProjectType] FROM [ProjectType]" The list box is populated no problem. The list box allows for multiple selection. Then I select multiple items, and click Filter button to populate a gridview. I have a code that checks for what's selected and builds a query for the database. The query looks something like that: SELECT id, ProjectId, ProjectNumber, ProgramID, TypeID, YearID FROM ProjectGrouping WHERE TypeID IN ('72a7ca82-df2f-4dd1-bc36-5b4b0e0efe5e')

I check in the profiler, the query shows up in there. When I execute the same statement in the database, it returns results, however the datareader is empty. I have integrated security set to true, and the loginId is a sysadmin.

Here is the code: " SelectCommand="SELECT [id], [ProjectType] FROM [ProjectTypeNew]">

This listbox is populated.

Now this code returns no records:

string strSQLconnection = "Data Source=yana-eee\sqlexpress;Initial Catalog=GTAA;Integrated Security=True";

SqlConnection sqlConnection = new SqlConnection(strSQLconnection);

SqlCommand sqlCommand = new SqlCommand(filterSql, sqlConnection);

sqlConnection.Open();

SqlDataReader reader = sqlCommand.ExecuteReader();

ProjectGroupDG.DataSource = reader;

ProjectGroupDG.DataBind();

---

where filterSql = SELECT id, ProjectId, ProjectNumber, ProgramID, TypeID, YearID FROM ProjectGrouping WHERE TypeID IN ('72a7ca82-df2f-4dd1-bc36-5b4b0e0efe5e')

Please help !!