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?
