Stored Procedure has both return value and data set (SqlDataReader)

[Neo Chou]

Greetings!

I met the same question as in ADO a few months ago.

I'm working on MS SQL Server 2000. I have a stored procedure that returns a
return value as well as a record set (by "select" statement). Below is my
ASP code:

<%
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
....
OBJdbConn.CursorLocation = adUseClient
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = OBJdbCONN
....
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = cmd.Execute
ResultType = cmd("@ResultType")
If ResultType = 0 Then
Do Until rs.EOF
....
rs.MoveNext
Loop
rs.Close
End If
%>

I can get the return value and record set at the same time, by specifying
"CursorLocation" as "adUseClient". Now I want to re-write it in
ASP.NET/ADO.NET. I'm using SqlConnection, SqlCommand and SqlDataReader. I
can only get the return value after the SqlConnection is closed, but at that
time SqlDataReader is no longer available. Is there any option in
ADO.NET/SqlClient to specify "CursorLocation" as ADO?

Thanks in advance!

Neo

 

[CT]

I'm afraid there isn't a CursorLocation option with ADO.NET. So, what you
can do is to have the return value returned as the first result set,
followed by the data from the SELECT statement, and use the NextResult
method of the DataReader to move from the return value once read, to the
actual result set. If modifying the stored procedure isn't an option you
will need to go with the DataAdapter and DataSet.