469,951 Members | 2,564 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

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
Nov 1 '06 #1
1 10927
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.
Nov 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Adam Knight | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.