|
Home > Articles
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.
Introduction
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.
<%
'Declare variables
Dim iCurrentPage
Dim iPageSize
Dim i
Dim oConnection
Dim oRecordSet
Dim oTableField
Dim sPageURL
'Declare constants
Const adOpenStatic = 3 'Open a
RecordSet using a static cursor
Const adLockReadOnly = 1 'Open a
RecordSet in read-only mode
'Retrieve the name of the current ASP document
sPageURL = Request.ServerVariables("SCRIPT_NAME")
'Retrieve the current page number from the QueryString
iCurrentPage = Request.QueryString("Page")
If iCurrentPage = "" Or iCurrentPage = 0 Then iCurrentPage = 1
'Set the number of records to be displayed on each page
iPageSize = 3
'An ADO connection string
oConnection = "Provider=SQLOLEDB.1;Persist
Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=PUBS_DATABASE;Use
Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=
DATABASE_SERVER; User Id=PubsDBUser;PASSWORD=gt6Te4Ja;"
'An SQL statement
sSQLStatement = "SELECT * FROM
Publishers"
'Create an ADO RecordSet object
Set oRecordSet = Server.CreateObject("ADODB.Recordset")
'Set the RecordSet PageSize property
oRecordSet.PageSize = iPageSize
'Set the RecordSet CacheSize property to the
'number of records that are returned on each page of results
oRecordSet.CacheSize = iPageSize
'Open the RecordSet
oRecordSet.Open sSQLStatement, oConnection, adOpenStatic,
adLockReadOnly
'Move to the selected page in the record set
oRecordSet.AbsolutePage = iCurrentPage
'Display the opening HTML of a table
Response.Write "<table border=""0""
width=""50%"" cellpadding=""2""
cellspacing=""0"">"
Response.Write "<tr>"
'Loop through the fields in the RecordSet and
'display a column heading for each field
For Each oTableField In oRecordSet.Fields
Response.Write "<th width=""50%""
bgcolor=""#008080"" align=""left""><font
color=""#FFFFFF""><b>" & oTableField.Name &
"</b></font></th>"
Next
Response.Write "</tr>"
Response.Write "<tr><td width=""50%""
bgcolor=""#C0C0C0"">"
'Use the GetString method to display the database rows
'The GetString method has the following parameters:
'StringFormat = This should be set to 2 (or the adClipString ADO constant)
'NumRows = Number of RecordSet rows to be used
'ColumnDelimiter = Delimiter to be used between columns
'RowDelimiter = Delimiter to be used between rows
'NullExpr = Expression to use for null values
Response.write oRecordSet.GetString(2, iPageSize,
"</td><td width=""50%""
bgcolor=""#C0C0C0"">",
"</td></tr><tr><td width=""50%""
bgcolor=""#C0C0C0"">", " ")
Response.Write "</td></tr></table>"
'Release database connectivity objects
oRecordSet.Close
set oRecordSet = nothing
set oConnection = nothing
%>
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
Response.Write "<th width=""50%""
bgcolor=""#008080"" align=""left""><font
color=""#FFFFFF""><b>" & oTableField.Name &
"</b></font></th>"
Next
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 Recordset’s GetString method. This method
also offers improved performance. The GetString method has the following parameters:
- StringFormat
- NumRows
- ColumnDelimiter
- RowDelimiter
- NullExpr
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:
<%
'Display a list of links to all of the other pages of results
For i = 1 to oRecordSet.PageCount
If i = CInt(iCurrentPage) Then
Response.Write "[ Page " & i & " ] "
Else
Response.Write "[ <a href=""" & sPageURL &
"?Page=" & i & Chr(34) & ">Page " & i &
"</a> ] "
End If
Next
%>
An alternative method of navigation is to use links to the next and previous pages of
results. This is achieved using the following:
<%
'If the current page number is less than the
'total number of pages then display a link
'to the next page of results
If CInt(iCurrentPage) < oRecordSet.PageCount Then
Response.Write "<a href=""" & sPageURL & "?Page="
& (iCurrentPage + 1 ) & """>Next Page</a>"
End If
'If the number of the current page is greater than
'the first page then display a link to the previous
'page of results
If CInt(iCurrentPage) > 1 Then
Response.Write "<a href=""" & sPageURL & "?Page="
& (iCurrentPage - 1 ) & """>Previous Page</a>"
End If
%>
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.
<%
Response.Write("Your search has returned ")
Response.Write(oRecordSet.RecordCount)
Response.Write(" records.")
%>
The disadvantage of using the RecordCount property is that it is not supported by all
Recordsets.
Further reading
Recordset paging is also covered in the following articles:
Useful Development Tools
| ASP
Documentation Tool™ |
| Automatically creates developer documentation for ASP 2.0
and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft
Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the
web application can also be incorporated into the reports. Documentation is created in
HTML, HTML Help and plain text formats. |
 View Sample
Output (HTML Help format).
 View Sample Output (HTML Format).
 Download
Trial Version (5.2Mb ZIP file). |
| .NET Documentation Tool |
| Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the
application. Documentation is created in HTML, HTML Help and plain text formats. |
View Sample
Output (HTML Help format).
View Sample Output (HTML Format).
Download
Trial Version (5Mb ZIP file). |
| SQL
Documentation Tool |
| The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers and Table Relationships. |
View Sample
Output (HTML Help format).
View Sample Output (HTML Format).
Download
Trial Version (10.3Mb ZIP file). |
| Indexing Service Companion |
|
The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.
|
Try Sample Search Facility.
Download
Trial Version (1.7Mb ZIP file). |
| The Website Utility |
| The Website Utility examines websites for errors and
areas that need to be optimised for search engines by using a built in web crawling engine.
Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags.
It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is
able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website. |
View Sample Output (HTML Format).
Download
Trial Version (3Mb ZIP file). |
|
