Help with Recordsets and Databases

YoungLuke · November 11th, 2003, 07:47 PM

If I was using a SQL database and was running a query on my ASP page like SELECT * FROM CLIENTS and wanted to output the whole thing, and did not know the table field names how would I do that. I have a web site where you can dynamically generate a query, by selecting the table, then the fields you want, and then under what parameters to pull the information. But if the user selects "All" on the field options page how do I output the fields. Any thing would help thanks.

pgtips · November 12th, 2003, 04:40 AM

I assume you would be using an ADO recordset to get the query results? If so, the recordset object has a Fields collection which you can iterate through. If say, you wanted to output the field names as column headers you could use the Name property of each field, then the actual data would be output using the Value property. Something like this:

Code:

' code here to run query, get recordset back and check it has data
Dim fld
Response.Write "<table><tr>"
' write out col headings
For Each fld in recordsetName.Fields
    Response.Write "<td>"
    Response.Write fld.Name
    Response.Write "</td>"
Next
Response.Write "</tr>"

' write out col data
Do While Not recordsetName.EOF
    Response.Write "<tr>"
    For Each fld in recordsetName.Fields
        Response.Write "<td>"
        Response.Write fld.Value ' maybe deal with Nulls here too
        Response.Write "</td>"
    Next
    Response.Write "</tr>"
    recordsetName.MoveNext
Loop

hth
Phil