Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reader.NextResult() not advancing to next resultset

Posted on 2006-06-06
9
Medium Priority
?
1,524 Views
Last Modified: 2012-08-13
Reader.NextResult() not advancing to next resultset. I have a 2 resultsets in a stored proc. When I run the stored proc in Query Analyzer I get 2 tables with results, one with 10 rows the other with 1 row. When I get to reader.nextresult the .hasrows property is false. I don't know what to do to get this working. Can you assist?

SqlDataReader lReader = lCommand.ExecuteReader();

                DataTable table1 = new DataTable();
                DataTable table2 = new DataTable();

                 while (lReader.Read())
                    {
                        table1.Load(lReader);

                        // read next resultset

                        while (lReader.NextResult())
                        {

                            table2.Load(lReader);
                        }

                    }
         
0
Comment
Question by:sutorius
  • 4
  • 3
  • 2
9 Comments
 
LVL 4

Expert Comment

by:RJeyaPrakash
ID: 16850652
hai


 is there any method called Load for Datatable?????



0
 
LVL 4

Assisted Solution

by:RJeyaPrakash
RJeyaPrakash earned 300 total points
ID: 16850702
try change yr coding like


while (lReader.Read())
{
  table1.Load(lReader);

  // read next resultset
}
if (lReader.NextResult() )
{
while (lReader.Read())
{
       table2.Load(lReader);
}
}



0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16851527
1) .NET version?

2) If you are using DataTables, then why are you messing with data readers.  It seems like unnecessary overhead and confusion.

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:sutorius
ID: 16851688
Hey there,

.net version 2.0 (version 2 of our website)

I decided to use sqldatasource instead of objectdatasource because of the paging, sorting AND I am allowing the user to select the number of rows they want returned. Additionally, we had a stored proc that we used for paging and sorting in .net 1.1 (version 1 of our website). By adding an additional paramater to the sp we were returning an additional resultset.
0
 
LVL 2

Author Comment

by:sutorius
ID: 16851735
fyi

here's is the header on the sp. By all means if you think I could be doing this with less mess please advise.

CREATE     procedure sp_web_edit_manager (
 @tabselected varchar(5) -- which tab was selected
,@stateselected varchar(5) = null  -- which state was selected, optional
,@nbr_edits int = 0 --Number of edits per page
,@page int = 1 --Which page should be returned
,@nbr_pages int =1 output --Let the app know how many pages there are
,@sort_option varchar(255) = 'edit_mast.edit_id' --How should we sort: 1=edit_id, 2=edit_name, 3=error_msg
,@desc tinyint = 0 --What is the sort order 0 = ascending, 1 = descending
,@total_edits int = 0 output --How many edits are there for this tab
,@user_id varchar(20) = 'Install'
) as
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1200 total points
ID: 16851832
Here is an example that you can modify and test:

SqlDataAdapter adapter = new SqlDataAdapter("stored procedure name", "connection string");
DataSet ds = new DataSet();
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
adapter.SelectCommand.Parameters.Add("@tabselected", "1");
adapter.SelectCommand.Parameters.Add("@stateselected", "");
adapter.SelectCommand.Parameters.Add("@nbr_edits", 0);
adapter.SelectCommand.Parameters.Add("@page", 1);
adapter.SelectCommand.Parameters.Add("@nbr_pages", 1);
adapter.SelectCommand.Parameters.Add("@sort_option", "edit_mast.edit_id" );
adapter.SelectCommand.Parameters.Add("@desc", 0 );
adapter.SelectCommand.Parameters.Add("@total_edits", 0 );
adapter.SelectCommand.Parameters.Add("@user_id", "Install");
adapter.Fill(ds);

Bob
0
 
LVL 2

Author Comment

by:sutorius
ID: 16852555
Bob,

I have the databound set, OnDataBound="EditMgrGridView_DataBound" and it is firing and I can step through the code above, I have DataSource set DataSourceID = "SqlDataSource1" and I am using the code above. I am using the following SqlDataSource, <asp:SqlDataSource DataSourceMode=DataSet ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

I am getting an empty grid. What step am I missing?



0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16852669
Aah yes, you did say 2.0 didn't you.

Here is an example of using an SqlDataSource with a GridView:

SqlDataSource Control in ASP.NET 2.0 for Rapid Application Development (RAD)
http://davidhayden.com/blog/dave/archive/2005/05/23/1046.aspx

<asp:GridView
    ID="GridView1"
    runat="server"
    DataSourceID="SqlDataSource1"
    AllowPaging = "True"
    AllowSorting = "True"
   
    ...
   
asp:GridView>


<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString =     "<%$ ConnectionStrings:ConnectionString" %>"
    SelectCommand = "Select * from Product"
asp:SqlDataSource>

Here is an example for a stored procedure:

asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString =      "<%$ ConnectionStrings:ConnectionString" %>"
    SelectCommand = "GetProducts"
    SelectCommandType = "StoredProcedure"
asp:SqlDataSource>

Bob
0
 
LVL 2

Author Comment

by:sutorius
ID: 16864342
I had a talk with my dba and it turns out that he had the sp wrong. It was not 2 resultsets. Thanks for the input guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question