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.
