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("SPN ame", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure
With sqlCmd
.Parameters.Add ("@ReturnValue" , SqlDbType.Int, 4).Direction
= ParameterDirect ion.ReturnValue
.Parameters.Add ("@UserID", SqlDbType.Int). Value =
CInt(Request.Qu eryString("UID" ))
End With
sqlConn.Open()
sqlReader = sqlCmd.ExecuteR eader
While (sqlReader.Read )
lblOutput.Text = "Total Count: " & sqlReader.GetIn t32(0) &
"<br>"
End While
sqlReader.Close ()
sqlCmd.ExecuteN onQuery()
lblOutput.Text += "Count: " & sqlCmd.Paramete rs(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**@rediffmai l.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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**@rediffmai l.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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.Read er)
Response.Write( sqlReader.GetVa lue(0)
End While
But how do I get the result of the second query in the ASPX page?
