Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Generating HTML Tables

By Andy Warren,

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.

Total article views: 5794 | Views in the last 30 days: 2
 
Related Articles
FORUM

conversion column string to date in ssis

conversion column string to date in ssis

FORUM

combine column values into a string...

combine column values into a string...

FORUM

Split String

Split comma delimitted String Into Columns

FORUM

Writing strings with chars 128-160 to an varchar column

Writing strings with chars 128-160 to an varchar column

FORUM

Update Datetime Column to Empty String - No Error - Strange Results

Update Datetime Column to Empty String - No Error - Strange Results

Tags
ado    
miscellaneous    
programming    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones