In this section, we look into the ADO properties and methods that are useful in scenarios wherein you want to allow the user to page through data, viewing a given number of records at a time.

Paging thru a Recordset Run Sample | View Source

The sample at right uses the AbsolutePage, PageCount, and PageSize properties to display records from the Products table five records per page.

Now let's see how that is done.

The script is essentially divided into the following:

1. Open the Recordset

<% 'specify the provider
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbtutor.mdb"

'create a recordset object
set recSet=Server.CreateObject( "ADODB.Recordset" )

'specify the source and open the recordset
strQuery="SELECT ProductID, ProductType, ProductName,
   ProductDescription, UnitPrice FROM products"
recSet.Open strQuery, strProvider, adOpenStatic, , adCmdText

Note again for efficiency, we open a read-only static cursor.

2. Specify the number of records per page and determine the current page

'specify the number of records per page
recSet.PageSize=5

'get current page
if not isEmpty( Request.QueryString( "pageno" ) ) then
   pageno=Request.QueryString( "pageno" ) 
else 
   pageno=1 
end if %>

3. Begin the HTML Table and markup the field headers

<table width=90% cellspacing=1 cellpadding=5 border=0>
<!-- begin column headers -->
<tr>
   <th>Product ID</th>
   <th>Product Type</th>
   <th>Product Name</th>
   <th>Product Description</th>
   <th>Unit Price</th>
</tr>

4. Set the AbsolutePage property using the "pageno" value, taken from the Request.QueryString

recSet.AbsolutePage=pageno

5. Cycle thru the page set to display five records at a time.

for intRecord=1 to recSet.PageSize %>
   <tr>
      <td><%= recSet( "ProductID" )%></td>
      <td><%= recSet( "ProductType" )%></td>
      <td><%= recSet( "ProductName" )%></td>
      <td><%= recSet( "ProductDescription" )%></td>
      <td align="right">$ <%= recSet( "UnitPrice" )%></td>
   </tr>

   <% ' increment record position with MoveNext method
   recSet.MoveNext
   if recSet.EOF then exit for

   <!-- next record=next row -->
next %>
</table>

Now let's explore the ADO properties and methods that apply to this procedure.

The Recordset.PageSize property indicates how many records constitute one page in the Recordset.

The property sets or returns a Long value, indicating how many records are on a page. Default is 10.

Use the PageSize property to determine how many records make up a logical page of data. Establishing a page size allows you to use the AbsolutePage property to move to the first record of a particular page.

This property can be set at any time, and its value will be used for calculating the location of the first record of a particular page.

The Recordset.AbsolutePage property specifies in which page the current record resides.

The property sets or returns a Long value from 1 to the number of pages in the Recordset object's PageCount, or returns one of the following constants.

Use the AbsolutePage property to identify the page number on which the current record is located. Use the PageSize property to logically divide the Recordset object into a series of pages, each of which has the number of records equal to PageSize ( except for the last page, which may have fewer records ). The provider must support the appropriate functionality for this property to be available.

Like the AbsolutePosition property, AbsolutePage is 1-based and equals 1 when the current record is the first record in the Recordset. Set this property to move to the first record of a particular page.

You can obtain the total number of pages from the PageCount property.

The Recordset.PageCount property indicates how many pages of data the Recordset object contains. It returns a Long value that is basically the result of RecordCount divided by PageSize. Any remainder counts as another page.

Pages are groups of records whose size equals the PageSize property setting. Use the PageCount property to determine how many pages of data are in the Recordset object.

Even if the last page is incomplete, because there are fewer records than the PageSize value, it counts as an additional page in the PageCount value.

If the Recordset object does not support this property, the value will be -1 to indicate that the PageCount is indeterminable.

In the paging sample, you will find the following snippet that outputs the links ( Page 1 | Page 2 . . . ) using a loop from 1 to PageCount, and inserting the loop counter in the href querystring.

<% links="<p>"
for page=1 to recSet.PageCount
   links=links & _
      "<a href='page.asp?pageno=" & page & _
      "'>Page "&page&"</a>"
   if page < recSet.PageCount then links=links & " | "
next
links=links & "</p>"
response.write links

Each pass in the loop appends an anchor <a> element along with the querystring for each page in the set,

<a href='page.asp?pageno=" & page & "'>

and writes the entire string when the loop is done. This loop counter is what we use to set the AbolutePage property:

recSet.AbsolutePage=pageno

This affords a flexible solution for providing page navigation links, without having to explicitly specify the target and text for each hyperlink. Also, because this method depends on PageCount, it adjusts as needed when the number of records per page ( PageSize ) is changed.

After rendering the page, close the record set.

' close the recordset and flush object from memory
recSet.Close
set recSet=Nothing %>

Well, that's about it for this session. Hopefully at least, you've gained insight on how to move thru a Recordset and retrieve data sets as separate pages.

Again, of course, the samples can further be improved, because in the samples we need to redundantly open and close the database.

Ideally when navigating or paging thru a Recordset, the connection should only be opened once, and closed when the operation is done, either thru a session- or application-wide event.

This way, you may not have to use the Move method at all. If your code provides for storing the Recordset object as a Session variable, that is then used to refer to the Recordset each time the page loads, the Recordset's AbsolutePosition is retained across pages while in a Session. Hence, you can explicitly call MoveNext or MovePrevious to position the pointer accordingly.

But do note that session state is only maintained for browsers that support cookies, or for browsers in which the user has not turned off cookie support.

Also, be aware that a session state expires; that is, a session can remain idle for only a specified amount of time before the server terminates it automatically. The default is 20 minutes. You may want to adjust this setting using Session.TimeOut.

The Timeout property specifies the timeout period assigned to the Session object for the current application. If the user does not refresh or request a page within the timeout period, the session ends. If you try to use a session variable when a session has ended, a run-time error occurs.

For more information on using the Session object, see Managing Sessions in the ASP documentation.

Also, when moving or paging thru record sets, you may want to sort the records to display in a particular order. You can do so by including an ORDER BY clause in the SQL statement that opens the Recordset.

strQuery="SELECT ProductCode, ProductType, ProductName, 
   ProductDescription, UnitPrice FROM Products 
   ORDER BY ProductName"

And . . . before you go, here are some things you might want to keep in mind.

The tables below summarize how the ADO methods and properties discussed above behave with each different CursorType setting.

Availability of ADO Recordset methods covered in this article:

Availability of ADO Recordset properties covered in this article:

Note that the AbsolutePosition and AbsolutePage properties are write-only when ADO is used with version 1.0 of the Microsoft OLE DB Provider for ODBC.

Next in this series:

• ADO Primer: Part VI
  • Introduction to Queries
  • Structured Query Language ( SQL ) Basics
  • Optimizing Queries

If you haven't done so, I suggest you start with:

• ADO Primer: Part I
  • Basic database concepts
  • Connecting to a database
  • The Recordset object

ADO Primer: Structured Query Language ( SQL ) Basics