I'm pretty sure you don't need to use all that concatention. Try: <cfset sqlQuery = "insert into testTable(testID,data1,data2) values (#testIDValue#,'#data1Value#','#data2Value#');">
Warren Koch ----- Original Message ----- From: "Richard White" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, January 21, 2008 4:42 AM 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:296969 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
