Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

Outputting database information to an HTML table is pretty easy: everyone who reads

4guys has probably done this dozens of times. And that’s part of the problem. Every time you need to display a table that contains information from a database, you probably just write out the code necessary to do this each time.  But if you are like me you are probably a little lazy, and that’s why I created a handy script to easily write out HTML tables.

Here was my situation: I was writing an application to analyze our webserver data (the tools that come with IIS aren’t very good in my opinion). All of my data is updated from another ASP page into an Access database, and then I needed to output the final data in a way that my boss (the MIS Manager) could view at any time. So I decided to create an ASP file and stick in on our Intranet instead of simply exporting the database to Excel.

However, I wanted to be able to add more fields in my Access database without changing the ASP page, and to easily total the data in each column. So I solved both problems by writing a little function to easily generate a simple HTML table with stats from a database, with the bottom row containing the totals for each column. This will accommodate any number of columns.

Because I wanted to use this in multiple pages, I encapsulated the code in a SUB in a separate file called "data-html.asp". The code and concept for this is very simple, so I’ll just explain it by jumping in to the code for it. (The script below assumes you have opened a connection to the DB and created a recordset named rsData).

This is the basic initializing of the variables. Each position of arrTotals will contain the current value of the corresponding column. I set the array to 50 arbitrarily, because I know there won't be more than 50 columns in any of my databases, and I don't want to waste the resources to ReDim the array. You can set this at a lower value if you don't need that many columns. Note that the variable count is set to one less than the number of fields. Later when I reference the recordset fields using integers, and since recordset indexes begin at zero we need to subtract one from the total.

The first thing to do is write out the table HTML and populate the header. I use a simple for loop to create the header, with the database column name as the header name.

Response.write("<TABLE border='1' cellpadding='3'>" & vbCrLf)
Response.write("<THEAD>" & vbCrLf)

for r = 0 to count 'populate the header 
	Response.Write("" & rsData(r).Name & " " & vbCrLf) 
Next
Response.Write("<THEAD>" & vbCrLf)

Now for the heart of the script. Here I use a Do ... Loop to run through the Recordset, with a For loop to write out the cells. Notice that the first cell is written before the For loop. In my database the first column is a date, and doesn’t need to be added (in fact, can’t be added). The For loop runs through each column of data and writes the value, while the array tallies the data by adding the current value to array.

Do Until rsData.EOF
	Response.Write("<tr>" & vbCrLf)
	Response.Write("<td>" & rsData(0) &  "</td>" & vbCrLf)
	For r = 1 to count
		Response.Write("<td>" & rsData(r) & "</td>" & vbCrLf)
		arrTotals(r) = arrTotals(r) + rsData(r)
    	Next
	Response.Write("</tr>" & vbCrLf)
    	rsData.MoveNext
Loop

Now to write the final row of the table, and output the totals for each column using another For loop, and close the table.

Response.Write("<tr>" & vbCrLf) 

Response.Write("<td><b>Totals</b></td>")
for r = 1 to count
	Response.write("<td><b>" & arrTotals(r) & "</b></td>" & vbCrLf )
Next
Response.Write(" </tr>" & vbCrLf)
Response.Write("</table>")

End Sub

%>

Finally, here’s how you can put all together with another asp page. In my app, the user chooses which type of data they want to see (browser stats, page hits, etc), and then all there is to do is open the recordset and output the data (because other pages have already updated the database with the current stats). This is one limitation to this script. You can’t really do anything with the data before you output it (unless you update the database before calling the Sub).

The data will look something  like this:

Now, if you really wanted to, you could incorporate these two pages into one, and use that as a generic HTML Table generator, but chances are you'll have reasons to keep them seperate.  But either way, the data-html.asp script will save you time.

A few things to note. If you have a large number of fields the table will probably go beyond the width of your screen, so you will end up having to scroll horizontally to see all of it.  Also, when using this script your database should probably have meaningful column names to make the column headers more understandable.  Additionally, if you actually want ASP to export the data into Excel to do more than just total the columns, I recommend you check out the ASP Today article on creating Excel files in ASP. I found it very easy to change my code to do this.

Happy Programming!

Attachments: