|
Recordset paging is the process of breaking up a recordset into multiple "pages" of information
for display. You've probably noticed that most well designed sites implement this feature,
allowing you to navigate through a recordset to view a certain number of records at a time.
Breaking the recordset data into pages allows for easy browsing by the user of the application
and it also adds a professional touch. When I first started encountering this feature
on the web a few years back, I immediately began working on how to implement it for myself.
The leisure of being able to casually browse and navigate through the results of a query
was wonderfull and a heck of a lot better than having a few hundred records dumped on my
browser all at once.
ADO 2.0 makes this a very easy feature to implement.
Recordset Properties
There are five different recordset properties that are used to implement recordset paging:
- CursorLocation - In order to use recordset paging, you will need to set this value to "adUseClient".
- PageSize - Sets the number of records that the recordset will display on each page. A neat use of this is to allow the client to configure this setting so that they can tailor the recordset to their tastes.
- PageCount - After setting the recordset's PageSize, you can read this property to check how many pages are in the recordset.
- AbsolutePage - This property tells you what page you are on. You can set this property on the fly to jump to a different page in the recordset.
- AbsolutePosition - This property will tell you what record you are on. You can read this property, or write to it to jump to a specific record.
|
Recordset Paging in Action
The most common use of recordset paging is to break up the results of a query submitted to a
search engine. I have put together a simple example using the biblio Access database that accompanies
Visual Studio 6.0. The sample presents the user with a form that allows a user to submit a year
in order to return a listing of the titles of books published during that year.
This example requires that you have at least the following items installed on your computer:
- Personal Web Server
- Front Page Server Extensions
- ADO 2.0+ object Library
- biblio.mdb - One of the sample Access databases that comes with Visual Studio 6.0.
First we will build the search page. The search page is a simple HTML page that contains a
single text box in which you can enter a year and a submit button to post the form's input to
the ASP page that actually performs the search and returns the result.
Book Search Page Code Listing
<BODY>
<!-- Create an HTML form that posts its results to bk_searchresult.asp -->
<form name=frmYear id=frmYear action=bk_searchresult.asp method=post>
<h1>Book Search</h1>
<h4>Enter a year to return a listing of books published for that year.</h4>
<p><b>Year: </b><input type=text size=20 id=txtYear name=txtYear></p>
<input type=submit name=btnSubmit id=btnSubmit>
</form>
</BODY>
|
The next step is to build the ASP page that will perform the search based on the value posted
from the search page and return the results to the browser.
Book Search Results Page Code Listing
<%
' Declare all of the variables that will be used in the page.
Dim objConn ' ADO Connection Object
Dim objRst ' ADO Recordset Object
Dim strYear ' The year that we are searching for.
Dim Sql ' Our SQL statement
Dim intPageCount ' The number of pages in the recordset.
Dim intRecordCount ' The number of records in the recordset.
Dim intPage ' The current page that we are on.
Dim intRecord ' Counter used to iterate through the recordset.
Dim intStart ' The record that we are starting on.
Dim intFinish ' The record that we are finishing on.
' Check to see if there is value in the NAV querystring. If there
' is, we know that the client is using the Next and/or Prev hyperlinks
' to navigate the recordset.
If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If
' Create the Connection Object
Set objConn = Server.CreateObject("ADODB.Connection")
' Set the connection string. I am connecting to the biblio database
' using the OLEDB Provider for version 3.51 of the Jet Engine. The
' Data Source is set to path of the database on my computer. You will,
' of course, set this to the appropiate path for your computer.
objConn.ConnectionString = "Provider=Microsoft.jet.OLEDB.3.51;" & _
"Data Source = E:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb"
objConn.Open
' Check to see if this page is loading as the result of a new search parameter
' being submitted. If so, set the strYear varable equal to the form submission.
' If not, set it equal to the value of the YEAR querystring.
If Request.Form("txtYear") <> "" Then
strYear = Request.Form("txtYear")
Else
strYear = Request.QueryString("YEAR")
End If
' Query the book titles from the Titles table where they match
' the strYear variable.
Sql = "Select Title From Titles Where Titles.[Year Published] = " & strYear
' Create you Recordset Object
Set objRst = Server.CreateObject("ADODB.Recordset")
' The CursorLocation and the CursorType must be set as they are here
' in order for Recordset Paging to work properly.
objRst.CursorLocation = 3 'adUseClient
objRst.CursorType = 3 'adOpenStatic
objRst.ActiveConnection = objConn
' Open the recordset.
objRst.Open Sql
' Set the PageSize, CacheSize, and populate the intPageCount and
' intRecordCount variables.
objRst.PageSize = 10
' The cachesize property sets the number of records that will be cached
' locally in memory.
objRst.CacheSize = objRst.PageSize
intPageCount = objRst.PageCount
intRecordCount = objRst.RecordCount
' Now you must double check to make sure that you are not before the start
' or beyond end of the recordset. If you are beyond the end, set
' the current page equal to the last page of the recordset. If you are
' before the start, set the current page equal to the start of the recordset.
If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
If CInt(intPage) <= 0 Then intPage = 1
' Make sure that the recordset is not empty. If it is not, then set the
' AbsolutePage property and populate the intStart and the intFinish variables.
If intRecordCount > 0 Then
objRst.AbsolutePage = intPage
intStart = objRst.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (objRst.PageSize - 1)
End if
End If
%>
<BODY>
<h1>Book Search Results</h1>
<h4>Your search for books published in <%=strYear%>
returned <%=intRecordCount%> records.</h4>
<%If intRecordCount > 0 Then
' Display the record that you are starting on and the record
' that you are finishing on for this page by writing out the
' values in the intStart and the intFinish variables.
%>
<h4>You are now viewing records
<%=intStart%> through <%=intFinish%>.</h4>
<table border=1>
<tr><td><font size=+1><b>Book Title</b></font></td></tr>
<%
' Iterate through the recordset until we reach the end of the page
' or the last record in the recordset.
For intRecord = 1 to objRst.PageSize
Response.Write "<tr><td>" & objRst("Title") & "</td></tr>"
objRst.MoveNext
If objRst.EOF Then Exit for
Next
%>
<tr><td align=right>
<%
' Check to see if the current page is greater than the first page
' in the recordset. If it is, then add a "Previous" link.
If cInt(intPage) > 1 Then
%>
<a href="bk_searchresult.asp?NAV=<%=intPage - 1%>&YEAR=<%=strYear%>"><< Prev</a>
<%End IF%>
<%
' Check to see if the current page is less than the last page
' in the recordset. If it is, then add a "Next" link.
If cInt(intPage) < cInt(intPageCount) Then
%>
<a href="bk_searchresult.asp?NAV=<%=intPage + 1%>&YEAR=<%=strYear%>">Next >></a>
<%End If%>
</td></tr>
</table>
<%End If%>
<a href="bk_search.htm">NEW SEARCH</a>
|
Summary
There are many more things that you could do to expand upon this simple example.
You could allow your users to set the number of records that would be returned with
each page by allowing them to pass a value to the PageSize property. In order to
exand your navigation features, you could offer links that load the first page
or last page of the recordset. You could even allow the users the ability to jump
to specific pages. Once you have a good understanding of the basics, the sky is the
limit for what you can do with recordset paging.
Support Materials
A copy of this article and the scripts described within is available for download here.
You might also find our Database Paging sample helpful.
|