Using a Variable to create the SQL Query

[fipper]

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.

[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

[fipper]

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

Mike.