Using ASP pages to page through Recordsets

Introduction
In this article we will learn how to use ASP pages to page through recordsets.

What is paging ?
Paging is a very useful way of presenting data. It allows you to show 5, 10 or whatever number of records you want per page and then show typical 'first page', 'last page', 'previous page', 'next page' etc buttons to navigate between pages.

How do I do it ?
This is what we will learn in subsequent pages.

What do I need to know ?
I only expect that you know at least the basics of accessing the database and presenting it's data on the ASP page. If you are not comfortable with it then kindly read Accessing the Database via ASP tutorial first.

Building paging.asp page
Open text pad or whatever program you use to build ASP pages and create a new page. Copy and paste the following code into it and then save it as 'paging.asp' :

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
 <style>
  body { font-family : Verdana; font-size : 8pt; }
  a { font-family : Verdana; font-size : 8pt;
   text-decoration : none; }
 </style>
</head>

<body>
<%
 Dim connStr
  connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
   Server.MapPath("paging.mdb")

 Dim rs
  Set rs = Server.CreateObject("ADODB.Recordset")

  rs.PageSize = 5
  rs.CacheSize = 5
  rs.CursorLocation = adUseClient

 rs.Open "Names", connStr, adOpenForwardOnly, adLockReadOnly, _
  adCmdTableDirect
 
 If Len(Request("pagenum")) = 0  Then
  rs.AbsolutePage = 1
 Else
  If CInt(Request("pagenum")) <= rs.PageCount Then
   rs.AbsolutePage = Request("pagenum")
  Else
   rs.AbsolutePage = 1
  End If
 End If

 Dim abspage, pagecnt
  abspage = rs.AbsolutePage
  pagecnt = rs.PageCount

 If Not rs.EOF Then
  Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
  Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

  Response.Write "Total number of records : " & rs.RecordCount
  Response.Write "<br><br>" & vbcrlf

  Dim fldF, intRec

  Response.Write "<table border=1 align=center cellpadding=3 "
  Response.Write "cellspacing=0><thead><tr>"
  For Each fldF in rs.Fields
   Response.Write "<td>" & fldF.Name & "</td>"
  Next
  Response.Write "</tr></thead><tbody>"

  For intRec=1 To rs.PageSize
   If Not rs.EOF Then
    Response.Write "<tr>"
    For Each fldF in rs.Fields
     Response.Write "<td>" & fldF.Value & "</td>"
    Next
    Response.Write "<tr>"
    rs.MoveNext
   End If
  Next
  Response.Write "</tbody></table><p>"

Note
Note that this is not complete ASP page so don't start running it. On the next page we will add some more code to it and then you can run it. Also remember that you can download the complete working page and the database at the end of this article.

Explanation
Most of the code given above will already be familiar to you. I will only explain the parts of it which involve paging through recordsets.

	rs.PageSize = 5

Ok first let me summarize to what we have done till getting to the above code. We have defined the connection string we will use to connect to the database. Then we have created the Recordset object but haven't yet opened the database. Now the above code, before opening the database we define certain properties of the Recordset object which will enable us to page through recordset later. It is important that you understand these properties now so that later when you create paging in your own pages you don't forget what you have learned here.

PageSize should be set to the number of records you want to show per page. If you want to show 5 records then set it to 5 and if want to show 10 records then set it to 10 and so on.

	rs.CacheSize = 5

CacheSize is totally optional as far as paging of recordset is concerned. It should be set to the number of records to be cached in the memory.

	rs.CursorLocation = adUseClient

Now this is a must. CursorLocation property should be set to 'adUseClient' or paging won't work.

	If Len(Request("pagenum")) = 0  Then
		rs.AbsolutePage = 1
	Else
		If CInt(Request("pagenum")) <= rs.PageCount Then
			rs.AbsolutePage = Request("pagenum")
		Else
			rs.AbsolutePage = 1
		End If
	End If

PageCount is a read only property which tells the total number of pages to be shown containing the records. For example if you have a database with 20 records and you have set PageSize to 5 then value of PageCount will be 4.

Ok now have set the PageSize to 5 and our sample database in this article contains 17 records so value of PageCount will be 4 with last ( 4th ) page showing only 2 records. Meaning there by that we have 4 pages of recordsets with us. But how to show the page we want ? This is where we need AbsolutePage property. It allows you to select the recordset page you want to show to the user. So if you want to show the first recordset page then set AbsolutePage to 1. You can set it to any page value between 1 and total number of recordset pages which you can get by PageCount property.

This is exactly what we have done in the above code. We are asking for the query string 'pagenum'. If it is there, that is we have given it then set AbsolutePage to it otherwise set it to 1.

	Dim abspage, pagecnt
		abspage = rs.AbsolutePage
		pagecnt = rs.PageCount

	If Not rs.EOF Then
		Response.Write "PageCount : " & rs.PageCount & _
			"<br>" & vbcrlf
		Response.Write "Absolute Page : " & rs.AbsolutePage & _
			"<br>" & vbcrlf

		Response.Write "Total number of records : " & rs.RecordCount
		Response.Write "<br><br>" & vbcrlf

Here we output the PageCount, AbsolutePage and RecordCount properties of Recordset object to show you how they work. It is not important as far as paging is concerned. You can use them if you want in your paging pages or simply ignore them.

	For intRec=1 To rs.PageSize
		If Not rs.EOF Then
			Response.Write "<tr>"
			For Each fldF in rs.Fields
				Response.Write "<td>" & _
					fldF.Value & "</td>"
			Next
			Response.Write "<tr>"
			rs.MoveNext
		End If
	Next
	Response.Write "</tbody></table><p>"

Now this is where we show the records according to the AbsolutePage property. We use two loops to iterate through the records. One loop loops through the number of records to show and other one shows all the fields in the record.

Completing paging.asp page
Copy the following code and paste it into the paging.asp page below the code you pasted in the last page :