Using ASP pages to page through Recordsetsby Faisal Khan.
 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 : 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 : | 
.NET
ASP (16) 
Contains 1 or more replies by the Author of this Article.
Contains 1 or more replies by 
