How can I get all Field Names or Column Names from a recordset or database table ? ..or.. How can I write a general "table dump" for all records and fields in a recordset or table? [Print this FAQ]
Answer:
This question has been answered many, many times in many, many places, and I'm not going to go into a long winded explanation, since some of the other pages and sites do such a good job of it.
But for those who want a quick and dirty answer...
The first For...Next loop below shows how to get the names of the fields from the recordset (and you simply ask for all fields in a table in your SQL clause to show all the fields in a given table).
The Do...Loop and second For...Next show how to get all the values for all the fields in all records.
<% Set conn = Server.CreateObject("ADODB.Connection") conn.Open "... put your connection string here ..."
Set RS = Server.CreateObject("ADODB.RecordSet") RS.Open "... put your tablename *or* SQL statement here ...", conn
Response.Write "<TABLE Border=1 CellPadding=5>" & vbNewLIne Response.Write "<TR>" & vbNewLine ' RS.Fields is the collection of fields associated with the recordset... ' The count given is correct, but since the fields are numbered ' starting at zero, we have to subtract one to get the maximal field number: For fnum = 0 To RS.Fields.Count-1 ' Naturally, each element in the Fields collection is ' an ADODB.Field object. And the Field object has various ' properties, including...ta da!...its Name: Response.Write "<TH>" & RS.Fields(fnum).Name & "</TH>" & vbNewLine Next Response.Write "</TR>" & vbNewLine
' Then, if you want to also DUMP the entire table: ' ' You simply do all records until you reach the end (EOF): Do Until RS.EOF ' The logic here is the same as for displaying the ' field names, but now we do the values for one record... Response.Write "<TR>" & vbNewLine For fnum = 0 To RS.Fields.Count-1 ' again, one of the ADODB.Field properties is Value, so... Response.Write "<TD>" & RS.Fields(fnum).Value & "</TD>" & vbNewLine Next Response.Write "</TR>" & vbNewLine RS.MoveNext Loop Response.Write "</TABLE>" & vbNewLine
RS.Close conn.Close %>
Quick and dirty. Just plug in your connection string and your table name (or SQL query) and it's ready to use.
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!
