Use the function PreserveSingleQuotes() around any variable that already has
the quotes in it just the way you want them. This is a very common problem
that I see on boards/forums frequently and I remember struggling with it for
a while myself before figuring out that CF was trying to "help" me by
escaping the quotes and that what I needed to do was turn it off.
-Chris Gilbert, Fodors.com
-----Original Message-----
From: kaigler [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 22, 2001 5:48 PM
To: CF-Server
Subject: dynamic sql - what am I missing?
I have a query that looks like this:
<CFQUERY DATASOURCE="#dbname#" NAME="AddTable1">
INSERT INTO #assntablename#
(#table1fields#)
VALUES (#table1sqlsetup#)
</CFQUERY>
when I run the code without the update I get something that looks right:
INSERT INTO mhha
(ID,wAddress1, wCity, wState, wZip, WorkPhone, WorkFax, Email, CompanyName)
VALUES (49522,'1401 Cedar, NorthEast','Grand
Rapids','MI','49503','616/004-0002','616/004-0997','[EMAIL PROTECTED]','Visiting
Nurse Services')
when I run this code I get the following error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression '''1401 Cedar NorthEast'''.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (220:5) to (220:52) in the template
file C:\INETPUB\WWWROOT\AFFINISCAPE\HTDOCS\ADMINUPLOADBATCH.CFM.
Date/Time: 08/22/01 16:37:43
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
Remote Address: 127.0.0.1
Line 220 is the start of the cfquery.
If I take the sql that is generated and wrap cfquery tags around it like
this:
<CFQUERY DATASOURCE="#dbname#" NAME="AddTable1">
INSERT INTO mhha
(ID,wAddress1, wCity, wState, wZip, WorkPhone, WorkFax, Email,
CompanyName)
VALUES (49522,'1401 Cedar, NorthEast','Grand
Rapids','MI','49503','616/004-0002',
'616/004-0997','[EMAIL PROTECTED]',
'Visiting Nurse Services')
</cfquery>
it works fine and adds the record.
Can you do what I am trying to do? Do you have to make the entire query
dynamic? What am I missing?
thanks,
kaigler
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------------------------------------------------------
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
