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.CreateOb ject("ADODB.Rec ordset")
Set rs2 = Server.CreateOb ject("ADODB.Rec ordset")

Set adocmd = Server.CreateOb ject("ADODB.Com mand")
adocmd.CommandT imeout = 120
adocmd.ActiveCo nnection = conn
adocmd.CommandT ype = adCmdStoredProc
adocmd.CommandT ext = "dbo.spr_MyProc edure"

With adocmd

set param = .createparamete r("@searchstrin g", adVarchar, adParamInput, 40,
guid)
.parameters.app end param
set param = .createparamete r("@numvalues ", adInteger, adParamOutput)
.parameters.app end param

On Error Resume Next

errorstring = ""
errornumber = 0

set rs1 = .execute
set rs2 = rs1.NextRecords et

'-- 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.Redire ct(HomePath & "/error.asp?eid=u nknown")
End If

numvalues = .Parameters("@n umvalues").Valu e

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.CreateOb ject("ADODB.Rec ordset")
Set rs2 = Server.CreateOb ject("ADODB.Rec ordset")

Set adocmd = Server.CreateOb ject("ADODB.Com mand")
adocmd.CommandT imeout = 120
adocmd.ActiveCo nnection = conn
adocmd.CommandT ype = adCmdStoredProc
adocmd.CommandT ext = "dbo.spr_MyProc edure"

With adocmd

set param = .createparamete r("@searchstrin g", adVarchar,
adParamInput, 40, guid)
.parameters.app end param
set param = .createparamete r("@numvalues ", adInteger, adParamOutput)
.parameters.app end param

On Error Resume Next

errorstring = ""
errornumber = 0

set rs1 = .execute
set rs2 = rs1.NextRecords et

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.Nextrec ordset

--
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.