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

Discussion in 'ASP .Net' started by Neo Chou, Aug 3, 2003.

  1. Neo Chou

    Neo Chou Guest

    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
     
    Neo Chou, Aug 3, 2003
    #1
    1. Advertising

  2. Neo Chou

    CT Guest

    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" <> wrote in message
    news:...
    > 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, Aug 3, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul
    Replies:
    1
    Views:
    379
    =?Utf-8?B?UmFtaXJvIENhbGRlcm9u?=
    Jan 19, 2004
  2. Lacka
    Replies:
    2
    Views:
    860
    Lacka
    Dec 31, 2004
  3. Mike P
    Replies:
    0
    Views:
    3,311
    Mike P
    Jun 19, 2006
  4. Replies:
    6
    Views:
    356
  5. SethM
    Replies:
    6
    Views:
    154
    Bob Barrows [MVP]
    Sep 22, 2008
Loading...

Share This Page