ColdFusion database loop on similar column names

[brorobo]

I have a Microsoft SQL 2000 database and a table with column names Objective1, Objective2, Objective3, (etc.) and I want to loop through each of these columns in the output so that they display:

#Objective1#
#Objective2#
#Objective3#
(etc.)

Is there a way to loop through the name of the column and the number?

Something like this obviously won't work, but it's kind of what I'd like to do:

<cfloop index="x" from="1" to="3" step="1">
<p>#objective[x]#</p>
</cfloop>

Any ideas?

Thanks in advance for your time.

[Gjslick]

What's your query name? I believe you can use the query name, and then grab the columns using array notation.

Ex:

<cfquery name="myQuery" datasource="dsn">
  SELECT * FROM table
</cfquery>

<cfoutput query="myQuery">
  <cfloop from="1" to="3" index="i">
    #myQuery[ 'objective' & i ]#<br>
  </cfloop>
</cfoutput>

Try that and let me know how it goes.

-Greg

[brorobo]

Thanks for replying

I tried the code you provided (my query name is GetObjectives) and I get an error:
Complex object types cannot be converted to simple values.

<cfoutput query="GetObjectives">
  <cfloop from="1" to="3" index="i">
    #GetObjectives[ 'objective' & i ]#<br>
  </cfloop>
</cfoutput>

[Gjslick]

Oh man, sorry about that. I don't have a CF server running at the moment to test on. But this code of going directly into the query resultset data should work:

<cfquery name="myQuery" datasource="dsn">
  SELECT * FROM table
</cfquery>

<cfset numRecords = myQuery.recordCount>
<cfloop from="1" to="#numRecords#" index="i">
  <cfloop from="1" to="3" index="j">
    #myQuery[ 'objective' & j ][ i ]#<br>
  </cfloop>
</cfoutput>

Hope that works, but if not, let me know and I'll re-install a local server when I get home

-Greg

[brorobo]

I had to modify your code a little, but it worked!

<cfoutput>
<cfloop from="1" to="#numRecords#" index="i">
  <cfloop from="1" to="3" index="j">
    #GetObjectives[ 'objective' & j ][ i ]#<br />
  </cfloop>
</cfloop>
</cfoutput>

Thanks!

[Gjslick]

Ah whoops, left a </cfoutput> at the end instead of a </cfloop> But I'm glad it worked!

All the best,
Greg