Easy ADO RecordSet Paging
Introduction
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.
Easy Recordset Paging
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 less well 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 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 technical 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 applications written in C# or VB.NET (including ASP.NET).
Documentation for SQL Server 7/2000/2005 databases and C#/VB.NET components associated with the
web application can also be incorporated into the reports. Documentation is created in
HTML, HTML Help and plain text formats. Additional support for ASP.NET web applications. A useful alternative to the popular NDoc code documentor. |
View Sample
Output (HTML Help format).
View Sample Output (HTML Format).
Download
Trial Version (3Mb ZIP file). |
| SQL
Documentation Tool |
| The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0, 2000 and 2005 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, Table Relationships, Jobs and DTS Packages. |
View Sample
Output (HTML Help format).
View Sample Output (HTML Format).
Download
Trial Version (10.3Mb ZIP file). |
| Text Workbench |
| Text Workbench is a file search and replacement utility for text files and Microsoft Office documents. Make rapid file replacements on multiple files and folders full of files. Advanced replacement options include regular expressions support. It even works on remote file systems via FTP. A Regular Expression Laboratory allows advanced pattern matching and replacement expressions to be built and tested. This great utility will make your everyday development tasks much easier!
|
|
Download
Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation). |
| ASP Spell Check |
| ASPSpellCheck is the easy way to add spell checking capabilities to your ASP or ASP.NET websites, Intranets and web applications. The utility allows you to add spell checking capabilities to any HTML text field or rich content editing text box. It works with all common web browsers, and there are no components or databases to install on the server.
|
Read ASPSpellCheck Review.
View ASPSpellCheck Examples.
Download
Trial Version (3Mb ZIP file; you have the option to either install directly from this link or save the file for later installation). |
Author details
Brett Burridge
has worked as a web developer since 1997 and has developed web applications for a range of corporations, start up busiensses and educational establishments.
Brett is presently employed as an Internet developer and technical writer
through his own company,
Winnersh Triangle Web Solutions Limited.
The company produces a number of
innovative products, including a range of software documentation tools, which include the
ASP Documentation Tool™, the .NET Documentation Tool for VB.NET and C#, and the SQL Server Documentation Tool. Other products include The Website Utility, which functions as a website error checker, search engine optimizer and ASP/ASP.NET search engine builder application.
As well as the ASPAlliance, Brett has written articles for Ariadne.ac.uk,
ASPToday, the software documentation portal www.softwaredocumentation.info, and has contributed recipes to the ASP.NET Developer's Cookbook.
links
Outside web development, Brett is interested in travelling (here are my travel logs from New York, Hong Kong and Tokyo), digital photography (here's my photo gallery), tropical fishkeeping
and collecting contemporary works of art by artists such as Doug Hyde.
Contact Brett by emailing
Article history
"Easy ADO Recordset paging" originally published on ASPWatch.com on October
26 2000. Republished on ASPAlliance.com on 28 September 2001.
ASP Kitchen: ASPWatch.com
articles: Easy ADO RecordSet Paging
|
