A Reusable Paging Class... and More

Introduction

After years of developing web pages, when it comes to user reports, there are only two types of requests. First, there's the detail page where you have data in columns with varying details and formats. Then there's the more popular request - A display page with paging support, drill-down, sorting, and more.

Such displays are found virtually everywhere: on-line stores, search engines results, and forums. Of course, there are countless samples and tutorials available in a variety of web development languages (ASP, PHP, ColdFusion). However, I'm a strong believer in creating reusable classes and components whenever possible. That's when I got started on this project - A reusable paging class that supports column formatting, drill-down, sorting, and column summary.

Sure, you say its been done before, so why another one? Well, I have not seen anything written to do what I want, and supports formatting, and sorting to boot. The folks doing PHP are well ahead of their counterparts in this area. They have great paging codes (and lots of other classes as well), but still missing some features.

Screen Shot

Installation

So, let's get to it. The supporting code contains all the files needed to get you going. This article assumes that your working folder is: c:\inetpub\wwwroot\paging. If you change the location, you need to modify inc\dsn.inc to configure your ODBC. On the other hand, if you use the same folder as I have, then run paging.reg to create the ODBC automatically.

1. Download and unzip paging.zip to your web server's root
2. Create an ODBC connection to the database provided. Depending on your preference, do one of the following:
   

   a) Automatic: Run (double-click) paging.reg (located in the INC folder) to import the ODBC configuration into your ODBC admin. Keep in mind; this works only if you unzip your work to some folder other than c:\inetpub\wwwroot\paging.

   b) Manual:
   

   1. Start ODBC Admin
   2. Add a new DSN
   3. Call it paging (if you change this name, you have to change the name in inc/dsn.inc as well.
   4. Point to file nw.mdb. File nw.mdb is a condensed version of the infamous Northwind database. It contains only the customer table.

3. Point your browser to the appropriate folder, and off you go

Directory Structure

The Paging Class

The class file is contained in pagination.inc. To use paging, include the class file at the top of your ASP code, and initialize the following member variables:

  ' instantiate the class object

  Set objPagination = New cPagination

  ' include any other values for your paging URL

  objPagination.PagingString = "report=2&key=" & strKey

  ' for the first column, where should the drill down go

  objPagination.DetailString = "index.asp?report=3&key=" & strKey & "&key2="

  '  SQL statement

  objPagination.SQLString = strSQL

  ' number of columns

  objPagination.NumberOfFields = 7

  ' formatting for columns (optional)

  redim strFormat(objPagination.NumberOfFields)

  strFormat(0) = ""

  strFormat(1) = ""

  strFormat(2) = ""

  strFormat(3) = ""

  strFormat(4) = "shortdate"

  strFormat(5) = "phone"

  strFormat(6) = "number02"

  objPagination.DataFormat = strFormat

  ' sorting for columns (optional)

  redim strSort(objPagination.NumberOfFields)

  strSort(0) = "customerid"

  strSort(1) = "companyname"

  strSort(2) = "contactname"

  strSort(3) = "contacttitle"

  strSort(4) = "dte"

  strSort(5) = "phone"

  strSort(6) = "dollars"

  objPagination.SortColumns = strSort

  ' is it ascending or descending

  objPagination.SortOrder = strSrt

  ' which column is currently sorted by

  objPagination.SortField = strCol

  redim strSum(iNumberOfFields)

  strSum(0) = ""

  strSum(1) = ""

  strSum(2) = ""

  strSum(3) = ""

  strSum(4) = ""

  strSum(5) = ""

  strSum(6) = "1"

  objPagination.SumColumns = strSum

  ' records per page of display (default is 10)

  objPagination.RecordsPerPage = 10

  ' the template for column header

  objPagination.TemplateHeader = strHeader

  ' the template for detail output

  objPagination.Template = strTemplate

  ' the footer

  objPagination.TemplateFooter = strFooter

  ' the template to contain the paging string

  objPagination.TemplateNavigation = strNavigation

  ' write the result HTML after calling the class's ShowRecord method

  ' iPageCurrent is set from a value received in the URL, which is sent when

  ' paging begins (see code for detail)

  response.write objPagination.ShowRecords(iPageCurrent)

  ' return resource to system

  Set objPagination = nothing

There you have it. It seems like a lot of lines, but you don't need everything if you only want to display plain rows with paging. Sorting and formatting are extras ( increasing the number of lines). Essentially, that is all you have to do to produce your paged displays.

Putting it Together - An Example

This section describes the steps needed to get your web site ready for the paging class. For every page you intend to have paging, do the following (refer to index.asp):

1. Include any codes at the of your page:



  ' VBA constants

  <!--#include FILE="inc/adovbs.inc"-->

  ' values for ODBC connection

  <!--#include FILE="inc/dsn.inc"-->

  ' paging class

  <!--#include FILE="inc/pagination.inc"-->



2. Paging variables:



  ' required for your paging class

  Dim strSQL			' your sql statement (required)

  Dim objPagination		' paging object  (required)

  Dim iPageCurrent		' the current page  (required)

  Dim strHeader			' column headers  (required)

  Dim strTemplate		' data  (required)

  Dim strFooter			' closing footer for table  (required)

  Dim strFormat			' column data format (optional)

  Dim strSort			' column names for sorting (optional)

  Dim strSum			' column summary (optional)



  ' I like to keep my code in one page, and divide them into different functions. iReport

  ' holds the value (1, 2, 3, etc.) that determines which function to call (see code detail)

  Dim iReport

  Dim strKey			' if you have a detail screen, this is where it is stored

  Dim strTemp			' all purpose variable

  Dim strCol			' name of the column currently sorted by

  Dim strSrt			' the sorting order (asc/desc)

  Dim strSearch			' if you intend to do searches



  ' set the page to view

  If Request("page") = "" Then iPageCurrent = 1 Else iPageCurrent = CInt(Request("page"))



  ' which screen should display

  If Request("report") = "" TheniReport = 1 Else iReport = CInt(Request("report"))



  ' if detail is requested, send the key field here

  If Request("key") = "" Then strKey = "" Else strKey = CStr(Request("key"))



  ' if a search is desired, it is picked up here, and passed on to other pages

  If Request("search") = "" Then strSearch = "" Else strSearch  = CStr(Request("search"))



  ' you might want to provide a mapping of where you are, and give yourself a

  ' way back to the main page

  strTemp = "<a href='index.asp'>main</a>"



  ' in search mode, we hide the mapping of where we are

  if strSearch <> "" then strTemp = " "



  ' setting up the output for the top of the page. Function WriteSearchFormHTML is

  ' a convenient function for writing the form so that we don't have to code it over

  ' and over again for other pages.

  strTemp = "<table border='0' cellspacing='1' cellpadding='2' width='700'> " & _

    "<tr bgcolor=FFFFFF> " & _

    "<td width='50%'>" & strTemp & "</td> " & _

    "<td width='50%' align='right'>" & WriteSearchFormHTML(strSearch) & " " & _

    "</tr></table>"



  ' write HTML to screen

  if strTemp <> "" then response.write ( strTemp )



  ' when we first get here, iReport is set to 1 if there is no value sent in the URL

  ' (report). report = 2 when we are drilling down.

  Select Case iReport

    Case 1

      call PagingDisplay

    Case 2

      call DrillDown

    Case 2

      call SearchDisplay

  End Select



3. Paging code



  function PagingDisplay()



    ' SQL statement

    strSQL = "SELECT customerid, companyname, contactname, contacttitle, dte " & _

      "FROM customers " & _

      "ORDER BY companyname"



    ' this string contains the start of your table, and the column header. Note that you

    ' change the column width, set background colors and more. Add class elements

    ' if you wish to do cascading stylesheets. Here, I have a table with 4 columns

    ' ( Company, Contact, Title, Date). Cut-n-paste addition columns depending on

    ' your desired output.

    strHeader = "<table border='0' cellspacing='1' cellpadding='2' width='700'> " & _

      "<tr bgcolor=B6C7E5> " & _

      "<td class='header10'><| col_u_0 |><b>Company</b><| col_d_0 |></td> " & _

      "<td class='header10'><| col_u_1 |><b>Contact</b><| col_d_1 |></td> " & _

      "<td class='header10'><| col_u_2 |><b>Title</b><| col_d_2 |></td> " & _

      "<td class='header10' align='right'><| col_u_3 |><b>Date</b><| col_d_3 |></td> " & _

      "</tr>"



    ' this is your table-row detail. For each column, there should be a corresponding

    ' column for output. This is where the key of paging begins. Note the pattern

    ' <| ____ |>. This pattern contains a value for what you want to output. For

    ' example, in the <tr> tag, I have <| class |>. This pattern holds the CSS for

    ' controlling alternating row colors.

    '

    ' In the first <td> tag, you have <| link |>. This pattern holds the link for drilling

    ' down to the next level. Class member variable 'DetailString' replaces this patter.

    ' The following <td> tags contain <| row1 |> and more. Row numbers are in

    ' order by the columns select from your SQL statement (0th based index).

    ' <| row0 |> is the first column (Customerid), <| row1 |> is the

    ' second (Companyname, and so on. Repeat for as many columns as needed.

    strTemplate = "<tr class='<| class |>'> " & _

      "<td class='smalltext' valign='top'><a href='<| link |>'><| row0 |></a></td> " & _

      "<td class='smalltext' valign='top'><| row1 |></td> " & _

      "<td class='smalltext' valign='top'><| row2 |></td> " & _

      "<td class='smalltext' valign='top' align='right'><| row3 |></td> " & _

      "</tr>"



    ' this is the closing of your table.

    strFooter = "</table>"



    ' Provide a string to hold the paginated output string (<| navigation |>).

    strNavigation = "<table border='0' cellspacing='1' cellpadding='2' width='700'> " & _

      "<tr bgcolor=FFFFFF> " & _

      "<td class='smalltext'><| navigation |></td> " & _

      "</tr></table>"



    ' see section 'The Paging Class' for detail on this code. Note the use of strFormat.

    ' First, I re-dimensioned an array with NumberOfFields elements. Then I set individual

    ' element with the desired output format. For example, I want the date field to

    ' be displayed as a short date, so:

    ' strFormat(4) = "shortdate"

    ' Right now, I have short date, long date, short time, and long time. You could enhance

    ' the code to format numerical data as well.

    Set objPagination = New cPagination

    objPagination.DetailString = "index.asp?report=2&key="

    objPagination.SQLString = strSQL

    objPagination.NumberOfFields = 5

    redim strFormat(objPagination.NumberOfFields)

    strFormat(0) = ""

    strFormat(1) = ""

    strFormat(2) = ""

    strFormat(3) = ""

    strFormat(4) = "shortdate"

    strFormat(5) = "phone"

    objPagination.DataFormat = strFormat

    redim strSort(objPagination.NumberOfFields)

    strSort(0) = "customerid"

    strSort(1) = "companyname"

    strSort(2) = "contactname"

    strSort(3) = "contacttitle"

    strSort(4) = "dte"

    strSort(5) = "phone"

    objPagination.SortColumns = strSort

    objPagination.SortOrder = strSrt

    objPagination.SortField = strCol

    redim strSum(iNumberOfFields)

    strSum(0) = ""

    strSum(1) = ""

    strSum(2) = ""

    strSum(3) = ""

    strSum(4) = ""

    strSum(5) = ""

    strSum(6) = "1"

    objPagination.SumColumns = strSum

    objPagination.RecordsPerPage = 10

    objPagination.TemplateHeader = strHeader

    objPagination.Template = strTemplate

    objPagination.TemplateFooter = strFooter

    objPagination.TemplateNavigation = strNavigation

    response.write objPagination.ShowRecords(iPageCurrent)

    Set objPagination = nothing

  end function

5. Search Page – If you want searching of your data, SearchDisplay is the place for it. This code is similar to the PagingDisplay code except for the SQL statement and whatever your output looks like.

6. Printing Footer – routine PrintFooter is just a reusable function to display the footer at the end of my web page.

As you can see, it doesn't take much to make it happen. For each paging site that I have to code, it takes about 10-20 minutes to complete. Just change the SQL statement, your column names, and some basic paging class member variables. It can't get easier than that.

If you are reading this section, I'll assume that you are competent with your ASP/BASIC code, and wont' bother you with the details of the paging code. However, I will describe the functions making up the bulk of the clas for those who are interested.

• ShowRecord has a typical structure. Variable definition, ODBC and recordset creation, then the output section. The IF statement verifies that you do have data to display, then it proceeds. When the recordset returns with 15+ pages, the class displays only 15 pages at a time. This makes it easier to read, and you can always see the next set of pages by click on the double arrows in either directions.
• FormatData needs code for formatting other data types (numeric, etc.)
• FormatSortImage places up and down images besides column titles

Enhancements

• More parameters for data formatting

Credit

Conclusion

This was a fun project, and it is used on many web pages since it was created. Unfortunately, the sorting needs a bit reworking to support sorting of the current page so that what you see is sorted to that page and not the whole recordset.

Please forward your comments/suggestions to ttgiang@yahoo.com. It would be great to hear about your experience, and suggestions on how to make this class better. Should you download (161 kb) and revise the class, please let me know so that I can share with everyone. Thank you for stopping by.