how can I dynamically build field names string in a sql update statement?

[david cowen]

Hi,

I'm trying to construct a dynamic sql server update statement on a table with a 
large number of fields, most of them type "bit".  First I evaluate all the 
"yes/no" fields and convert them to 1/0.  Then I build a string of column 
(field) names and values, e.g, {name of field1}=0, {name of field2}=1, etc., 
iterating through all of the fields for the table.  

Then I try to execute the query:

<cfquery name="FindRequest" datasource="rhcaccesssql">  
        Update Requests Set 
        #column_string#
        Where RequestID = 
        #FindCurrent.requestID#
</cfquery> 

However, Coldfusion 6.0 bombs on #column_string#.  How can I accomplish this?


Thanks much for your help!


Here is the code:


<cfset numvar=0> 
<cfset charvar=""> 
<cfset column_namestring=""> 
<cfset column_valstring=""> 
<cfset column_string="">
<cfset query_string="">
<cfquery name="bitfields" datasource="rhcaccesssql">
        select column_name, data_type from information_schema.columns where
        table_name='Requests' and data_type='bit'
</cfquery>                      

<!------------------------------->
<cfloop query="bitfields" startrow="2"> 
        <cfset column_namestring="," & #column_name# & "="> 
        <cfif data_type eq "varchar" or data_type eq "nvarchar">    
                                <cfset charvar = evaluate(#column_name#)>       
             
                <cfset column_valstring="'" & charvar & "'"> 
        <cfelse> 
                <cfset numvar=evaluate(#column_name#)> 
                <cfif data_type eq "bit"> 
                        <cfif numvar eq "yes">          
                                <cfset numvar = 1> 
                        </cfif> 
                        <cfif numvar eq "no"> 
                                <cfset numvar = 0> 
                        </cfif> 
                </cfif> 
                <cfset column_valstring=numvar> 
        </cfif> 
                <cfset column_string = column_string & column_namestring & 
column_valstring>
</cfloop> 
<cfset column_string=mid(column_string,2,len(column_string)-1)> 
<cfquery name="FindRequest" datasource="rhcaccesssql">  
        Update Requests Set 
        "#column_string#"
        Where RequestID = 
        #FindCurrent.requestID#
</cfquery> 





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ 

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282513
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4