Returning recordsets from a stored procedure

[stjulian]

(IIS 6.0, SQL Server 2000)

I have a block of code that populates a recordset from a stored procedure.
The problem is, the recordset seems to be forward only (which would be OK),
but can't jump with the "AbsolutePage" property (which isn't OK)

How do I define the recordset that will allow this?

Julian


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

Set rsSearchM = Server.CreateObject ("ADODB.Recordset")
rsSearchM.ActiveConnection = conn
rsSearchM.CursorLocation = 3

' write to database using spr
With adocmd

set param = .CreateParameter("@criteria", adVarchar, adParamInput, 50,
"red" )
.parameters.append param

set param = .createparameter("@numrows", adInteger, adParamOutput)
.parameters.append param

On Error Resume Next

errorstring = ""
errornumber = 0
valid = 0

rsSearchM.open = .execute

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

.execute
numrowsM = .Parameters("@numrows").Value

End With
On Error GoTo 0
set adocmd = nothing

pagesize = 1000
rsSearchM.PageSize = pagesize
numpages = rsSearchM.PageCount

rsSearchM.MoveFirst
rsSearchM.AbsolutePage = currpage

----------------------------------------------------------------------------------

 

[Bob Barrows [MVP]]

(IIS 6.0, SQL Server 2000)

I have a block of code that populates a recordset from a stored
procedure. The problem is, the recordset seems to be forward only (which
would
be OK), but can't jump with the "AbsolutePage" property (which isn't OK)

How do I define the recordset that will allow this?

Julian


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

Set rsSearchM = Server.CreateObject ("ADODB.Recordset")
rsSearchM.ActiveConnection = conn
rsSearchM.CursorLocation = 3

I'm assuming that's adUseClient (too lazy to look it up). Why not use the
constant instead of the "3"?

' write to database using spr
With adocmd

set param = .CreateParameter("@criteria", adVarchar, adParamInput,
50, "red" )
.parameters.append param

set param = .createparameter("@numrows", adInteger, adParamOutput)
.parameters.append param

On Error Resume Next

errorstring = ""
errornumber = 0
valid = 0

rsSearchM.open = .execute

That is very strange syntax! It really works? Anyways, the Execute method
creates a NEW recordset with default properties. I'm not sure why that Open
method is allowing the recordset returned from Execute to be assigned to
rsSearchM, but given your results, it certainly seems to be. The correct
syntax of course is:

rs.Open adocmd

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

.execute

You're executing it twice?? There is no reason to do that. The open
statement above should provide a client-side static cursor. As long as you
have a SET NOCOUNT STATEMENT in your stored procedure, and the procedure
only returns a single recordset, you should be able to read the output
parameter at this point without calliing .Execute again.

numrowsM = .Parameters("@numrows").Value

Curious. I would not have expected you to be able to get the parameter
value at this point without consuming the recordset returned by the
procedure. You've confirmed that you are actually getting a value at this
point?

 

[Bob Barrows [MVP]]

(IIS 6.0, SQL Server 2000)

I have a block of code that populates a recordset from a stored
procedure. The problem is, the recordset seems to be forward only (which
would
be OK), but can't jump with the "AbsolutePage" property (which isn't OK)

PS. You should investigate more efficient methods to accomplish
record-paging. Here are a couple examples:
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
http://www.adopenstatic.com/experiments/recordsetpaging.asp

 

[stjulian]

Yup.

I do a lot of copying and pasting from my old code. Just got used to the 3 I
guess.

I have even done a simple

SET rsSearchM = .execute

That doesn't give the desired results either.

There is a SET NOCOUNT ON statement before the SELECT that makes the
recordset in the SP

The @numrows comes from

SET @numrows = @@ROWCOUNT

and the value is not returned without the second ".execute" stement.

Any other ideas?

This should be easier for me. But, thank You for all the help.

Julian

 

[Bob Barrows [MVP]]

Yup.

I do a lot of copying and pasting from my old code. Just got used to
the 3 I guess.

I have even done a simple

SET rsSearchM = .execute

That doesn't give the desired results either.

It shouldn't. Did you read my initial reply carefully? Execute always
returns a server-side forwardonly recordset (unless you have earlier
modified the cursorlocation property of the Connection object to make
adUseClient the default cursor location)

SQL Server will not return the output parameter value until all recordsets
returned by the procedure have been consumed. With a server-side cursor,
that means you need to move to the last record in the recordset, or close
it, before attempting to read the output parameter value. With a client-side
cursor, the entire resultset is sent to the client (ADO) and put into a
client-side cursor, so the output parameter value should be available
immediately after the recordset.open statement.

There is a SET NOCOUNT ON statement before the SELECT that makes the
recordset in the SP

Actually, SET NOCOUNT ON should be the first statement in the procedure, but
it shouldn't matter where it is in the procedure.

The @numrows comes from

SET @numrows = @@ROWCOUNT

and the value is not returned without the second ".execute" stement.

I'm amazed that it is returned. When you execute the procedure again, it
does all the actions that it performed the first time, including sending the
resultset again. Hmm, perhaps ADO consumes those results when you don't
assign them to a recordset variable ...

Any other ideas?

I'm still unclear as to what I am trying to fix. Did you revise your code
per my initial reply? Specifically, eliminate both Execute statements and
use

rs.Open adocmd

If so, show me your new code as well as the text of the stored procedure.