Using variable for update statement?

[mkiv]

I am trying to use a variable for a update statement. When I try to use #chr(39)# to single quote the date I get a ODBC syntax error. Here is a sample of what I am basically doing. Any suggestions? thx

<cfset fname = 'John'>
<cfset lname = 'Smith'>
<cfset startdate = '12/04/2001'>
<cfset enddate = '08/05/2003'>
<cfset empno = 313>
<cfset MYSTRING = 'UPDATE EmployeesBackup SET empfirst = #chr(39)##fname##chr(39)#, emplast = #chr(39)##lname##chr(39)#, startdt = #chr(39)##startdate##chr(39)#, enddt = #chr(39)##enddate##chr(39)# WHERE empid = #empno#'>

<cfquery name=&quot;test&quot; datasource=&quot;tci&quot;>
#MYSTRING#
</cfquery>

 

[Sarky78]

mkiv,

Could you not change your assignment of mystring to something like this:

<cfset MYSTRING = &quot;UPDATE EmployeesBackup SET empfirst = '#fname#', emplast = '#lname#', startdt = '#startdate#', enddt = '#enddate#' WHERE empid = #empno#&quot;>

I haven't tested this against a database but when this is output all of the single quotes are in place around all of the string variables.

If this doesn't work against the database surround it with the PreserveSingleQuote() function, which does what it says on the tin!

<cfquery name=&quot;test&quot; datasource=&quot;tci&quot;>
#PreserveSingleQuotes(MYSTRING)#
</cfquery>

Hope this helps!

Tony