|
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.
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
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 -----
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
|
