Building a Database Driven Table in Classic ASP Part 2

The original title for this post was Table Evolution 2: Sort and Page

In Part 1, we split the rendering of database-driven HTML tables into 3 parts: Establishing a Database Connection, Getting a RecordSet, and Drawing the TABLE. In this part, we will clean up the column headers, add sorting, and paging.

Renaming Columns

Sometimes you’ll want to have the table columns named differently than the columns in the database. Fixing this is as a simple as modifying the SQL query. By aliasing the fields in the query, we can rename the columns.

' original query that renders ugly column headers 
sSQL = "SELECT E.fname, E.lname, D.name FROM Employee E, Department D WHERE E.deptId = D.deptId " 

' aliased query that generates nice column headers 

sSQL = "SELECT E.fname AS FirstName, E.lname AS LastName, D.name AS Department " &_
"FROM Employee E, Department D WHERE E.deptId = D.deptId "

Sorting

We can add generic sorting functionality to our table with just a few lines of reusable code. The first version of the sort doesn’t require javascript and will work on every browser.

<%
sSQL = "SELECT CompanyName, ContactName, ContactTitle, City FROM Customers "
' the sort sequence will be passed in from the querystring 

sort = Request.QueryString("s")
If len(sort) > 0 AND IsNumeric(sort) Then
  sSQL = sSQL & " ORDER BY " & s 

End If
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = ac.Execute(sSQL)
%>

'  Update the column code in the drawTable subroutine 

Response.Write "<tr>"
intColumn = 1
strPath = Request.ServerVariables("PATH_INFO")
For Each field In rs.Fields
   Response.Write "<th><a href=""" & strPath & "?s=" & intColumn & """>" & field.name & "</a></th>"
   intColumn = intColumn + 1 

Next
Response.Write "</tr>" & chr(10)

Paging

With large recordsets, we may only want to return a limited number of records. By limiting the number of records returned, the page renders faster. For a detailed tutorial on ADO paging read Recordset Paging with ADO 2.0 by Michael Qualls. We want to add generic code that will work with any query. Inside the connect.asp file there will be a new subroutine called drawPaging.

Files

View the updated source of table.asp.txt and connect.asp.txt.

Last Words

In Part 1 and here in Part 2, we created some generic code libraries to connect to a database, return a recordset, and then draw an HTML table that supports sorting and paging. This code will allow us to quickly generate reports in ASP.