Transact SQL Other Articles Software Reviews
Easy ADO Recordset Paging
This article demonstrates the use of ADO Recordset paging. Paging is invaluable for splitting up the results of database queries into manageable screens of data.
The following code will retrieve a Recordset from a data source, then format it in a table.
The code should work on your system with only minor modifications. The only things you should need to be changing are the lines that define oConnection (the ADO connection) and the sSQLStatement SQL query. The oConnection variable should be a connection string. The sSQLStatement should be a SQL query.
The code has been tested with SQL Server 7.0 and Access 2000 databases, although it should work with other databases supported by ADO.
How the code sample works
Obtaining field names
A little known feature of Recordsets is the ability to easily obtain a list of field names contained within that Recordset. This saves a lot of time, and means that one results page could potentially be used for the results from different tables. Field names are obtained from the Fields collection of the Recordset object. Each Field in this collection has a corresponding Name property which contains the field name. The following code is used to retrieve a list of field names:
For Each oTableField In oRecordSet.Fields
Using the ADO Recordset GetString method
The conventional method of rendering a Recordset in HTML is to loop through the records, writing a new table row for each record.
An alternative method is to use the ADO Recordsets GetString method. This method also offers improved performance. The GetString method has the following parameters:
StringFormat specifies how the Recordset should be converted to a string. The parameter should be set to 2 (corresponding to the adClipString ADO constant).
NumRows is the number of records that should be converted into the returned Recordset. In the sample code above, the number of rows are contained in the iPageSize variable.
ColumnDelimiter is the string that should be appended to each column. In this particular example this is a closing table cell tag (</td>), followed by an opening table cell tag (<td>).
RowDelimiter is the string that is appended to each row. In this example, it is a closing table cell tag, followed by a closing table row tag (</tr>), then an opening table row tag (<tr>) followed by an opening table cell tag.
Finally, NullExpr specifies what should be displayed if the particular Recordset field has a null value. Setting this parameter to " " will add a HTML non-breaking space, and in Netscape browsers will prevent empty table cells from appearing blank.
Navigating around the Recordset
ADO allows Recordsets to be broken down into sections, called pages. Each of these pages of results can contain a user-specified number of records. The number of records contained within each page is controlled by the PageSize property of the Recordset. When returning a Recordset from the database, the records can be returned from a specific page by setting the AbsolutePage property.
In the sample code above, the AbsolutePage property is set from the iCurrentPage variable, which is itself obtained from the Page parameter from the QueryString. In this way it is possible to introduce page navigation (the code for this is below).
Adding links to other pages of results
If you want to add links to all of the other pages of results, then use something like the following VBScript:
An alternative method of navigation is to use links to the next and previous pages of results. This is achieved using the following:
Displaying the number of records in the Recordset
Obtaining the number of records in a Recordset is easily achieved since it is contained within the RecordCount property of the Recordset object. You can then show the user the number of records returned, e.g.
The disadvantage of using the RecordCount property is that it is not supported by all Recordsets.