The article was written in 2001.

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 her 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.

Labels: ADO, ASP, Table, VBscript

This article was written in 2001. The original title was Table Evolution 1 - The Basics

One of the most common tasks an ASP developer will do is populating an HTML table with data. In the years I've been doing ASP coding, I've created hundreds of tables. This article is the first part of a series demonstrating the evolution of moving data from the database to the screen. Before we can streamline the process, let's first understand the basics.

Three Steps

I've isolated three distinct steps to creating database-driven HTML tables.

1. Connecting to the database.

2. Getting a recordset.

3. Drawing the TABLE.

By separating each task, we will be able to reuse code and reduce the number of lines in our application. In the long run this will make your code easier to maintain.

Establishing a Database Connection

There should be one and only one place in your code where you establish a connection to a database. This means placing your connection code in an include file. Rewriting connection code on each ASP page that performs a database connection will come back to haunt you when the server name changes or your boss moves you from SQL Server to Oracle. Write this code once and if it needs updated, it can be done quickly without compromising the application.

Each database has a slightly different connection string. For this example, I'll be using a connection string to SQL Server. For more information on writing ADO connection strings read the article What's in an ADO Connection String? by John Peterson.

<% 
on error resume next 
' Create ADO Connection Object 
Set ac = Server.CreateObject("ADODB.Connection") 
' Build connection string and then Open connection
strSQL7 = "driver={sql server};server=mySQLServer;database=Northwind;uid=mas;pwd=secret" 
ac.Open strSQL7 
' Detect if there was an error connection to the database 
If err.number <> 0 Then 
  Response.Write "There was an error connecting to the database: " 
  Response.Write Err.number & " - " & Err.Description Response.End 
End If 
%>

Getting a RecordSet

There are several ways to get a recordset. You could use ADO to open up a table and then retrieve each field. You could even use a custom component to return a recordset. For this example we are going to use straight SQL.

<% 
sSQL = "SELECT CompanyName, ContactName, ContactTitle, City FROM Customers " 
Set rs = Server.CreateObject("ADODB.Recordset") 
Set rs = ac.Execute(sSQL) 
%> 

Drawing the TABLE

The Recordset holds all the information we need to draw the HTML table. In order to support code reuse, we will create a subroutine to draw the table in an include file.

Sub drawTable(rs, border, cellspacing, cellpadding, width, align) 
If NOT rs.EOF Then 
    Response.Write "<table width=""" & width & """ align=""" & align & """ border=""" & border & """ cellspacing=""" & cellspacing & """ cellpadding=""" & cellpadding & """>" & chr(10)
    Response.Write "<tr>" 
    For Each field In rs.Fields 
        Response.Write "<th>" & field.name & "</th>" 
    Next 
    Response.Write "</tr>" & chr(10) 
    '=== the altRow will allow us to set a different background color for alternate rows 
    altRow = FALSE 
    While NOT rs.EOF 
        If altRow = TRUE Then 
            Response.Write "<tr class=""altRow"">" 
        Else 
            Response.Write "<tr>" 
        End If 
        
        For each field in rs.Fields 
            thisValue = field.value 
            If len(thisValue) = 0 Then thisValue = " "         
            Response.Write "<td>" & thisValue & "</td>" 
        Next Response.Write "</tr>" & chr(10) 
        altRow = NOT altRow 
        rs.MoveNext 
    Wend 
    Response.Write "</table>" 
Else 
    Response.Write "<p>The query returned no data.</p>" 
End If 
End Sub

Last Words

The connection and drawTable code should be placed inside a single include file. Once that is done we can generate database-drive TABLES with a mere 6 lines of ASP code. I also recommend setting up a CSS file to customize the look of the TABLE. This code covers the basics. The Connection and Recordset have more methods and properties, but for this example we don't need them. In Part 2, we will improve upon the table with better column titles, paging and sorting.

Labels: ADO, ASP, Table, VBscript