Paging Through a Recordset

Don't want all your records to show on just one page? Below you'll find the code you need to implement paging through your recordsets.

For demonstration purposes only! Please be aware of SQL Injection, and techniques to avoid.

<%@ Language=VBScript %>
<% Option Explicit %>
'Option Explicit forces you to declare your variables. You should always use this for debugging purposes.
<% Response.Buffer = True %>

'ADOVBS.INC is needed for named constants
<html>
<body>

<%
Dim Connect_String
Dim Page_Size 'variable which holds the number of records to be viewed per page.
Dim Current_Page 'variable which keeps track of which page is the current page.
Dim MyConn
Dim RS
Dim SQL
Dim Page_Count 'variable which stores the number of pages that can be viewed.

'if using SQL Server then use
Connect_String = "Provider=SQLOLEDB;Data Source=xxxx;UID=xxxx;PWD=xxxx;DATABASE=Demo"
'if using DSN-Less then use
Connect_String = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("your_database.mdb")
'if using DSN then use
Connect_String = Your_DSN

Page_Size = 15 'here we set the number of records viewed per page to 15.

If Request("Page")="" Then
Current_Page = 1
Else
Current_Page = CInt(Request("Page")) 'the CInt function converts the value to an integer.
End If

Set MyConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
MyConn.Open Connect_String

RS.CursorLocation = adUseClient
RS.PageSize = Page_Size

'below change the statement to reflect your query
SQL = "SELECT * FROM tblClients"

RS.Open SQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText

Page_Count = RS.PageCount

If 1 > Current_Page Then Current_Page = 1
If Current_Page > Page_Count Then Current_Page = Page_Count

RS.AbsolutePage = Current_Page

Do While RS.AbsolutePage = Current_Page AND Not RS.EOF
%>

'below change the fields to the fields in your table. <table>
<tr><td><%=RS("ClientID")%></td></tr>
<tr><td><%=RS("Company")%></td></tr>
<tr><td><%=RS("Address")%></td></tr>
<tr><td><%=RS("Phone")%></td></tr>
</table>

<%
RS.MoveNext
Loop

'clean up
RS.Close
Set RS = Nothing
MyConn.Close
Set MyConn = Nothing

Response.Write "<br>"

'below is the page navigation.
'we're using images for Next and Previous. you could easily use simple hyperlinks instead.
If Current_Page <> 1 Then
Response.Write "<a href=""paging_demo2.asp?Page="
'be sure to rename paging_demo2.asp to whatever you end up naming this page.
Response.Write Current_Page - 1
Response.Write """><img src=""../prev_1.gif"" border=""0""></a>" & vbCrLf
Response.Write " " & vbCrLf
End If
If Current_Page < Page_Count Then
Response.Write "<a href=""paging_demo2.asp?Page="
Response.Write Current_Page + 1
Response.Write """><img src=""../next_1.gif"" border=""0""></a>" & vbCrLf
End IF

%>
<br>
</body>
</html>

Grab these 2 images below, create your own, or use standard hyperlinks instead.

database paging sql access