Coding Article #: 37 - Published On: July 12, 2012 @ 21:49:34 PM - Last Updated on: July 13, 2012

Supported Files
File Download:
carrzkiss_125157Columns.zip
File Size: 35.86 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 your records in Columns
#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.
We use the Paging System demonstrated here, on all of our sites.
When you see the paging, this is it. We have it customized with a lot of CSS goodies, to fit into our pages look and feel.

View Live Example«

First, we need to add in some constants. These will allow us to create our ROWS and COLUMNS.
In this case, we are getting 5 Columns and 20 ROWS

<%
  Const MAX_CELLS_PER_ROW = 5
  Const MAX_ITEMS_PER_CELL = 20
  Dim itemIndex, cellIndex
%>

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

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

Next, we will create our paging system.

<%
Set sql = Server.CreateObject("ADODB.Command")
sql.ActiveConnection=objConn
sql.Prepared = true
Set objRs=Server.CreateObject("ADODB.RecordSet")
if request("NumPerPage") <> 0 Then NumPerPage = Int(request("NumPerPage")) Else NumPerPage = 100 end if
sql.commandtext="SELECT ID, MyTitle FROM Table1"

 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
	
objRs.CursorLocation = adUseClient
objRs.CursorType = adOpenKeyset 
objRs.LockType = adLockreadonly

'objRs.open searchsql
objRs.open sql
    Number = objRs.RecordCount
    if Not objRs.EOF Then
    	objRs.MoveFirst
    	objRs.PageSize = NumPerPage
    TotalPages = objRs.PageCount
    objRs.AbsolutePage = CurrentPage
    End if
    ScriptName = request.servervariables("ScriptName")
%>

Now, we can add in some CSS.

<style type="text/css">
.RegNum{
	font-size:12px; 
	font-family:Verdana, Geneva, sans-serif; 
	color:#356;
}
.lgNum{
	font-size:15px; 
	font-family:Verdana, Geneva, sans-serif; 
	color:#356;
}
blockquote{
	color:#356;
	font-size:14px;
	font-family:Georgia, "Times New Roman", Times, serif;
}
</style>

Next, is all the code for the table.

<%
    'Print the recent Data
    response.write "<br/><br/><hr style=""background-color:#CCCCCC"">Showing page <strong>" & CurrentPage & "</strong> of <strong>" & TotalPages & "</strong>: Total of Friends: <strong>" & Number & "</strong>"

  If objRs.EOF Then
    Response.Write "No Data Found"
  Else
    Response.Write "<table style=""border:1px double #000;"">"
    
    Do While Not objRS.EOF And objRS.AbsolutePage = CurrentPage
      cellIndex = 0
      Response.Write "<tr>"  ' Creates the beginning table row tag
      Do While cellIndex < MAX_CELLS_PER_ROW
        Response.Write "<td style=""border:1px double #000;"">"
        itemIndex = 0
        Do While itemIndex < MAX_ITEMS_PER_CELL
          Response.Write objRs("MyTitle") & "<br />"
          objRS.MoveNext
          If objRS.EOF Then Exit Do
          itemIndex = itemIndex + 1
        Loop
        Response.Write "</td>"
        cellIndex = cellIndex + 1
        If objRS.EOF Then Exit Do
      Loop
 
      While cellIndex < MAX_CELLS_PER_ROW
        Response.Write "<td> </td>"
        cellIndex = cellIndex + 1
      Wend
      Response.Write "</tr>"  ' Creates the ending table row tag
    Loop
    Response.Write "</table>"
  End If
%>

And last, we create our numbers for our paging.

<%

    'Creating the paging numbers
    'Display PREV page link, if appropriate
    if Not CurrentPage = 1 Then
    	Response.Write "<A href='" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=" & CurrentPage - 1 & "'><font class=""RegNum""><strong>..</strong></font></A>  "
    if CurrentPage > 5 and TotalPages > 10 Then
    Response.write("<A href=" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=1><font class=""RegNum""><strong>1</strong></font></A>" & "<font class=""RegNum""><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 Number of which page you are currently on, Thus BIG Number.
	Response.Write "<font class=""lgNum""><strong>" & a & "</strong></font>"
    Else
    Response.write("<A href=" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""RegNum""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    Next
    Else
    	if TotalPages = 1 Then 
    			Response.write ""
    		Else
			' This is the 1 number, when the page first loads. Change this with the CSS to give it a different color.
			' When changing this CSS information, this will also affect the "Page you are own" as well. Above.
    			Response.Write "<font class=""lgNum""><strong>1</strong></font>  "
    	End if
    	if TotalPages > 10 Then     'id=161&MWC=Layouts
    	fim = 10
    	Else
    	fim = TotalPages
    	End if
    	For a = 2 To fim
    if a = Cint(request("page")) Then
    Response.write( "<font class=""RegNum""><strong>" & a & "</strong></font>  ")
    Else
    Response.write("<A href=" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=" & a &"><font class=""RegNum""><strong>" & a & "</strong></font></A>" & "  " )
    End if
    Next
    End if
    if CurrentPage < TotalPages - 5 and TotalPages > 10 Then
    Response.write("<font class=""RegNum""><strong>... </strong></font><A href=" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=" & TotalPages &"><font class=""RegNum""><strong>" & TotalPages & "</strong></font></A>" & "  " )
    End if
	
	

    'Display NEXT page link, if appropriate
    if Not CurrentPage = TotalPages Then
    	Response.Write "<A href='" & ScriptName & "?id="&MyID&"&NumPerPage=" & NumPerPage & "&page=" & CurrentPage + 1 & "'><font class=""RegNum""><strong>..</strong></font></a>"
    Else
    	Response.Write ""
    End if
    %>