If I have a stored procedure that returns two separate non related data result sets can I bind the second result set to a separate controls in ASP.net?

If I have a stored procedure that returns two separate non related data result sets can I bind the second result set to a separate controls in ASP.net?

I know the first result set can be bound.

For example, if I have a stored procedure that basically returned to SQL queries like this?
SELECT uid, Name, Price FROM PRODUCTS
SELECT COUNT(*) AS VENDORCOUNT FROM VENDORS

Returns something like:

uid      Name      Price
1      Product1      5
2      Product2      6
3      Product3      7

VENDORCOUNT
10

If I bind
Repeater1.DataSource = objCommand.ExecuteReader()
        Repeater1.DataBind()

Then on my asp page and asp:repeater control I can access that data with something like:
<%#DataBinder.Eval(Container.DataItem,"uid") %>
<%#DataBinder.Eval(Container.DataItem,"Name") %>
<%#DataBinder.Eval(Container.DataItem,"Price") %>

That gives me the data from the first query.

Can I bind the VENDORCOUNT data to a label? If so, how do I get that data exactly?

Thank you,
Shawn
smowerAsked:
Who is Participating?
 
Robert SchuttSoftware EngineerCommented:
Oh wow, never realized that it was a 'new' feature. Well that part is actually just my way to set up a quick test, the business part should work I guess.

You do have to put the reader in a variable. The way you tried it might not be working because you're making a new reader on which you call NextResult. I'll see if I can reproduce but in the mean-time could you try this:
        objConnect.Open()
        Dim objReader As SqlDataReader = objCommand.ExecuteReader()
        Repeater1.DataSource = objReader
        Repeater1.DataBind()
        If objReader.NextResult() Then
            Repeater2.DataSource = objReader
            Repeater2.DataBind()
        End If
        objReader.Close()
        objConnect.Close()

Open in new window

edit: I forgot setting the 2nd DataSource...
0
 
Robert SchuttSoftware EngineerCommented:
Store the reader in a local variable and use NextResult after the first DataBind():
        Using objConnection As New SqlConnection("server=.\sqlxe2012;database=ee;trusted_connection=true;")
            objConnection.Open()
            Using objCommand As New SqlCommand("spQ_28648431", objConnection)
                objCommand.CommandType = CommandType.StoredProcedure
                Using objReader As SqlDataReader = objCommand.ExecuteReader()
                    Repeater1.DataSource = objReader
                    Repeater1.DataBind()

                    objReader.NextResult()
                    Repeater2.DataSource = objReader
                    Repeater2.DataBind()
                End Using
            End Using
        End Using

Open in new window

This assumes your label is in another Repeater even though only 1 record is returned, you need some container.
<asp:Repeater ID="Repeater2" runat="server">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem,"vendorcount") %>
</ItemTemplate>
</asp:Repeater>

Open in new window

Another option would be to simply read the count and set the label text (instead of lines 10-11, after the NextResult):
If objReader.Read Then Label1.Text = objReader(0)

Open in new window

In that case you would have something like this in your aspx:
Vendor count: <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

Open in new window

0
 
smowerAuthor Commented:
Thank you!  I tried adapting your example but my VS complained about the Using statement. The site I am working on is based on asp.net 1.1. Apparently the using statement isn't supported on asp.net 1.1.

I tried to update my code like this:

'My current Working Code snippet
objConnect.Open()
        Repeater1.DataSource = objCommand.ExecuteReader()<wbr ></wbr>
        Repeater1.DataBind()       
        objConnect.Close()

'Tried changing to this
objConnect.Open()
        Repeater1.DataSource = objCommand.ExecuteReader()<wbr ></wbr>
        Repeater1.DataBind()      
objCommand.ExecuteReader().NextResult()
        Repeater2.DataBind() 
        objConnect.Close()

Open in new window


but I got the following error:
ExecuteReader requires an open and available Connection. The connection's current state is Open, Fetching.

Thank you,
Shawn
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Robert SchuttSoftware EngineerCommented:
BTW: I wasn't able to reproduce with .NET 1.1 (don't have it installed anywhere) but the error I got is similar and just means that you can't open 2 readers on 1 connection at the same time. Since you actually only need 1 reader that should not be a further problem with the code I posted.
0
 
smowerAuthor Commented:
Thank you! It didn't like SqlDataReader but worked when I put OleDbDataReader like this and then it worked!

objConnect.Open()

        Dim objReader As OleDbDataReader = objCommand.ExecuteReader()
        Repeater1.DataSource = objReader
        Repeater1.DataBind()
        If objReader.NextResult() Then
            Repeater2.DataSource = objReader
            Repeater2.DataBind()
        End If
        objReader.Close()

Open in new window


So in terms of efficiency and speed, is it better to have one stored procedure return multiple datasets and retrieve the data using this method than having separate stored procedures and calling them individually or is it sixes?

Thank you,
Shawn
0
 
Robert SchuttSoftware EngineerCommented:
I'm not sure if that can be answered for each and every situation but I think it's probably best to do what you did and hit the database only once in a situation like this.
0
 
smowerAuthor Commented:
Thank you!
0
 
smowerAuthor Commented:
Thank you. So in terms of efficiency and speed, is it better to have one stored procedure return multiple datasets and retrieve the data using this method of next result than having separate stored procedures and calling them individually or is it about the same performance wise?

Thank you,
Shawn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.