If I remove the quote marks, then the program fails on line 0 of this routine; i.e., this code does not execute. With the quote marks, the routine gets executed but fails and there is a sql statement in the resulting error message. The sql statement has quote marks around the "set field1=0, set field2=1, set field3=0" portion.
-----Original Message----- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 6:20 PM To: CF-Talk Subject: RE: how can I dynamically build field names string in a sql update statement? 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> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282520 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
