Getting column data without column names

[Jared@Itron]

Hello all,

I'm trying to write a coldfusion program to display account data such as
name, address, phone #, etc. I retrieve this data from an SQL query. The
thing is, I want to make the code more robust by allowing a variable number of
columns. Namely, if someone adds another column to the SQL table such as
hobbies, I don't want to have to change the code. I found an SQL query to get
the colum names in the order they appear in the table. I search for the record
in the table by account number. My question is, when I get the one row back
from the query is there a way to loop through that row without knowing the
column names, thereby allowing the code to automatically pick up on new
columns? Thanks in advance for your time and help. This is a great forum for
getting good answers.

-Jared

[paross1]

This is a simple example of dynamically displaying your column names and data
in a table. They will, however, be listed in alphabetical order.

<cfquery name="Q1" datasource="your_dsn">
SELECT *
FROM your_table
WHERE whatever
</cfquery>

<cfif IsDefined("Q1.ColumnList")>
<table border="1">
<tr><cfloop list="#Q1.ColumnList#" index="col" delimiters=",">
<th align="left" nowrap><cfoutput>#col#</cfoutput></th>
</cfloop></tr>
<cfoutput query="Q1">
<tr><cfloop list="#Q1.ColumnList#" index="col">
<td align="left" nowrap>#Q1[col][CurrentRow]#</td></cfloop></tr>
</cfoutput>
</table>
</cfif>

Phil

[Dan Bracuk]

Originally posted by: Jared@Itron
Hello all,

I'm trying to write a coldfusion program to display account data such as
name, address, phone #, etc. I retrieve this data from an SQL query. The
thing is, I want to make the code more robust by allowing a variable number of
columns. Namely, if someone adds another column to the SQL table such as
hobbies, I don't want to have to change the code. I found an SQL query to get
the colum names in the order they appear in the table. I search for the record
in the table by account number. My question is, when I get the one row back
from the query is there a way to loop through that row without knowing the
column names, thereby allowing the code to automatically pick up on new
columns? Thanks in advance for your time and help. This is a great forum for
getting good answers.

-Jared

You had better hope the new field is not a date field.

[Jared@Itron]

Why is that? Also, once I have this dynamic list of column names and text
input fields with the current data already in them, how do I Update the SQL
table? Do I have to dynamically reference each column in that row to put the
new data in them? I have to name the text inputs dynamically to give them
different names as in name="param#count#". I can't use that within two other #
signs to update the table as in name=#param#count##.

[Dan Bracuk]

Do a simple test.
Create a table add a record, and run paross1's code against it. Then add a date field, set it to something and rerun paross1's code.