I was recently working on a website for a client and needed a simple method to read the column names and records from a database table and return them as a semantically correct HTML table. After doing a little searching I found a forum post with the code I was looking for. I've adapted that code and also polished it up a bit for all to consume.
Recordset Function In ASP
The function takes any SQL and returns it as an HTML table. The begining part of the function sets up the opening TABLE element and also writes out the column names as TH elements.
function ReturnHTMLTable(query)
set rs = cn.Execute(query)
rs.MoveFirst
response.Write "<table class=""data"" cols=""" & rs.Fields.Count & """>"
response.Write "<tr>"
For Each oField In rs.Fields
response.Write "<th>" & oField.Name & "</th>"
Next
response.Write "</tr>"
Looping Through the ASP Recordset
Next the function loops through the recordset writing out values under the appropriate column name.
Do While Not rs.EOF
response.Write "<tr>"
For Each oField In rs.Fields
response.Write "<td>"
If IsNull(oField) Then
response.Write " "
Else
response.Write oField.Value
End If
response.Write "</td>"
Next
rs.MoveNext
response.Write "</tr>"
Loop
Finally it writes out the closing TABLE tag.
response.Write "</table>"
Putting It All Together
I've been looking for a simple function like this for a long time so perhaps I'll help a few others out with this. I've mostly used it to show on result pages after a user adds a new item to a database via a web form.
function ReturnHTMLTable(query)
set rs = cn.Execute(query)
rs.MoveFirst
response.Write "<table class=""data"" cols=""" & rs.Fields.Count & """>"
response.Write "<tr>"
For Each oField In rs.Fields
response.Write "<th>" & oField.Name & "</th>"
Next
response.Write "</tr>"
Do While Not rs.EOF
response.Write "<tr>"
For Each oField In rs.Fields
response.Write "<td>"
If IsNull(oField) Then
response.Write " "
Else
response.Write oField.Value
End If
response.Write "</td>"
Next
rs.MoveNext
response.Write "</tr>"
Loop
response.Write "</table>"
end function
Using The Function
The function can be used by selecting the columns you want by passing the SQL to the function like so:
ReturnHTMLTable("SELECT id, firstname, lastname, datetime FROM mytable")
You can also show the columns in a different order by changing the order of your columns in your SQL statement.
