Retrieve ADO Recordset Field names (Classic ASP)

Question

I wonder if someone can help:

Long story short, I'm using MSSQL2005 to build a Pivot table. The data being examined is limited by date range (All data for 1 week starting from the nearest Monday to the date selected)

When I run the Stored Proc and pass it a date, I get The correct table back eg:

Time	1 Jan 09	2 Jan 09	3 Jan 09	...
09:00	0	9	25	...
09:30	8	27	65	...
10:00	20	44	112	...

The only problem I have is that the column headers will vary based on both the date passed in to the SP (The desired view date) and the logic inside the SP (which forces the left-hand column to be the nearest Monday to the date specified).

This means that when I display the results to the user, I (currently) need to duplicate the date-checking logic in classic ASP [easy but a maintainability fail]

What I really need is a way of retrieving the column names from the recordset itself.

Can someone please point me in the right direction?

I've Googled but all the results I get seem to relate to reading a Table Schema - which doesn't help in this case as my table is being generated on the fly in memory.

Many thanks in advance for any help you can provide

Solution 1

Given an ado record set you could do roughly the following (This is in psuedo code):

foreach (field in rs.Fields)
{
    alert(field.Name);
}

This will give you the name of the field check out this documentation.

Solution 2

Something like this ought to do it:-

 <table>
   <thead>
      <tr>
         <%For Each fld in rst.Fields%>
           <th><span><%=Server.HTMLEncode(fld.Name)%></span></th>
         <%Next %>
      </tr>
   </thead>
   <tbody>
 <%
   Do Until rst.EOF
      OutputRow rst.Fields
      rst.MoveNext
   Loop
 %>
   </tbody>
 </table>

 Sub OutputRow(fields)
 %>
      <tr>
         <%For Each fld in fields%>
           <td><span><%=Server.HTMLEncode(fld.Name)%></span></td>
         <%Next %>
      </tr>
 <%
 End Sub
 %>

Comments

[Basic]

That was embarrassingly simple - Use the .Name property. Needless to say it worked perfectly. Thank you.

[iconoclast]

Given that .Name is not documented (e.g.) here, I wouldn't be too embarrassed.

[JoshBerke]

@iconoclast it is documented here w3schools.com/ado/ado_ref_field.asp you were looking at the recordset not the field.

[Ben Hamilton]

I'm using this for my code but the column headings are displayed on every row?

[Albofish]

@BenHamilton Bit late, but edit the Sub to HTMLEncode fld.Value not fld.Name