Skip to main content
Participant
July 6, 2006
Answered

Using a Variable to create the SQL Query

  • July 6, 2006
  • 2 replies
  • 498 views
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.
This topic has been closed for replies.
Correct answer The_ScareCrow
cf automatically escapes the single quotes, so you need to preserve them

<cfquery name="fred" datasource="mydb">
update db_table_name set
#PreserveSingleQuotes(upst)#
where recnum = 24
</cfquery>

Ken

2 replies

fipperAuthor
Participant
July 6, 2006
Thank you. That's exactly what I needed. I searched for single quote double quote, but never came up with the function.

Mike.
The_ScareCrowCorrect answer
Inspiring
July 6, 2006
cf automatically escapes the single quotes, so you need to preserve them

<cfquery name="fred" datasource="mydb">
update db_table_name set
#PreserveSingleQuotes(upst)#
where recnum = 24
</cfquery>

Ken