RecordsetToHTMLTable - Convert an ADO Recordset to a HTML table

' Create a HTML table from a recordset
'
' set the TableAttribs argument to a suitable value
'     (eg "BORDER=1") to modify the table's standard layout
' you should omit the NullValues argument if you want that null
'     values are displayed in empty cells
' set the ShowFieldNames to True to display field names in boldface
' set the IncludeWhiteSpace argument to True if you want to produce
'     a longer and less efficient (but more readable) output

Function RecordsetToHTMLTable(rs As ADODB.Recordset, _
    ByVal TableAttribs As String, Optional ByVal NullValues As String = _
    "&nbsp;", Optional ByVal ShowFieldNames As Boolean, _
    Optional ByVal IncludeWhiteSpace As Boolean) As String
    Dim res As String
    Dim fld As ADODB.Field
    Dim tmp As String 
    Dim lf As String, tb As String
    
    ' fill these variables only if spaces are to be kept
    If IncludeWhiteSpace Then
        lf = vbCrLf
        tb = vbTab
    End If
    
    ' prepare the <TABLE> tag
    res = "<TABLE " & TableAttribs & ">" & lf
    
    ' show field names, if required
    If ShowFieldNames Then
        res = res & tb & "<HEAD>" & lf
        For Each fld In rs.Fields
            res = res & tb & tb & "<TD><B>" & fld.Name & "</B></TD>" & lf
        Next
        res = res & tb & "</HEAD>" & lf
    End If

    ' get all the records in a semi-formatted string
    tmp = rs.GetString(, , "</TD>" & lf & tb & tb & "<TD>", _
        "</TD>" & lf & tb & "</TR>" & lf & tb & "<TR>" & lf & tb & tb & "<TD>", _
        NullValues) 
    ' strip what has been appended to the last cell of the last row
    tmp = Left$(tmp, Len(tmp) - Len(lf & tb & "<TR>" & lf & tb & tb & "<TD>"))
    
    ' add opening tags to the first cell of the first row of the table
    ' and complete the table 
    RecordsetToHTMLTable = res & tb & "<TR>" & lf & tb & tb & "<TD>" & tmp & lf _
        & "</TABLE>"
    
End Function

Francesco Balena

Submit a Tip

Browse "Web Development" Tips

Browse All Tips

0 Comments (click to add your comment)

Comment and Contribute