Re: [KCFusion] update syntax error

Adaryl Wakefield Mon, 24 May 2004 12:58:52 -0700

Title: Message

The only thing I can see right off the bat is that there is a comma in there that may be confusing Access. I can't test that cause I think SQL server is smart enough to ignore plural possesive commas.

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:52 PM

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

Re: [KCFusion] update syntax error

Reply via email to