Coding Article #: 38 - Published On: July 18, 2012 @ 00:36:39 AM - Last Updated on: July 18, 2012

Supported Files
File Download:
carrzkiss_125251Paging38.zip
File Size: 23.56 KB
Unzip with
(OR)

Screenshot of Source Code

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") & ";"
objConn.Open

' 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
searchsql.ActiveConnection=objConn
' 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
    Else
    CurrentPage = CInt(Request.QueryString("page"))
	NumPerPageOf = ((CurrentPage * NumPerPage) - NumPerPage) + 1
    End if
rsSearch.CursorLocation = adUseClient
rsSearch.CursorType = adOpenKeyset 
rsSearch.LockType = adLockreadonly
rsSearch.open searchsql 
	
    Number = rsSearch.RecordCount
    if Not rsSearch.EOF Then
    	rsSearch.MoveFirst
    	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">
body{
	background-color:#000;
	color:#D6D6D6;
	width:95%;
}
a:link, a:link font, a:active, a:active font, a:visited, a:visited font{
  font-family: Arial; 
  font-size: 12px; 
  color:#666666;
  text-decoration: none; 
}
a:hover{
text-decoration:overline underline;
}
.tdID{
	text-align:left;
	vertical-align:top;
	border:1px #333 double;
	width:50px;
}
.tdRound{
	text-align:left;
	vertical-align:top;
	border:1px #333 double;
	width:150px;
}
/*CList = For Current Page*/
.CList{
	font-size:15px;
}
/*FList = For All other page links*/
.FList{
	font-size:12px;
}
/*Lets set the page to the center, it just looks more professional than having it set alLeft or alRight*/
#MainDiv{
	margin-left:auto;
	margin-right:auto;
	width:500px;
}
</style>

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"
	else
    response.write "Showing page <strong>" & CurrentPage & "</strong> of <strong>" & TotalPages & "</strong>: Total of Records: <strong>" & Number & "</strong>"%> 
 
 <table>
 <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>       
    <%rsSearch.movenext
wend%>
</table>

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)
    		Else
    			ini = (TotalPages - 9)
    			fim = TotalPages
    		End if 
    	Else
    		ini = 1
    		fim = 10
    	End if
    	
    else
    ini=1
    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>")
    Else
    Response.write("<A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""FList""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    Next
    Else
    	if TotalPages = 1 Then 
    			Response.write ""
    		Else
    			Response.Write "<font class=""CList""><strong>1</strong></font>  "
    	End if
    	if TotalPages > 10 Then 
    	fim = 10
    	Else
    	fim = TotalPages
    	End if
    	For a = 2 To fim
    if a = Cint(request("page")) Then
    Response.write( "<font class=""CList""><strong>" & a & "</strong></font>  ")
    Else
    Response.write("<A href=" & ScriptName & "?NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""FList""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    Next
    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>"
    Else
    	Response.Write ""
    End if
	end if
' Here is where we close our Recordset and our Database connection.
	rsSearch.close
    Set rsSearch = Nothing
	objConn.close
	set objConn = Nothing
%>