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

[stjulian]

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

[Bob Barrows [MVP]]

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.