Published: Saturday, December 12, 1998
Paging through Database Results N Records at a
Time
By Scott Mitchell
One of ASP's great assests is that it can connect to a database on the
server and report the results of a query to a user. If the queries are
small, the page loads quickly, and there isn't too much data to wade
through. However, if there are hundreds of thousdands of results being
returned by the database, attempting to display all that data on one page
would not only be difficult for the reader to utilize, but would also be
very slow to execute!
There is a solution, however. Using ADO you can page through data N
records at a time. The records are retrieved N at a time, so the query
time will be shortened considerably, plus the data will be easier to
examine and use. We will be using ClientSide cursors (meaning that the
recordset cursor will be stored on the client's side, as opposed on the
server (which is the default), the CacheSize property (to specify how many
records to cache on the cursor), and the paging properties: AbsolutePage,
PageSize, and PageCount.
Let's start by looking at some code. Remember to include ADOVBS.inc. (If
you don't have it, it can be downloaded here. This program assumes that
ADOVBS is located in your web application's root directory and that the
file is named "thisfile.asp".)
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include virtual="/ADOVBS.inc"-->
<%
'Set how many records per page we want
Const NumPerPage = 10
'Retrieve what page we're currently on
Dim CurPage
If Request.QueryString("CurPage") = "" then
CurPage = 1 'We're on the first page
Else
CurPage = Request.QueryString("CurPage")
End If
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=MyDB"
'Explicitly Create a recordset object
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
'Set the cursor location property
rs.CursorLocation = adUseClient
'Set the cache size = to the # of records/page
rs.CacheSize = NumPerPage
'Open our recordset
Dim strSQL
strSQL = "SELECT Name,Salary FROM Employee ORDER BY Name"
rs.Open strSQL, Conn
rs.MoveFirst
rs.PageSize = NumPerPage
'Get the max number of pages
Dim TotalPages
TotalPages = rs.PageCount
'Set the absolute page
rs.AbsolutePage = CurPage
'Counting variable for our recordset
Dim count
%>
<HTML>
<BODY>
<B>Name - Salary</B><BR>
<%
'Set Count equal to zero
Count = 0
Do While Not rs.EOF And Count < rs.PageSize
Response.Write(rs("Name") & " - " & rs("Salary") & "<BR>")
Count = Count + 1
rs.MoveNext
Loop
'Print out the current page # / total pages
Response.Write("Page " & CurPage & " of " & TotalPages & "<P>")
'Display Next / Prev buttons
if CurPage > 1 then
'We are not at the beginning, show the prev button
Response.Write("<INPUT TYPE=BUTTON VALUE=PREV
ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage -
1 & "';"">")
End If
if CInt(CurPage) <> CInt(TotalPages) then
'We are not at the end, show a next button
Response.Write("<INPUT TYPE=BUTTON VALUE=NEXT
ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage +
1 & "';"">")
End If
%>
</BODY>
</HTML>
|
Let's quickly go over some of those ADO properties we used. Setting
PageSize to an integer value tells ADO how many records per page you are
wanting. PageCount, then, tells you how many pages total there are.
Obviously you need to set PageSize before PageCount will yield a
sensible result. (This is not strictly true, since PageSize defaults to
10, but always be sure to set PageSize explicitly if you plan on using
paging!) AbsolutePage is a value you can set to tell ADO to get a certain
page. So, if you have pages x through y with N records per page, you
could say, AbsolutePage = M, and the recordset cursor will start at the
M*Nth record (assuming M < y).
The CacheSize tells ADO to cache N number of rows on the cursor. Since we
chose to have 10 records per page, I set the cache size to 10. ADO will
grab the first 10 records, and cache those. It won't hit the database
until you request any past the cached limit. Since we are only displaying
10 records per page, we are only having one quick database hit per page.
The caching property is vital to the performance of the script above.
Hopefully you now understand how to page through data using ADO / ASP.
Be forewarned, you can only page through certain cursor types. For
example, you cannot page through a forward-only cursor (which is the
default for server side cursors). Paging allows a quick and visually
appealing way for you to display results from a query to your users!
Happy Programming!
By Scott Mitchell
