Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

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 "&nbsp;" 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.

  Site Map | Privacy Policy

All content is 1995 - 2012