Answered
Using a Variable to create the SQL Query
I need to create a "dynamic" Update query. I want to store
the meet of the command in a variable and then reference the
variable in in query.
Example:
<cfquery name="fred" datasource="mydb">
update db_table_name set
pbname = 'Fred Flintstone',
pbnumber = '555-555-1234'
pbage = 25
where recnum = 24
</cfquery>
I would like use code this:
<cfset upst = "pbname = 'Fred Flintstone', pbnumber = '555-555-1234', pbage = 25">
<cfquery name="fred" datasource="mydb">
update db_table_name set
#upst#
where recnum = 24
</cfquery>
When I run this, I get the following error message:
Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Fred Flintstone''.
The SQL line is:
update db_table_name set pbname = ''Fred Flintstone'', pbnumber = ''555-555-1234'', pbage = 25 where recnum = 24
I know its hard to see, but the '' are 2 ' not 1 " . I have no idea why Coldfusion (or maybe the ODBC driver??) is placing the 2nd ' in the command which causes the errors.
Can anyone shed some light on this topic?
While this is a simple example, my application is far more complex. I have over 50 fields in the udpate and depending on changes to the form values, I may need to update all the fields, some of the fields or NONE of the fields.
I can use <cfif> to test if any fields have changed and if so, include them in the update command, but if NONE of the fields have changed, I would have an empty update command and therefore get an error. I want to avoid having to test for changes twice (once to determine if I am doing the update and twice to perform the update).
Thanks,
Mike.
Example:
<cfquery name="fred" datasource="mydb">
update db_table_name set
pbname = 'Fred Flintstone',
pbnumber = '555-555-1234'
pbage = 25
where recnum = 24
</cfquery>
I would like use code this:
<cfset upst = "pbname = 'Fred Flintstone', pbnumber = '555-555-1234', pbage = 25">
<cfquery name="fred" datasource="mydb">
update db_table_name set
#upst#
where recnum = 24
</cfquery>
When I run this, I get the following error message:
Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Fred Flintstone''.
The SQL line is:
update db_table_name set pbname = ''Fred Flintstone'', pbnumber = ''555-555-1234'', pbage = 25 where recnum = 24
I know its hard to see, but the '' are 2 ' not 1 " . I have no idea why Coldfusion (or maybe the ODBC driver??) is placing the 2nd ' in the command which causes the errors.
Can anyone shed some light on this topic?
While this is a simple example, my application is far more complex. I have over 50 fields in the udpate and depending on changes to the form values, I may need to update all the fields, some of the fields or NONE of the fields.
I can use <cfif> to test if any fields have changed and if so, include them in the update command, but if NONE of the fields have changed, I would have an empty update command and therefore get an error. I want to avoid having to test for changes twice (once to determine if I am doing the update and twice to perform the update).
Thanks,
Mike.