define "bombs" and do a <cfoutput> on #column_string# to see exactly
what SQL you're generating.
On 6/28/07, david cowen <[EMAIL PROTECTED]> wrote:
> 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:282515
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
