This article is the fifth of a series on how to retrieve and update information stored in databases. It is organized into the following references:

• Navigating thru the Recordset
• Dividing a Recordset into pages

In this section, we look into the ADO properties and methods to let users move around the records in a data source.

Navigating thru a Recordset Run Sample | View Source

This is handy in cases where we need to quickly show the first, next, previous or last items in, say, a products catalogue.

The sample at right illustrates use of the ADO Move, MoveFirst, MoveLast, MoveNext, and MovePrevious methods to position the record pointer of a Recordset based on a given command.

Now let's see how that is done. The script is essentially composed of two parts:

• First is a snippet to open the Recordset

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

'create and open the recordset
set recSet=Server.CreateObject( "ADODB.Recordset" )
strQuery="SELECT ProductCode, ProductType, ProductName,
   ProductDescription, UnitPrice FROM Products"
recSet.Open strQuery, strProvider, adOpenStatic, , adCmdText

Recall that a Recordset by default opens with a forward-only CursorType.

To be able to support both forward and backward scrolling, the Recordset's CursorType must be set to either adOpenKeyset ( 1 ) or adOpenStatic ( 3 ).

Here we use a read-only static cursor, efficient yet still provides the functionality for our purpose. This offers slightly improved performance over adOpenKeyset, and suffices if you need not show any concurrent additions, changes, or deletions by other users.

recSet.Open strQuery, strProvider, adOpenStatic, , adCmdText

• The other part of the code is a set of conditionals to determine which of the "move" buttons in the form made the http request ( as they are actually submit buttons ), then simply call the appropriate move method.

recno=Request.Form( "recno" )

if Request.Form( "cmdnext" )<>"" then
   recSet.Move recno-1
   recSet.MoveNext
if recSet.EOF then recSet.MovePrevious
elseif Request.Form( "cmdback" )<>"" then
   recSet.Move recno-1
   recSet.MovePrevious
if recSet.BOF then recSet.MoveNext
elseif Request.Form( "cmdfirst" )<>"" then
   recSet.MoveFirst
elseif Request.Form( "cmdlast" )<>"" then
   recSet.MoveLast
end if %>

Notice above the declaration of a variable named "recno", to which we assign the value of a form input - Request.Form( "recno" ), the purpose of which is shown in the following.

Below is the HTML, which is likewise divided into two parts:

• A form to contain the Recordset's navigation buttons, with a hidden input named "recno" to store the current AbsolutePosition property of the record set.

<form method="post">
<table width=90% cellspacing=0 cellpadding=3 border=0>
<tr>
   <td><b>Record Number <%= recSet.AbsolutePosition %> of 
      <%= recSet.RecordCount %></b></td>
   <td align="right">
      <input type="hidden" name="recno"
         value="<%= recSet.AbsolutePosition %>">
      <input type="submit" name="cmdfirst" value="First">
      <input type="submit" name="cmdback" value="Back">
      <input type="submit" name="cmdnext" value="Next">
      <input type="submit" name="cmdlast" value="Last"></td>
   </tr>
</table>
</form>

• An HTML Table to render the current record's data.

<table width=90% cellspacing=1 cellpadding=5 border=0>
<col width=35% align="right">
<% for each field in recSet.Fields %>
   <tr valign="top">
      <td><b><%= field.Name %></b></td>
      <td><% if field.Name="UnitPrice" then %>$ <% end if %>
      <%= field.Value %></td>
   </tr>
<% next
recSet.Close
set recSet=Nothing %>
</table>

The example shows how the AbsolutePosition property is used to track the progress of pointer movement across the records of a Recordset.

On each page load, the Recordset's current AbsolutePosition property is stored as the value of the hidden input "recno". When the page initially loads, this property returns 1.

<input type="hidden" name="recno" 
   value="<%= recSet.AbsolutePosition %>">

When the user clicks any of the buttons to move the record pointer, the page actually posts to itself, and the conditionals check which button made the request to the page.

If the request was made from either the cmdfirst or cmdlast buttons, the script simply calls the MoveFirst or MoveLast method, and ADO positions the pointer accordingly.

If the request was made from either the cmdnext or cmdback buttons, the script first does a little calculating.

Every time the page is then called, the hidden input's value is passed along to the current page as Request.Form( "recno" ), which returns the Recordset's AbsolutePosition in the calling page. We tag this value as "recno" in the current page,

recno=Request.Form( "recno" )

to determine how much we need to move the pointer.

Remember that the pointer is always at the first record each time the page loads. So we then call the Move method to position the pointer a certain number ( recno-1 ) from the first record.

recSet.Move recno-1

This essentially repositions the pointer to where it was before any call to move was made. Then we simply call either MoveNext or MovePrevious, and ADO positions the pointer accordingly.

For instance, if Request.Form( "recno" ) returns 11, we move the pointer one less ( recno-1 ) or 10 records from the first, then call MoveNext or MovePrevious as the case may be.

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

The ADO AbsolutePosition property specifies the ordinal position of a Recordset object's current record.

The property sets or returns a Long value ( an integer ) from 1 to the number of records in the Recordset object ( RecordCount ), or returns one of the following constants.

Use the AbsolutePosition property to move to a record based on its ordinal position in the Recordset object, or to determine the ordinal position of the current record. The provider must support the appropriate functionality for this property to be available.

AbsolutePosition is 1-based ( starts counting from 1, in contrast with zero-based, which starts from 0 ) and equals 1 when the current record is the first record in the Recordset.

So with that in mind, we can track where in the Recordset the current pointer is, as shown in the sample. We can also determine the total number of records in the Recordset using the RecordCount property.

Record Number <%= recSet.AbsolutePosition %> of 
   <%= recSet.RecordCount %>

Note that the AbsolutePosition property cannot be used as a surrogate record number. The position of a given record changes when you delete a preceding record.

There is also no assurance that a given record will have the same AbsolutePosition if the Recordset object is requeried or reopened.

For updateable record sets, bookmarks are still the recommended way of retaining and returning to a given position and are the only way of positioning across all types of Recordset objects, as long as the provider supports it.

The ADO CacheSize property indicates the number of records from a Recordset object that are cached locally in memory.

The property sets or returns a Long value that must be greater than 0. Default is 1.

Use the CacheSize property to control how many records the provider keeps in its buffer and how many to retrieve at one time into local memory. For example, if the CacheSize is 10, after first opening the Recordset object, the provider retrieves the first 10 records into local memory.

As you move through the Recordset object, the provider returns the data from the local memory buffer. As soon as you move past the last record in the cache, the provider retrieves the next 10 records from the data source into the cache.

The value of this property can be adjusted during the life of the Recordset object, but changing this value only affects the number of records in the cache after subsequent retrievals from the data source. Changing the property value alone will not change the current contents of the cache.

If there are fewer records to retrieve than CacheSize specifies, the provider returns the remaining records; no error occurs.

A CacheSize setting of zero is not allowed and returns an error.

When you set the AbsolutePosition property explicitly, even if it is to a record in the current cache, ADO reloads the cache with a new group of records starting with the record you specified.

Note that records retrieved from the cache do not reflect concurrent changes that other users make to the source data. To force an update of all the cached data, use the Resync method.

Because cached records are loaded into memory, you should avoid caching more records than is necessary.

The ADO RecordCount property indicates the current number of records in a Recordset object. The property returns an integer value.

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.

Reading the RecordCount property on a closed Recordset causes an error.

If the Recordset object supports approximate positioning or bookmarks - that is, Supports ( adApproxPosition ) or Supports ( adBookmark ) returns true - this value will be the exact number of records in the Recordset regardless of whether it has been fully populated.

If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.

The cursor type of the Recordset object affects whether the number of records can be determined.

The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count, depending on the data source, for a dynamic cursor.

The Recordset.Move method moves the position of the current record in a Recordset object. Syntax is

recordset.Move numRecords, Start

wherein numRecords specifies a signed Long expression ( an integer ) to denote the number of records the current record position moves.

Start is optional and specifies a String or Variant that evaluates to a bookmark. You can also use one of the following BookmarkEnum values:

The Move method is supported on all types of Recordset objects.

If the numRecords argument is greater than zero, the current record position moves forward ( toward the end of the recordset ). If numRecords is less than zero, the current record position moves backward ( toward the beginning of the recordset ).

If the Move call would move the current record position to a point before the first record, ADO sets the current record to the position before the first record in the recordset ( BOF is true ). An attempt to move backward when the BOF property is already true generates an error.

If the Move call would move the current record position to a point after the last record, ADO sets the current record to the position after the last record in the recordset ( EOF is true ). An attempt to move forward when the EOF property is already true generates an error.

Calling the Move method from an empty Recordset object generates an error.

If you pass the Start argument, the move is relative to the record with this bookmark, assuming the Recordset object supports bookmarks. If not specified, the move is relative to the current record.

If you are using the CacheSize property to locally cache records from the provider, passing a numRecords argument that moves the current record position outside the current group of cached records forces ADO to retrieve a new group of records starting from the destination record. The CacheSize property determines the size of the newly retrieved group, and the destination record is the first record retrieved.

If the Recordset object is forward-only, a user can still pass a numRecords argument less than zero as long as the destination is within the current set of cached records. If the Move call would move the current record position to a record before the first cached record, an error will occur.

Thus, you can use a record cache that supports full scrolling over a provider that supports only forward scrolling.

Even if a forward-only Recordset object supports backward moves in this way, calling the MovePrevious method on any forward-only Recordset object still generates an error.

As their names imply, these methods move to the first, last, next, or previous record in a specified Recordset object and makes that record the current record.

recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious}

Use the MoveFirst method to move the current record position to the first record in the Recordset. The Recordset object must support bookmarks or backward cursor movement; otherwise, the method call will generate an error.

Use the MoveLast method to move the current record position to the last record in the Recordset.

Use the MoveNext method to move the current record position one record forward ( toward the bottom of the Recordset ). If the last record is the current record and you call the MoveNext method, ADO sets the current record to the position after the last record in the Recordset ( EOF is true ). An attempt to move forward when the EOF property is already true generates an error.

To prevent that, we check for EOF in our calls to MoveNext.

if recSet.EOF then recSet.MovePrevious

Use the MovePrevious method to move the current record position one record backward ( toward the top of the recordset ). The Recordset object must support bookmarks or backward cursor movement; otherwise, the method call will generate an error.

If the first record is the current record and you call the MovePrevious method, ADO sets the current record to the position before the first record in the recordset ( BOF is true ). An attempt to move backward when the BOF property is already true generates an error.

Likewise, to prevent that, we check for BOF in our calls to MovePrevious.

if recSet.BOF then recSet.MoveNext

If the Recordset object does not support either bookmarks or backward cursor movement, the MovePrevious method will generate an error.

In the same way, if the recordset is forward-only and you want to support both forward and backward scrolling, you can use the CacheSize property to create a record cache that will support backward cursor movement through the Move method.

You can call the MoveFirst method in a forward-only Recordset object; doing so may cause the provider to re-execute the command that generated the Recordset object.