By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,017 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,017 IT Pros & Developers. It's quick & easy.

SqlDataReader & Stored Procedure

P: n/a
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?

Oct 19 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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!
*************************************************
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?

Oct 19 '06 #2

P: n/a
I know it's early, but you didn't say how :P

dr.NextResult()

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

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamMwrote in
message news:en**************@TK2MSFTNGP02.phx.gbl...
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!
*************************************************
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
>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?


Oct 19 '06 #3

P: n/a
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?
Cowboy (Gregory A. Beamer) wrote:
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!
*************************************************
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?
Oct 19 '06 #4

P: n/a
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

<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?

Oct 19 '06 #5

P: n/a
while (dr.Read())
{
//do stuff
}

dr.NextResult();

while (dr.Read())
{
}

--
http://www.openmymind.net/
http://www.fuelindustries.com/
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?
Cowboy (Gregory A. Beamer) wrote:
>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!
*********************************************** **
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googleg roups.com...
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?

Oct 19 '06 #6

P: n/a
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.

Karl Seguin [MVP] wrote:
while (dr.Read())
{
//do stuff
}

dr.NextResult();

while (dr.Read())
{
}

--
http://www.openmymind.net/
http://www.fuelindustries.com/
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?
Cowboy (Gregory A. Beamer) wrote:
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!
*************************************************
<rn**@rediffmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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?
Oct 19 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.