Take a look at PreserveSingleQuotes(). That should fix things.
Adrian
http://www.adrianlynch.co.uk/
-----Original Message-----
From: Richard White
Sent: 21 January 2008 10:43
To: CF-Talk
Subject: writing insert query sql in coldfusion
Hi, i have a very strange error occuring when trying to build an insert
query in coldfusion then trying to run it in a query
i have an app that manipulates alot of sql code. it builds tables and
inserts values into them. i have got it building tables fine except when i
try to run an insert code it tells me that there is an sql syntax.
this is what i am doing.
<cfset sqlQuery = "insert into testTable(testID,data1,data2) values (" &
testIDValue & ",'" & data1Value & "','" & data2Value & "');"
note that the data1 and data2 columns are varchar and the testID is bigint,
so i have had to enclose the data1 and data2 values with quotes: '
then i try to run the code by the following:
insert into testTable(testID,data1,data2) values (123,'a','b');
which is fine as if i put that directly into sql it goes in with no
problems. but i have noticed that when it is outputting it in the query it
is actually trying to put 2 of the single quotes together on each side as
follows:
insert into testTable(testID,data1,data2) values (123,''a'',''b'');
which of course is throwing an error saying i have a problem in my sql
syntax
does anyone know why this is occuring? it would be ok if they put a double
quote on each side of the string values but it isnt it is putting two of the
single quote marks together
does anyone have any ideas on how i can get around this?
thanks very much for your help
<cfquery name="runInsertQuery" datasource="dbName" username="#dbUsername#"
password="#dbPassword#">
<cfoutput>#sqlQuery#</cfoutput>
</cfquery>
the problem is if i cfoutput the code it displays as follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296967
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
