Returning recordsets from a stored procedure

(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 = "

Error Number " & Err.Number & "
" & "The Error
Code was: " & Err.Description & "

Re: Returning recordsets from a stored procedure

stjulian wrote:
> (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 = "

Error Number " & Err.Number & "
" & "The
> Error Code was: " & Err.Description & "

Re: Returning recordsets from a stored procedure

stjulian wrote:
> (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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Returning recordsets from a stored procedure

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]" wrote in message
news:eO5XYlE0HHA.4476@TK2MSFTNGP06.phx.gbl...
> stjulian wrote:
>> (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 = "

Error Number " & Err.Number & "
" & "The
>> Error Code was: " & Err.Description & "

Re: Returning recordsets from a stored procedure

stjulian wrote:
> 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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"