Previous Article
Column & Paging System for Classic ASP
Paging System for Classic ASP
Go Back To List
Coding Article #: 38 - Published On: July 18, 2012 @ 00:36:39 AM - Last Updated on: July 18, 2012
This article has been Favorited 49 times
Join today, and add this to your favorites.
Coding Source - Share on MySpace Coding Source - Share With Facebook Coding Source - Share on Twitter Coding Source - Share on Reddit Coding Source - Share on Digg It Coding Source - Share on Stumble Upon It Coding Source - Share on Delicious
Share With Friends (Updated 6-8-2010)

Supported Files
Support Files! (Members Only)
Login (or) Register today!
Decompress with
Download winzip today!    (OR)    
Screenshot of Source Code
Coding Source - Paging System for Classic ASP
Have a lot of records in your database, that you need to page through? If the answer is yes, then this is the code for you.

#1: Display a number of records that you want to display to your visitor
#2: Page through your records, instead of having them all display on the same page.

This code does it all in one simple easy to use the script.
Customize the CSS code, to give the page your very own twist, to suit your site's needs.

View Live Example«

Next, we need to get our DATABASE connection.
Below, you can choose to use either JET or Microsoft Access Driver.

Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("paging.mdb") & ";"

' OR
objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & _
Server.MapPath ("paging.mdb")

Next, we create a paging script from our datbaase.

' Here we are creating our Command object.
Set searchsql = CreateObject("ADODB.Command")
' Here we are connected to our database Object
' Prepared set to TRUE, we are ready to get our records
searchsql.Prepared = true
' Here we are creating our RecordSet for to display data with
Set rsSearch=Server.CreateObject("ADODB.RecordSet")
' To change the number of records that are Displayed change the
' NumPerPage = 10  <== Change this # to the value you want to display
if request("NumPerPage") <> 0 Then NumPerPage = Int(request("NumPerPage")) Else NumPerPage = 10 end if
' Here is where we get our data from our database.
searchsql.commandtext="SELECT id, first_name, last_name FROM NamesList"

 if Request.QueryString("page") = "" Then
    CurrentPage = 1 'We're On the first page
	NumPerPageOf = 1
    CurrentPage = CInt(Request.QueryString("page"))
	NumPerPageOf = ((CurrentPage * NumPerPage) - NumPerPage) + 1
    End if
rsSearch.CursorLocation = adUseClient
rsSearch.CursorType = adOpenKeyset 
rsSearch.LockType = adLockreadonly searchsql 
    Number = rsSearch.RecordCount
    if Not rsSearch.EOF Then
    	rsSearch.PageSize = NumPerPage
    TotalPages = rsSearch.PageCount
    rsSearch.AbsolutePage = CurrentPage
    End if
    ScriptName = request.servervariables("ScriptName")
	cs = 1

Next, we add in some CSS, to give our table a little better look.

<style type="text/css">
a:link, a:link font, a:active, a:active font, a:visited, a:visited font{
  font-family: Arial; 
  font-size: 12px; 
  text-decoration: none; 
text-decoration:overline underline;
	border:1px #333 double;
	border:1px #333 double;
/*CList = For Current Page*/
/*FList = For All other page links*/
/*Lets set the page to the center, it just looks more professional than having it set alLeft or alRight*/

Next, we check if their are any records, and if there is, we show the table.
If, not, then we show a message. - "Sorry, there are no records available"

' Check to see if there is any records to display, if there is not, then show a messages, else let the people know how many records are available.
	if rsSearch.eof then
	response.Write"Sorry, there are no records available"
    response.write "Showing page <strong>" & CurrentPage & "</strong> of <strong>" & TotalPages & "</strong>: Total of Records: <strong>" & Number & "</strong>"%> 
 <tr><td class="tdID">ID</td><td class="tdRound">First Name</td><td class="tdRound">Last Name</td></tr>   
    While Not rsSearch.EOF and Count < rsSearch.PageSize
    count = count + 1
	' If we have records, then display them using the Variables listed below.
	if not rsSearch.eof then
	ID = rsSearch("id")
	fname = rsSearch("first_name")
	lname = rsSearch("last_name")
	end if
    <%'This is were your Table of records go%>
 <tr><td class="tdID"><%=ID%></td><td class="tdRound"><%=fname%></td><td class="tdRound"><%=lname%></td></tr>       

This is our Pagenation script, to jump from one set of records to the next.
Creating the paging numbers
Display PREV page link, if appropriate
DO NOT mess with this area, ONLY if you need to change the QueryString around.
Example: ?NumPerPage
Lets say that you are doing Queries, then you would change it to suit your Query.
Example: ?id="&id&"&NumPerPage
As you see in the above Example, i kept the ? and the NumPerPage, and added in my Query inbewteen them.
If you do use a Query, then you will need to change every place that it is needed. There are 5 area's
For CSS Styling----------------------------
To change the way that the links look and feel, please look in the CSS section for CList and FList
There you will be able to customize the font styling for the links.

    if Not CurrentPage = 1 Then
    	Response.Write "<A href='" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & CurrentPage - 1 & "'><font class=""FList""><strong>..</strong></font></A>  "
    if CurrentPage > 5 and TotalPages > 10 Then
    Response.write("<A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=1><font class=""FList""><strong>1</strong></font></A>" & "<font class=""FList""><strong> ... </strong> </font>" )
    End if
    if TotalPages > 10 Then
    	if CurrentPage > 5 Then
    		if TotalPages > (CurrentPage + 5) Then
    			ini = (CurrentPage - 4)
    			fim = (CurrentPage + 5)
    			ini = (TotalPages - 9)
    			fim = TotalPages
    		End if 
    		ini = 1
    		fim = 10
    	End if
    fim = TotalPages
    End if
    For a = ini To fim
    if a = Cint(request("page")) Then
	' This is the page number that you are currently on, change its font size here.
    Response.write( "<font class=""CList""><strong> " & a & " </strong></font>")
    Response.write("<A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""FList""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    	if TotalPages = 1 Then 
    			Response.write ""
    			Response.Write "<font class=""CList""><strong>1</strong></font>  "
    	End if
    	if TotalPages > 10 Then 
    	fim = 10
    	fim = TotalPages
    	End if
    	For a = 2 To fim
    if a = Cint(request("page")) Then
    Response.write( "<font class=""CList""><strong>" & a & "</strong></font>  ")
    Response.write("<A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""FList""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    End if
    if CurrentPage < TotalPages - 5 and TotalPages > 10 Then
    Response.write("<font class=""FList""><strong>... </strong></font><A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & TotalPages &"><font class=""FList""><strong>" & TotalPages & "</strong></font></A>" & "  " )
    End if
    'Display NEXT page link, if appropriate
    if Not CurrentPage = TotalPages Then
    	Response.Write "<A href='" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & CurrentPage + 1 & "'><font class=""FList""><strong>..</strong></font></a>"
    	Response.Write ""
    End if
	end if
' Here is where we close our Recordset and our Database connection.
    Set rsSearch = Nothing
	set objConn = Nothing

Post to Facebook about: Paging System for Classic ASP