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.
Download and unzip paging.zip to your web server's root
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.
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
The code for formatting phone numbers can be downloaded at ASP101.com (http://www.asp101.com/samples/phone_format.asp)
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.
