How can I display Recordset data in an HTML TABLE? How can I get the names of all the fields in a table? [Print this FAQ]
Answer:
Part of the challenge of creating data-driven ASP pages is presenting the data in an easy-to-read format. What good is information if it is difficult to digest?
One of the best ways to display database information is to place it in an HTML TABLE. This can be done with a rather generic chunk of code, outlined below. The code makes use of the Fields collection of the Recordset object. The Fields collection contains a Field object for each column returned by the Recordset. The Field object has properties like Name (the name of the column) and Value (the value of the data in that column). To list all of the names of the database column, one could use code like:
'Assumes we have a populated recordset named objRS Dim objField For Each objField in objRS.Fields Response.Write objField.Name & "<BR>" Next
We'll use this technique to display both the column headers in our table (TH tags) and the data in our TD tags. The generic chunk of code below can be used to easily dump a populated Recordset's contents into an HTML TABLE.
<% 'Assume we have already made our connection to the db and already 'have our populated Recordset, objRS 'First, display the TABLE header info: Response.Write "<TABLE BORDER=0 CELLSPACING=1>"
'Display table headings for each column in the Recordset Dim objField Response.Write "<TR>" & vbNewLine For Each objField in objRS.Fields Response.Write "<TH>" & objField.Name & "</TH>" & vbNewLine Next Response.Write "</TR>" & vbNewLine
'Now, loop through the recordset, displaying TR/TD tags Do While Not objRS.EOF 'Write the TR Response.Write "<TR>" & vbNewLine
'Now loop through the recordset Fields For Each objField in objRS.Fieldsd Response.Write "<TD>" & objField.Value & "</TD>" & vbNewLine Next
'Close the TR tag Response.Write "</TR>" & vbNewLine
'Advance the recordset objRS.MoveNext Loop
'Close the table tag... Response.Write "</TABLE>" & vbNewLine %>
And there you have it! One caveat: if you are returning a lot of rows in your Recordset object, your table will grow to be quite large, too large to be of any use (how can someone comprehend thousands of rows of information spit out to them at once?). To overcome this problem, you will need to page your data. For more information on this be sure to read: Paging through Records Using a Stored Procedure.
Do you have a FAQ you'd like to suggest?
Suggestions? Comments? If so, send it in!
Also, if you'd like to be a FAQ Admin (creating/editing FAQs),
let me know! If you are looking for other FAQs, be
sure to check out the 4Guys
FAQ and Commonly Asked Messageboard Questions!
