Retrieve ADO Recordset Field names (Classic ASP)

Question

Hi all,

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 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 | ...

(Apologies for the atrocious "table" formatting).

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

Answer 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.

Answer 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
 %>