Well, without trying the code out or looking real hard at it.. I see that you have quotes around #column_strings# in your cfquery. Remove those and see what happens first.
The next obvious step would be to take a look at the outputted (is that a word?) SQL statement in the error message. ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: david cowen [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 6:44 PM To: CF-Talk Subject: how can I dynamically build field names string in a sql update statement? 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 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282516 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
