ADO Recordset Paging

Many times after executing a query we get large number of records back and need to display certain number of records per page. Typically this occurs while providing search or find functionality.

Contributed by Bipin Joshi
Rating:

/ 37
January 01, 2003

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Above task can be accomplished by using ADO recordset's paging properties. Following properties are of our interest :

PageSize which sets the number of records per ADO recordset page
PageCount which gives total number of pages
AbsolutePage which gets/sets the current page

Now,we will develop a sample script to demonstrate the usage. For the purpose of
this example you should have biblio.mdb that comes with VB. For the sake of
simplicity error handling and fine tuning is omitted.

Following steps are involved in the process :

Declare ADO recordset variable
Set cursor type, location and pagesize properties
Open recordset
Display a list of page nummbers as hyperlinks so that when user clicks any link he will be presented with the records from that page
Set current page using AbsolutePage property
Display record contents from current page

Example Code :

<%@ Language=VBScript %>
<% Response.Buffer=true %>

<%
dim rs,i,strConn,strTemp
set rs=server.CreateObject("adodb.recordset")

rs.CursorLocation=3 'clientside
rs.CursorType=3 'staticrecordset
rs.PageSize=100

strConn="Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\Microsoft Visual Studio\VB98\Biblio.mdb"

rs.Open "select * from authors",strConn

Response.Write "<table><tr>"
for i=1 to rs.PageCount
Response.Write "<td><ahref=paging.asp?pg=" & i & ">" & i & "</a></td>"
next
Response.Write "</tr></table><br>"

if Request.QueryString("pg")="" then
rs.AbsolutePage=1
For i=1 to 100
Response.Write rs.Fields("author") & "<br>"
rs.MoveNext
Next
else
rs.AbsolutePage=cint(Request.QueryString("pg"))
For i=1 to 100
Response.Write rs.Fields("author") & "<br>"
rs.MoveNext
Next
end if

</BODY>
</HTML>

Let's examine the code

dim rs,i,strConn,strTemp
set rs=server.CreateObject("adodb.recordset")

First we declared variables for recordset,connection string and counter
Next we reated recordset object using CreateObject method
rs.CursorLocation=3
rs.CursorType=3
rs.PageSize=100

The cursorlocation is set to client-side and cursor type is set to static cursor.
Then we set the PageSize property to 100 which sets records per page to 100.
rs.Open "select * from authors",strConn
The recordset is then opened.
Response.Write "<table><tr>"
Response.Write rs.PageCount
for i=1 to rs.PageCount
Response.Write "<td><ahref=paging.asp?pg=" & i & ">" & i & "</a></td>"
next
Response.Write "</tr></table>"

This code simply puts the links for available pages in a table. The total pages can be found out by PageCount property
if Request.QueryString("pg")="" then
    rs.AbsolutePage=1
    For i=1 to 100
        Response.Write rs.Fields("author") & "<br>"
        rs.MoveNext
    Next
else
    rs.AbsolutePage=cint(Request.QueryString("pg"))
    For i=1 to 100
        Response.Write rs.Fields("author") & "<br>"
        rs.MoveNext
    Next
end if

This code checks for page number selected by the user.First time when we visit
that page it will be blank and hence we set the AbsolutePage property to 1. If
user selects any page that page number will be assigned to AbsolutePage property.
This causes the record pointer to move to the start of the page. We then display
author names by scrolling through the recordset.