473,544 Members | 2,231 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 "AbsolutePa ge" property (which isn't OK)

How do I define the recordset that will allow this?

Julian
----------------------------------------------------------------------------------
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_search ALL"

Set rsSearchM = Server.CreateOb ject ("ADODB.Records et")
rsSearchM.Activ eConnection = conn
rsSearchM.Curso rLocation = 3

' write to database using spr
With adocmd

set param = .CreateParamete r("@criteria" , adVarchar, adParamInput, 50,
"red" )
.parameters.app end param

set param = .createparamete r("@numrows", adInteger, adParamOutput)
.parameters.app end 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.Redire ct(HomePath & "/error.asp?eid=u nknown")
End If

.execute
numrowsM = .Parameters("@n umrows").Value

End With
On Error GoTo 0
set adocmd = nothing

pagesize = 1000
rsSearchM.PageS ize = pagesize
numpages = rsSearchM.PageC ount

rsSearchM.MoveF irst
rsSearchM.Absol utePage = currpage

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

Jul 27 '07 #1
4 17375
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 "AbsolutePa ge" property (which isn't OK)

How do I define the recordset that will allow this?

Julian
----------------------------------------------------------------------------------
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_search ALL"

Set rsSearchM = Server.CreateOb ject ("ADODB.Records et")
rsSearchM.Activ eConnection = conn
rsSearchM.Curso rLocation = 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 = .CreateParamete r("@criteria" , adVarchar, adParamInput,
50, "red" )
.parameters.app end param

set param = .createparamete r("@numrows", adInteger, adParamOutput)
.parameters.app end 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.Redire ct(HomePath & "/error.asp?eid=u nknown")
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("@n umrows").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?


--
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"
Jul 27 '07 #2
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 "AbsolutePa ge" 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...recordset.html
http://www.adopenstatic.com/experime...dsetpaging.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"
Jul 27 '07 #3
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]" <re******@NOyah oo.SPAMcomwrote in message
news:eO******** ******@TK2MSFTN GP06.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 "AbsolutePa ge" property (which isn't OK)

How do I define the recordset that will allow this?

Julian
----------------------------------------------------------------------------------
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_search ALL"

Set rsSearchM = Server.CreateOb ject ("ADODB.Records et")
rsSearchM.Activ eConnection = conn
rsSearchM.Curso rLocation = 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 = .CreateParamete r("@criteria" , adVarchar, adParamInput,
50, "red" )
.parameters.app end param

set param = .createparamete r("@numrows", adInteger, adParamOutput)
.parameters.app end 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.Redire ct(HomePath & "/error.asp?eid=u nknown")
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("@n umrows").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?


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

Jul 27 '07 #4
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"
Jul 27 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5309
by: Eli Sidwell | last post by:
Trying to return a Recordset to an ASP and the Recordset is empty. The StorredProc works in the query analyzer and it even works from a quick VB app that I wrote to test it. The storedproc that I am using is fairly complex (creates some temporary tables and populates them with 'Insert Into Select ...', but the during testing the only Select...
4
10880
by: Michael Trosen | last post by:
Hi Everyone, I hope someone can help, I'm pretty new to pro*c programming. I have the following application setup: a pro*c program calls a stored procedure and recieves a cursor back: the cursor is defined as: SQL_CURSOR delpt_cursor
7
16488
by: (Pete Cresswell) | last post by:
I posted this in the MS Access group, but no luck. ------------------------------------------ I've got another stored procedure in the same app that returns multiple recordsets and the code works. But now I've written another SP and the code traps out with the 3251 message. The SP is writing two recordsets. When I run the SP in Query...
5
2596
by: rhungund | last post by:
Hi All. My question is this. I have a complex stored procedure in SQL Server which works fine when I run it in Query Analyzer. However, when I call it within my ASP script, it returns nothing, and sometimes locks up. If I change my script to call other existing stored procedures it works fine. It's just with this particular stored proc. ...
16
5697
by: Randy Harris | last post by:
I was inspired by the recent discussion of returning multiple recordsets to ADO from a stored procedure. (Amazed is probably more accurate). I asked about how to accomplish same with Oracle and got a nudge in the right direction from Mr. Kreft. I promised to provide details once working, so here it is. The code is shown below. My next...
4
4343
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier Architecture. The Stored Procedures are used through TableAdaptors, which in turn are used by Class Files. I wish to be able to return this new ID...
1
11290
by: stjulian | last post by:
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...
3
2561
by: Homer J. Simpson | last post by:
I have the following stored procedure: ALTER PROCEDURE . AS BEGIN SET NOCOUNT ON; SELECT COUNT(*) FROM QUICKNOTES END ....and the following data source in my .aspx file:
3
10018
by: Dooza | last post by:
Hi there, I have a Stored Procedure in SQL 2000 that takes 10 input paremeters, and outputs 11 recordsets, not rows, but 11 different SELECT statements. The purpose of this is to for a menu system in a store. If all the inputs are NULL, then just the top level categories are output in the first recordset. The ID of each category will then...
0
7783
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7392
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7720
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5931
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5309
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3430
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3422
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1850
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.