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.

<%@ 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.