RE: [KCFusion] update syntax error

Mon, 24 May 2004 13:56:32 -0700

Title: Message
Here was my solution...thanks for the help:
<CFSCRIPT>
function killcurly(formval){
var outval = formval;
outval = replace(outval,chr(8220),'"', "all"); // replace left Curly double quote
outval = replace(outval,chr(8221),'"', "all"); // replace right double quote
outval = replace(outval,chr(8216),'&lsquo;', "all"); // replace left Curly single quote
outval = replace(outval,chr(8217),'&rsquo;', "all"); // replace right Curly singe quote
outval = replace(outval,chr(8230),'...', "all"); // replace MS ellipsis with three periods
outval = replace(outval,chr(8211),'-', "all"); // replace MS em dash with ASCII dash
outval = replace(outval,chr(10),'<p>', "all"); // optionally get rid of line break
outval = replace(outval,chr(13),'<p>', "all"); //optionally get rid of carraige return
 
return outval;
}
</CFSCRIPT>
 
<CFQUERY NAME="insertdata" DATASOURCE="laura" DBTYPE="ODBC">
UPDATE reviews
SET reviewer='#reviewer#',
    description='#killcurly(description)#'
WHERE ID=#ID#
</CFQUERY>
 
 
Christopher V. Holdman
Webmaster - City of Olathe
work: 913.971.6286
cell: 913.238.4681
-----Original Message-----
From: Ramsey Abu-Absi [mailto:[EMAIL PROTECTED]
Sent: Monday, May 24, 2004 3:16 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [KCFusion] update syntax error

I think the problem is the single quote used for the possessive in "Chalk's".  I think the way I've handled this before is to either use a double quote in the SQL statement, like this:

<CFQUERY NAME="insertdata" DATASOURCE="laura" DBTYPE="ODBC">
UPDATE reviews
SET reviewer='#reviewer#',
    description="#killcurly(form.description)#"
WHERE ID=#ID#
</CFQUERY>

or to use the HTMLEditFormat function to escape the single quotes like this:

<cfset ODBCdesc = #killcurly(form.description)#>

<CFQUERY NAME="insertdata" DATASOURCE="laura" DBTYPE="ODBC">
UPDATE reviews
SET reviewer='#reviewer#',
    description='#HTMLEditFormat(ODBCDesc)#'
WHERE ID=#ID#
</CFQUERY>

Thanks,
Ramsey

At 04:02 PM 5/24/2004, Benjamin Price wrote:
It looks like you are replacing the left and right Curly single quote with a single tick mark.  CF sees the single tick mark as the end to the value are inserting.  So 'My Favorite Things is Laura Chalk' is the value to insert, but it then has "debut recording, and it proves to be enjoyable." left from the full value, so it doesn't know what to do.  In SQL server you can just put two single ticks in place of one to escape it for the db and CF code.  I don't know if that works in Access.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Chris Holdman
Sent: Monday, May 24, 2004 2:52 PM
To: '[EMAIL PROTECTED]'
Subject: RE: [KCFusion] update syntax error

 

 
I apologize, I noticed that error the correct code I am using is...

 
<CFSCRIPT>
function killcurly(formval){
var outval = formval;
outval = replace(outval,chr(8220),chr(34)); // replace left Curly double quote
outval = replace(outval,chr(8221),chr(34)); // replace right double quote
outval = replace(outval,chr(8216),chr(39)); // replace left Curly single quote
outval = replace(outval,chr(8217),chr(39)); // replace right Curly singe quote
outval = replace(outval,chr(8230),'...'); // replace MS ellipsis with three periods
outval = replace(outval,chr(8211),'-'); // replace MS em dash with ASCII dash
outval = replace(outval,chr(10),' '); // optionally get rid of line break
outval = replace(outval,chr(13),' '); //optionally get rid of carraige return

 
return outval;
}
</CFSCRIPT>

 
<CFQUERY NAME="insertdata" DATASOURCE="laura" DBTYPE="ODBC">
UPDATE reviews
SET reviewer='#reviewer#',
    description='#killcurly(form.description)#'
WHERE ID=#ID#
</CFQUERY>

 
But I still get a suntax error...
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ ''My Favorite Things is Laura Chalk's debut recording'.

 
The error occurred in D:\Webserver\laurachalk.com\www\admin\review_edit_pro.cfm: line 21

19 : SET reviewer='#reviewer#',


20 :    
description='#killcurly(form.description)#'


21 : WHERE ID=#ID# 


22 : </CFQUERY>


23 :

SQL    UPDATE reviews SET reviewer='Roger Atkinson', description='My Favorite Things is Laura Chalk's debut recording, and it proves to be enjoyable.' WHERE ID=23
DATASOURCE   laura
VENDORERRORCODE   -3100
SQLSTATE   42000
Christopher V. Holdman
Webmaster - City of Olathe
work: 913.971.6286
cell: 913.238.4681
-----Original Message-----
From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 24, 2004 2:41 PM
To: [EMAIL PROTECTED]
Subject: Re: [KCFusion] update syntax error
Thats not an update statement. Update takes the form
UPDATE table
SET attribute = value
WHERE this= that
and make sure to have single quotes around your strings.

 
Adaryl "Did you reboot?" Wakefield
Aviator by passion
Programmer by sheer force of will
----- Original Message -----
From: Chris Holdman
To: '[EMAIL PROTECTED]'
Sent: Monday, May 24, 2004 2:32 PM
Subject: [KCFusion] update syntax error

I have a simple acces database that I am trying to update.  I was using cfupdate (I know everyone hates it) so I changed to using a cfquery and using SQL to update.  The field I am updating is generated from people copying and pasting from word so I was getting all kinds of boxes and ?s instead of the single quotes and double quotes.  I found a niucfty little script to replace the bad characters, but when I try to update the database I get a syntax error now.  Here is the update code:
<CFSCRIPT>
function killcurly(formval){
var outval = formval;
outval = replace(outval,chr(8220),chr(34)); // replace left Curly double quote
outval = replace(outval,chr(8221),chr(34)); // replace right double quote
outval = replace(outval,chr(8216),chr(39)); // replace left Curly single quote
outval = replace(outval,chr(8217),chr(39)); // replace right Curly singe quote
outval = replace(outval,chr(8230),'...'); // replace MS ellipsis with three periods
outval = replace(outval,chr(8211),'-'); // replace MS em dash with ASCII dash
outval = replace(outval,chr(10),' '); // optionally get rid of line break
outval = replace(outval,chr(13),' '); //optionally get rid of carraige return
return outval;
}
</CFSCRIPT>

<CFQUERY NAME="insertdata" DATASOURCE="laura" DBTYPE="ODBC">
UPDATE reviews
(ID,reviewer,description)
VALUES(#ID#,#reviewer#,#killcurly(form.description)#)
</CFQUERY>
Here is the error generated:
Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

 
The error occurred in D:\Webserver\laurachalk.com\www\admin\review_edit_pro.cfm: line 20

 
18 : UPDATE reviews 


19 : (ID, reviewer,description) 


20 : VALUES(#ID#,#reviewer#,#killcurly(form.description)#) 


21 : </CFQUERY>


22 :

UPDATE reviews (ID, reviewer,description) VALUES(23,Roger Atkinson, JAM Magazine, June/July 2004,My Favorite Things is Laura Chalk's debut recording, and it proves to be enjoyable. Backed by the stellar Danny Embrey (Danny also arranged all of the tunes except for the title cut), Paul Smith, Bob Bowman, and Todd Strait (with Rod Fleeman bringing his acoustic guitar to several tracks), Laura brings her clear voice to a program of bossa novas, ballads, and standards.) 


 


DATASOURCE              laura


VENDORERRORCODE      -3503 


SQLSTATE                  42000


 


 


What am I doing wrong?

 
Christopher V. Holdman
Webmaster - City of Olathe
work: 913.971.6286
cell: 913.238.4681

 
========================================================= Kansas City ColdFusion User Group's website & listserv is hosted through the generous support of Clickdoug.com To send email to the list, email [EMAIL PROTECTED] To (un)subscribe, email [EMAIL PROTECTED] with your request. For hosting solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. ======================================================

Reply via email to