Classic ASP - Draw SQL Tables

Place the following code into an include file and it will draw a table. It can display either horizontal (HTable) or vertical (VTable) and has many options as parameters. Make sure you set strconn to a valid connection i.e strConn = "Provider=MSDASQL;DSN=database;"


Const adOpenStatic = 3
Const adUseClient = 3

'///////////////////////////////////////////////////////////////
'//Function list
'///////////////////////////////////////////////////////////////
'//Draw a Vertical table
'//Function VTable(objNum,strSQL,TWidth,DisplayNames,Alignment,CellShading,Border,CellPadding,CellSpacing)
'//
'//Draw a Horizontal table
'//Function HTable(objNum,strSQL,TWidth,DisplayNames,Alignment,CellShading,Border,CellPadding,CellSpacing)
'//
'//

Function OpenDB(objNum,strConn,strSQL)

'// Create the ADO objects:
Set objConn(objNum) = Server.CreateObject("ADODB.Connection")
Set objRS(objNum) = Server.CreateObject("ADODB.Recordset")
'// Connect to the database using OLE DB:
objConn(objNum).Open strConn
'// Use a client-side cursor so we can sort later and so we can create a disconnected recordset:
objRS(objNum).CursorLocation = adUseClient
objRS(objNum).Source = strSQL
'// Use a static cursor so we can navigate freely:
objRS(objNum).CursorType = adOpenStatic
Set objRS(objNum).ActiveConnection = objConn(objNum)
'// Open the report's recordset:
objRS(objNum).Open
'// Disconnect the recordset from the connection:
Set objRS(objNum).ActiveConnection = Nothing
'// Close the connection to the database:
objConn(objNum).Close
End Function

Function HTable(objNum,strSQL,TWidth,DisplayNames,Alignment,CellShading,Border,CellPadding,CellSpacing)
if TWidth = "" then TWidth="100%"
if DisplayNames = "" then DisplayNames = "Yes"
if Alignment = "L" then Alignment = "Left"
if Alignment = "C" then Alignment = "Center"
if Alignment = "R" then Alignment = "Right"
if CellShading = "" then CellShading = "No"
if Border = "" then Border=0
if CellPadding = "" then CellPadding=0
if CellSpacing = "" then CellSpacing=0

Call OpenDB(objNum,strConn,strSQL)

If Not (objRS(objNum).BOF And objRS(objNum).EOF) Then

objRS(objNum).AbsolutePage = 1
objRS(objNum).PageSize= objRS(objNum).RecordCount
intDisplayRows = objRS(objNum).PageSize - 1
intColMax = objRS(objNum).Fields.count - 1
response.write("")
N=0

For intCol = 0 To intColMax
if Len(ObjRs(objNum).Fields(intCol).Value) > 0 then
MyName = ObjRs(objNum).Fields(intCol).Name
MyValue = ObjRs(objNum).Fields(intCol).Value

if MyValue <> "000000" then

if CellShading = "Yes" then
If (N/2)=Int(N/2) Then
response.write("")
Else
response.write("")
end if
N=N+1
else
response.write("")
end if

if DisplayNames = "Yes" then
response.write("")
response.write(ObjRs(objNum).Fields(intCol).Name)
response.write("")
end if


Select Case MyName
Case "Scheduled Date"
response.write("< align=" + Alignment + ">")
response.write(MyValue)
response.write(" (") & (WeekdayName(Weekday(MyValue))) & ")"
response.write("")
Case "AM PM"
If MyValue <> "" then
response.write("")
If MyValue="083000" Then
response.write("AM")
Else If MyValue="123000" Then
response.write("PM")
end if
end if
response.write("")
end if
Case Else
response.write("")
response.write(ObjRs(objNum).Fields(intCol).Value)
response.write("")
End Select
end if
end if
Next
response.write("")
response.write("")
else
response.write("&nb sp")
End If
ObjRS(objNum).Close
End Function

Function VTable(objNum,strSQL,TWidth,DisplayNames,Alignment,CellShading,Border,CellPadding,CellSpacing)
if TWidth = "" then TWidth="100%"
if DisplayNames = "" then DisplayNames = "Yes"
if Alignment = "L" then Alignment = "Left"
if Alignment = "C" then Alignment = "Center"
if Alignment = "R" then Alignment = "Right"
if CellShading = "" then CellShading = "No"
if Border = "" then Border=0
if CellPadding = "" then CellPadding=0
if CellSpacing = "" then CellSpacing=0

Call OpenDB(objNum,strConn,strSQL)

If Not (objRS(objNum).BOF And objRS(objNum).EOF) Then

objRS(objNum).AbsolutePage = 1
objRS(objNum).PageSize= objRS(objNum).RecordCount
intDisplayRows = objRS(objNum).PageSize - 1
intColMax = objRS(objNum).Fields.count - 1
response.write("")
N=0
if CellShading = "Yes" then
If (N/2)=Int(N/2) Then
response.write("")
Else
response.write("")
end if
N=N+1
else
response.write("")
end if


if DisplayNames = "Yes" then
For intCol = 0 To intColMax
response.write("")
response.write(ObjRs(objNum).Fields(intCol).Name)
response.write("")
next
response.write("")
else
response.write("")
end if

For intRow = 0 To intDisplayRows

if CellShading = "Yes" then
If (N/2)=Int(N/2) Then
response.write("")
Else
response.write("")
end if
N=N+1
else
response.write("")
end if

For intCol = 0 To intColMax
response.write("")
response.write(ObjRs(objNum).Fields(intCol).Value)
response.write("")
Next
response.write("")
ObjRs(ObjNum).MoveNext
Next

response.write("")
else
response.write("&nbs p")

End If
ObjRS(objNum).Close
end function


Now all you have to do is call the function and pass it some SQL and parameters and it will draw a table with the results!

Sorry for it being a bit messy but this blog isn't wide enough to display the code properly so it's a bit hard to understand. I have also had to add spaces so that the blog doesn't parse the code.