469,333 Members | 4,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,333 developers. It's quick & easy.

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

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

Nov 17 '05 #1
1 2565
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.

--
Carsten Thomsen
Enterprise Development with Visual Studio .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
"Neo Chou" <ne*****@hotmail.com> wrote in message
news:eM**************@tk2msftngp13.phx.gbl...
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

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dino L. | last post: by
5 posts views Thread by Rob Wire | last post: by
2 posts views Thread by Rob Meade | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.