Generating HTML tables for displaying data is a pretty common task these
days. Easy to do, easy to tweak, no special tools needed (though they do come in
handy), all the end user needs is a browser. How you do it can make all the
difference in how easy it is to maintain. They key to separate your display code
from the data. XML and style sheets offer some interesting ways of doing this
which I'll discuss in a later article, today we're just looking at old fashioned
HTML. Combined with our friend the ADO recordset, we can do some wonderful
things. Well, useful anyway. This may be old news to many of you, but I still
see a lot of this table code being written. If nothing else when you see it
you'll have a resource to point the offender to!
Let's start with the some what contrived example of needing to generate a web
page that displays all the files in a specific folder. We know that the folder
is updated once a day. Here is some code that shows one way of doing it that
could easily be run from a SQL job:
| Sub LoadFilesIntoTable(PathToLoad As String, OutputFileName As String)
Dim oFSO As Scripting.FileSystemObject Dim oFolder As Scripting.Folder Dim oFile As Scripting.File Dim oStream As Scripting.TextStream Dim sTemp As String Set oFSO = New Scripting.FileSystemObject If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then Set oFolder = oFSO.GetFolder(PathToLoad) 'added tab and cr/lfs to make it more readable when viewing source sTemp = "<TABLE WIDTH=100% BORDER=1>" & vbCrLf For Each oFile In oFolder.Files sTemp = sTemp & vbTab & "<TR><TD>" & oFile.Name & "</TD><TD>" & oFile.Size & "</TD></TR>" & vbCrLf Next sTemp = sTemp & "</TABLE>" Set oFolder = Nothing 'create the file, overwrite any previous version of the file Set oStream = oFSO.CreateTextFile(OutputFileName, True, False) oStream.Write sTemp Set oStream = Nothing End If Set oFSO = Nothing End Sub |
Nothing very fancy, just using the filesystem object to both get the list of
files and to write it back to disk. See how the table formatting tags are mixed
in? Now suppose the user asks for the date created to be added. We only need to
change one line, like this:
| sTemp = sTemp & vbTab & "<TR><TD>" & oFile.Name & "</TD><TD>" & oFile.Size & "</TD><TD>" & oFile.DateCreated & "</TD></TR>" & vbCrLf |
Fairly trivial right? What if the report needed to be sorted by file size? Or
able to sort on all columns? Sorting on all columns requires more effort, we'll
cover that next time too - but sorting on one column we should be able to do.
But how? Just thinking while I write, I came up with these ideas:
1) Load the info into an array, write or find some sorting code, sort it,
then do the file generation.
2) Maybe some kind of hack, use the OS to do something like this "dir
*.* >temp.txt" which will create temp.txt containing all the files in
the folder, then load & parse it back into columns, then do the file
generation.
3) Load the data into a table, sort it using an order by, create the file,
drop the table.
All will work, but being a SQL site and all, I think we'll try #3! Here is
what I came up with:
| Sub LoadFilesIntoTable2(PathToLoad As String, OutputFileName As String)
Dim oFSO As Scripting.FileSystemObject Dim oFolder As Scripting.Folder Dim oFile As Scripting.File Dim oStream As Scripting.TextStream Dim sTemp As String Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set oFSO = New Scripting.FileSystemObject If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=EG\ONE" cn.Execute "Create Table #FileList(FileName varchar(100), FileSize int, FileCreated datetime)" Set oFolder = oFSO.GetFolder(PathToLoad) For Each oFile In oFolder.Files cn.Execute "Insert into #FileList (FileName, FileSize, FileCreated) values ('" & oFile.Name & "'," & oFile.Size & ",'" & oFile.DateCreated & "')" Next Set oFolder = Nothing Set rs = cn.Execute("Select * from #FileList order by Filesize") 'added tab and cr/lfs to make it more readable when viewing source sTemp = "<TABLE WIDTH=100% BORDER=1>" & vbCrLf Do Until rs.EOF sTemp = sTemp & vbTab & "<TR><TD>" & rs.Fields("FileName") & "</TD><TD>" & rs.Fields("FileSize") & "</TD><TD>" & rs.Fields("FileCreated") & "</TD></TR>" & vbCrLf rs.MoveNext Loop sTemp = sTemp & "</TABLE>" rs.Close Set rs = Nothing cn.Execute "drop table #FileList" cn.Close Set cn = Nothing 'create the file, overwrite any previous version of the file Set oStream = oFSO.CreateTextFile(OutputFileName, True, False) oStream.Write sTemp Set oStream = Nothing End If Set oFSO = Nothing End Sub |
Now we can sort on any column if we need to, we could even use a where clause
to restrict the output. We've still got our display code mixed with the data and
I'll bet at least one person is thinking that using SQL and a table for sorting
this list is overkill?
Let's do something about making this code more generic and separating the
UI/data. For that all we need is a function that creates a table from a
recordset. Here is a very simple version:
| Public Function CreateTableFromRS(rs As ADODB.Recordset) As String
Dim J As Integer Dim sTemp As String sTemp = "<TABLE WIDTH=100% BORDER=1>" Do Until rs.EOF sTemp = sTemp & "<TR>" For J = 0 To rs.Fields.Count - 1 sTemp = sTemp & "<TD>" & rs.Fields(J) & "</TD>" Next sTemp = sTemp & "</TR>" rs.MoveNext Loop sTemp = sTemp & "</TABLE>" CreateTableFromRS = sTemp End Function |
Once we have that, we can change our earlier code to look like this:
| Set rs = cn.Execute("Select * from #FileList order by Filesize")
sTemp = CreateTableFromRS(rs) |
Now if (when!) we change the columns included, the output portion is handled
automatically. Now let me show you another advantage of this technique. Suppose
you'd like to add a header row to your table. Going back to our earlier example,
we would do something like this:
| sTemp = sTemp & "<TR><TH>FileName</TH><TH>File Size</TH><TH>Date Created<TH></TR>" |
This works, but now we are back to having to change our code in multiple
places each time we change the columns being displayed. If we use the table
technique and our new function, we can leverage some meta data to do this work
for us, like this:
| Public Function CreateTableFromRS2(rs As ADODB.Recordset) As String
Dim J As Integer Dim sTemp As String sTemp = "<TABLE WIDTH=100% BORDER=1>" sTemp = sTemp & "<TR>" For J = 0 To rs.Fields.Count - 1 sTemp = sTemp & "<TH>" & rs.Fields(J).Name & "</TH>" Next sTemp = sTemp & "</TR>" & vbCrLf Do Until rs.EOF sTemp = sTemp & "<TR>" For J = 0 To rs.Fields.Count - 1 sTemp = sTemp & vbTab & "<TD>" & rs.Fields(J) & "</TD>" & vbCrLf Next sTemp = sTemp & "</TR>" rs.MoveNext Loop sTemp = sTemp & "</TABLE>" CreateTableFromRS2 = sTemp End Function |
As you can see I'm looping through the fields collections to get the column
names. This code be easily extended to format the column sizes based on the size
of the column, do special formatting when the data type is a number or date,
etc. If you use this function in all your pages, you leverage every little
improvement you make across all of those pages! That is code reuse at it's best.
Now let's return to whether we really need SQL to do the sorting. Or even to
create a recordset. The answer is a conditional no. We can do the sorting other
ways, but having the recordset is what makes the separation between data and UI
work. We don't have to have SQL running to use a recordset though. In the next
example I'm creating a disconnected recordset, loading the data into it, then
using it's ability to filter and sort to customize the output.
| Sub LoadFilesIntoTable3(PathToLoad As String, OutputFileName As String)
Dim oFSO As Scripting.FileSystemObject Dim oFolder As Scripting.Folder Dim oFile As Scripting.File Dim oStream As Scripting.TextStream Dim sTemp As String Dim rs As ADODB.Recordset Set oFSO = New Scripting.FileSystemObject If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then Set rs = New ADODB.Recordset rs.Fields.Append "FileName", adVarChar, 100 rs.Fields.Append "FileSize", adInteger rs.Fields.Append "FileCreated", adDBTimeStamp rs.Open Set oFolder = oFSO.GetFolder(PathToLoad) For Each oFile In oFolder.Files rs.AddNew rs.Fields("FileName") = oFile.Name rs.Fields("FileSize") = oFile.Size rs.Fields("FileCreated") = oFile.DateCreated rs.Update Next Set oFolder = Nothing 'sort it rs.Filter = "Filename like 's%'" rs.Sort = "FileSize desc" rs.MoveFirst 'probably should go in the createtable function sTemp = CreateTableFromRS2(rs) rs.Close Set rs = Nothing 'create the file, overwrite any previous version of the file Set oStream = oFSO.CreateTextFile(OutputFileName, True, False) oStream.Write sTemp Set oStream = Nothing End If Set oFSO = Nothing End Sub |
Whether you create a table, do a select from an existing table, or create a
disconnected recordset depends on the circumstances of course. The key is that
by always pushing our data into a recordset we can leverage our display code
heavily and that is worth something.
In a follow up article we'll look at how we can use XML to accomplish the
same thing and add the ability to do client side sorting. As always I look
forward to your comments. Whether you agree or disagree, take a min to add your
thoughts to the discussion forum attached to the article, other readers will
appreciate the additional insight only you can provide.
