How to I create blank columns using QueryAddColumn?

[Haitashi]

Hi:

I need to dymanically create a number of columns using the queryAddColumn function.

I was going to use the loop below. That way each column would have a generic dynamically created name.

<cfloop index="I" from="1" to="#Variables.qProgramDetails.recordcount#">
    <cfset temp = QueryAddColumn(#this.RecordSet#, "Detail#I#", "VarChar", newArray) />
</cfloop>

The problem I'm encountering is that when running that code I get an error that says: "The column name (Detail1) that you specified already exists in this query".

I'm guessing something isn't quite right since apparently on the second iteration of this loop the column name isn't updating to "Detail2".

Any ideas? As always, thanks in advance!! =)

[acoder]

...or perhaps the query already has a column named Detail1. Is this.RecordSet an empty query before the loop?

[cymark]

Try this:

<cfloop ....>
 
   <cfset col = "Detail"&#I#>
   <cfset temp = QueryAddColumn(#this.RecordSet#, "#col#", "VarChar", newArray) />
 
</cfloop>

Or try dynamic variables - lookup Evaluate function.

Good luck - CM

[acoder]

cymark, welcome to Bytes and thanks for your contribution.

You could do away with the pound signs in the cfset statement, i.e.

<cfset col = "Detail"& I>

In this case, however, the code that you've posted won't make a difference because it does recognise "#I#" to be 1 (see error message).

[cymark]

Hi,

Sorry was a bit quick there.

The following seems to work OK:

<cfset myQuery = QueryNew("")>
 
<cfset FastFoodArray = ArrayNew(1)>
<cfset FastFoodArray[1] = "French Fries">
<cfset FastFoodArray[2] = "Hot Dogs">
<cfset FastFoodArray[3] = "Fried Clams">
<cfset FastFoodArray[4] = "Thick Shakes">
 
 
<cfloop index="I" from="1" to="5">
 
    <cfset col = "Detail"&I>
 
     <cfset temp = QueryAddColumn(#myQuery#, #col#, "VarChar", FastFoodArray) />
</cfloop>
 
 
<cfdump var="#myQuery#">

Creates cols with Detail1, .. to Detail5

Hope it helps...