Using a stored procedure to return multiple recordsets with .Filter support

Discussion in 'ASP General' started by stjulian, Nov 1, 2006.

  1. stjulian

    stjulian Guest

    I have a stored procedure which returns 2 tables and 1 output value. I want
    the first table to be assigned to rs1 and the second to rs2. However when I
    run this, I get the following error as I begin to refer to the rs2 recordset
    (Do while not rs2.eof). I have even used 2 ".execute" statements in the code
    below, but the code assigns the first recordset twice.

    Microsoft VBScript runtime error '800a01a8'
    Object required: 'rs2'

    /output.asp, line 166


    How can I assign the two tables to the appropriate recordsets? Also, I need
    the .MoveFirst method support, and if possible, the .Filter method.


    The code for calling the Stored Procedure is below

    ' --------------------------------------------------------------------------------
    Set rs1 = Server.CreateObject("ADODB.Recordset")
    Set rs2 = Server.CreateObject("ADODB.Recordset")

    Set adocmd = Server.CreateObject("ADODB.Command")
    adocmd.CommandTimeout = 120
    adocmd.ActiveConnection = conn
    adocmd.CommandType = adCmdStoredProc
    adocmd.CommandText = "dbo.spr_MyProcedure"

    With adocmd

    set param = .createparameter("@searchstring", adVarchar, adParamInput, 40,
    guid)
    .parameters.append param
    set param = .createparameter("@numvalues", adInteger, adParamOutput)
    .parameters.append param

    On Error Resume Next

    errorstring = ""
    errornumber = 0

    set rs1 = .execute
    set rs2 = rs1.NextRecordset

    '-- check the return value
    If Err.Number <> 0 Then
    errorstring = "<p>Error Number " & Err.Number & "<br>" & "The Error Code
    was: " & Err.Description & "</p>"
    errornumber = Err.Number
    Response.Write(errorstring)
    Response.Redirect(HomePath & "/error.asp?eid=unknown")
    End If

    numvalues = .Parameters("@numvalues").Value

    End With
    On Error GoTo 0
    set adocmd = nothing
    ' --------------------------------------------------------------------------------



    Thank You,
    Julian
     
    stjulian, Nov 1, 2006
    #1
    1. Advertising

  2. stjulian wrote:
    > I have a stored procedure which returns 2 tables and 1 output value.
    > I want
    > the first table to be assigned to rs1 and the second to rs2. However
    > when I
    > run this, I get the following error as I begin to refer to the rs2
    > recordset (Do while not rs2.eof). I have even used 2 ".execute"
    > statements in the code below, but the code assigns the first
    > recordset twice.
    >
    > Microsoft VBScript runtime error '800a01a8'
    > Object required: 'rs2'
    >
    > /output.asp, line 166
    >
    >
    > How can I assign the two tables to the appropriate recordsets? Also,
    > I need
    > the .MoveFirst method support, and if possible, the .Filter method.
    >
    >
    > The code for calling the Stored Procedure is below
    >
    > '
    > ----------------------------------------------------------------------

    ----------
    > Set rs1 = Server.CreateObject("ADODB.Recordset")
    > Set rs2 = Server.CreateObject("ADODB.Recordset")
    >
    > Set adocmd = Server.CreateObject("ADODB.Command")
    > adocmd.CommandTimeout = 120
    > adocmd.ActiveConnection = conn
    > adocmd.CommandType = adCmdStoredProc
    > adocmd.CommandText = "dbo.spr_MyProcedure"
    >
    > With adocmd
    >
    > set param = .createparameter("@searchstring", adVarchar,
    > adParamInput, 40, guid)
    > .parameters.append param
    > set param = .createparameter("@numvalues", adInteger, adParamOutput)
    > .parameters.append param
    >
    > On Error Resume Next
    >
    > errorstring = ""
    > errornumber = 0
    >
    > set rs1 = .execute
    > set rs2 = rs1.NextRecordset
    >


    Firstly, in order to avoid extra resultsets containing "x rows effected"
    messages, make sure you include the line "SET NOCOUNT ON" in the body of
    the stored procedure.

    Next, if you need bookmark support, you need to set the recordset's
    cursortype to either static or dynamic and use Open rather than Execute
    to open them:

    rs1.CursorType = adopenStatic
    rs1.Open adocmd
    Set rs2=rs1.Nextrecordset



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 1, 2006
    #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. Mike P
    Replies:
    0
    Views:
    3,375
    Mike P
    Jun 19, 2006
  2. JB
    Replies:
    0
    Views:
    303
  3. Darren

    Newbie using multiple recordsets

    Darren, Apr 3, 2004, in forum: ASP General
    Replies:
    4
    Views:
    147
    Bob Barrows
    Apr 3, 2004
  4. stjulian
    Replies:
    4
    Views:
    174
    Bob Barrows [MVP]
    Jul 27, 2007
  5. Dooza
    Replies:
    3
    Views:
    208
    Dooza
    Jun 5, 2008
Loading...

Share This Page