Building a Database Driven Table in Classic ASP Part 1

The original title of this post 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.