SqlDataReader & Stored Procedure

[rn5a]

Suppose a SQL Server 2005 stored procedure looks like this:

ALTER PROCEDURE SPName
@UserID int

SELECT COUNT(*) FROM Table1 WHERE UserID = @UserID
SELECT COUNT(*) FROM Table1

In the ASPX page, I can get the result of the first query in the above
SP using

While (sqlReader.Reader)
Response.Write(sqlReader.GetValue(0)
End While

But how do I get the result of the second query in the ASPX page?

 

[Cowboy \(Gregory A. Beamer\)]

You advance to the next query in the reader. But, note, that this can only
be done after you are finished with the first result set. If you want to
simply chain the two answers, you can use a temp table approach.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

 

[Karl Seguin [MVP]]

I know it's early, but you didn't say how

dr.NextResult()

and as Greg pointed out, you need to be done going through the first one.

Karl

 

[rn5a]

That's exactly where I am getting stuck. How do I find out that the
first query has been finished with & that I can now move on to the
second query? Using

SqlDataReader1.NextResult

as Karl pointed out?

 

[Kuldeep]

How critical is it to you to use a SQL DataReader ?
I mean, the problem would not arise if you are catching the entire result
into a DataSet.
Would'nt a simple objDs.Tables[0] and objDs.Tables[1] for the two different
queries solve the problem?

Just a thought!
Kuldeep

 

[Karl Seguin [MVP]]

while (dr.Read())
{
//do stuff
}

dr.NextResult();

while (dr.Read())
{
}

 

[rn5a]

This is what I finally did & it works fine:

Stored Procedure:
----------------

ALTER PROCEDURE dbo.SPName
@UserID int
AS
DECLARE
@count int

SELECT COUNT(*) AS TotalCount FROM Table1
SET @count = (SELECT COUNT(*) FROM Table1 WHERE UserID = @UserID)
RETURN @count

ASPX Code:
---------

<script runat="server">
Sub Page_Load(ByVal obj As Object, ByVal ea As EventArgs)
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection
Dim sqlReader As SqlDataReader

sqlConn = New SqlConnection("......")
sqlCmd = New SqlCommand("SPName", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

With sqlCmd
.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction
= ParameterDirection.ReturnValue
.Parameters.Add("@UserID", SqlDbType.Int).Value =
CInt(Request.QueryString("UID"))
End With

sqlConn.Open()
sqlReader = sqlCmd.ExecuteReader

While (sqlReader.Read)
lblOutput.Text = "Total Count: " & sqlReader.GetInt32(0) &
"<br>"
End While
sqlReader.Close()

sqlCmd.ExecuteNonQuery()
lblOutput.Text += "Count: " & sqlCmd.Parameters(0).Value

sqlConn.Close()
End Sub
</script>
<form runat="server">
<asp:Label ID="lblOutput" runat="server"/>
</form>

I will definitely try it out using your approach, Karl.