|
 |
asp_databases thread: Paging Stored Procedure recordset
Message #1 by "Owen Mortensen" <ojm@a...> on Wed, 30 May 2001 18:00:16 -0700
|
|
I'm wondering if anyone knows if it's possible to paginate a recordset
that's returned from a stored procedure. In my first attempt, I'm getting
an error when I try to set the absolute page on the recordset. I'm using
SQL Server 7.0 database.
Here's some code....
==================================
dim objComm
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnectString
objComm.CommandText = "spStoredProc"
objComm.CommandType = adCmdStoredProc
objComm.Parameters.Append = objComm.CreateParameter("@Param1", adInteger)
objComm.Parameters("@Param1")=22
set objRS = objComm.Execute
objRS.CacheSize = PageSize
objRS.PageSize = PageSize
objRS.AbsolutePage = PageNumber
==================================
The last line is where I get an error. Says something about the recordset
being from a live feed...?
TIA,
Owen
Message #2 by "Ken Schaefer" <ken@a...> on Thu, 31 May 2001 18:59:47 +1000
|
|
You can't do it that way because your are using an adOpenForwardOnly cursor,
which does not support .PageSize, nor .AbsolutePage.
You have options. If you want to stick to adOpenForwardOnly & using the
proc, then see here:
http://www.adOpenStatic.com/experiments/recordsetpaging.asp - there is also
code there on how to open a recordset using an adOpenStatic, which does
support .AbsolutePage
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Owen Mortensen" <ojm@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, May 31, 2001 11:00 AM
Subject: [asp_databases] Paging Stored Procedure recordset
: I'm wondering if anyone knows if it's possible to paginate a recordset
: that's returned from a stored procedure. In my first attempt, I'm getting
: an error when I try to set the absolute page on the recordset. I'm using
: SQL Server 7.0 database.
:
: Here's some code....
:
: ==================================
: dim objComm
:
: set objComm = Server.CreateObject("ADODB.Command")
: objComm.ActiveConnection = strConnectString
: objComm.CommandText = "spStoredProc"
: objComm.CommandType = adCmdStoredProc
:
: objComm.Parameters.Append = objComm.CreateParameter("@Param1", adInteger)
: objComm.Parameters("@Param1")=22
:
: set objRS = objComm.Execute
:
: objRS.CacheSize = PageSize
: objRS.PageSize = PageSize
: objRS.AbsolutePage = PageNumber
: ==================================
:
: The last line is where I get an error. Says something about the recordset
: being from a live feed...?
:
: TIA,
: Owen
:
:
: ---
: * Fast, Full-Featured Microsoft® Excel Web Reports & Charts!
: A breakthrough in high performance Web application development,
SoftArtisans
: ExcelWriter 1.1 supports native Excel charting, image insertion, and
: advanced functions & formatting. One click generates presentation-quality
: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the
: Excel Object. Several editions, including ExcelWriterFREE, are available.
:
URL:<http://adtracking.wrox.com/track.asp?x=p2p%2Fe%2Fd%26w%2Fsoftart&url=ht
tp://www.softartisans.com/softartisans/excelwriter.html>
$subst('Email.Unsub')
Message #3 by "Owen Mortensen" <ojm@a...> on Thu, 31 May 2001 09:58:33 -0700
|
|
I'm not stuck on adOpenForwardOnly. That's just the default. Which cursor
should I use for paging and how and where do I specify it.
Thanks,
Owen
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, May 31, 2001 2:00 AM
To: ASP Databases
Subject: [asp_databases] Re: Paging Stored Procedure recordset
You can't do it that way because your are using an adOpenForwardOnly cursor,
which does not support .PageSize, nor .AbsolutePage.
You have options. If you want to stick to adOpenForwardOnly & using the
proc, then see here:
http://www.adOpenStatic.com/experiments/recordsetpaging.asp - there is also
code there on how to open a recordset using an adOpenStatic, which does
support .AbsolutePage
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Owen Mortensen" <ojm@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, May 31, 2001 11:00 AM
Subject: [asp_databases] Paging Stored Procedure recordset
: I'm wondering if anyone knows if it's possible to paginate a recordset
: that's returned from a stored procedure. In my first attempt, I'm getting
: an error when I try to set the absolute page on the recordset. I'm using
: SQL Server 7.0 database.
:
: Here's some code....
:
: ==================================
: dim objComm
:
: set objComm = Server.CreateObject("ADODB.Command")
: objComm.ActiveConnection = strConnectString
: objComm.CommandText = "spStoredProc"
: objComm.CommandType = adCmdStoredProc
:
: objComm.Parameters.Append = objComm.CreateParameter("@Param1", adInteger)
: objComm.Parameters("@Param1")=22
:
: set objRS = objComm.Execute
:
: objRS.CacheSize = PageSize
: objRS.PageSize = PageSize
: objRS.AbsolutePage = PageNumber
: ==================================
:
: The last line is where I get an error. Says something about the recordset
: being from a live feed...?
:
: TIA,
: Owen
:
:
:
Message #4 by "Owen Mortensen" <ojm@a...> on Thu, 31 May 2001 11:46:24 -0700
|
|
OK. Here's what I've tried:
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>DB Test</TITLE>
</HEAD>
<BODY>
Database Test
<%
dim objRS
dim strSQL
dim strConn
dim dbTotalPages
strConn = "Provider=SQLOLEDB;User ID=userID;password=password;Initial
Catalog=AM;Data Source=DB1"
set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "spFindNews 74, '<=', '05/31/2001'"
objRS.CacheSize = 20
objRS.PageSize = 20
objRS.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
do while not objRS.EOF
response.write objRS("headline") & "<BR>"
response.write objRS("brief") & "<BR>"
response.write objrs("start_date") & "<BR><BR>"
objRS.MoveNext
loop
dbTotalPages = objRS.PageCount ' count the pages
objRS.AbsolutePage = 3 ' set the page number
response.write "Total Pages: " & dbTotalPages & "<BR>"
%>
</BODY>
</HTML>
The stored procedure "spFindNews" is fine and is returning all the records
as requested. My problem is that the paging is not working. The line
"objRS.AbsolutePage = 3" gets an error saying : "ADODB.Recordset error
'800a0cb3'
The operation requested by the application is not supported by the provider.
/dbtest.asp, line 36"
Any further ideas?
Owen
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, May 31, 2001 2:00 AM
To: ASP Databases
Subject: [asp_databases] Re: Paging Stored Procedure recordset
You can't do it that way because your are using an adOpenForwardOnly cursor,
which does not support .PageSize, nor .AbsolutePage.
You have options. If you want to stick to adOpenForwardOnly & using the
proc, then see here:
http://www.adOpenStatic.com/experiments/recordsetpaging.asp - there is also
code there on how to open a recordset using an adOpenStatic, which does
support .AbsolutePage
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Owen Mortensen" <ojm@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, May 31, 2001 11:00 AM
Subject: [asp_databases] Paging Stored Procedure recordset
: I'm wondering if anyone knows if it's possible to paginate a recordset
: that's returned from a stored procedure. In my first attempt, I'm getting
: an error when I try to set the absolute page on the recordset. I'm using
: SQL Server 7.0 database.
:
: Here's some code....
:
: ==================================
: dim objComm
:
: set objComm = Server.CreateObject("ADODB.Command")
: objComm.ActiveConnection = strConnectString
: objComm.CommandText = "spStoredProc"
: objComm.CommandType = adCmdStoredProc
:
: objComm.Parameters.Append = objComm.CreateParameter("@Param1", adInteger)
: objComm.Parameters("@Param1")=22
:
: set objRS = objComm.Execute
:
: objRS.CacheSize = PageSize
: objRS.PageSize = PageSize
: objRS.AbsolutePage = PageNumber
: ==================================
:
: The last line is where I get an error. Says something about the recordset
: being from a live feed...?
:
: TIA,
: Owen
|
|
|
View Cart
