When you cftry the expression, the first single quote is added. When the expression fails, it stops processing the rest of the statement and thus the second single quote will not be outputted on failure.
CF stops processing at the point of failure and does not execute what happens afterwards on a given template unless error capturing exists. Teddy On 1/29/07, Adrian Lynch <[EMAIL PROTECTED]> wrote: > > View the source and see if the SQL is as you have printed below. It might > be > a special character in the XML that's causing trouble. > > If that doesn't help. Strip it down to the basics and build up the SQL > till > the error appears, that'll help in finding it. > > Adrian > > -----Original Message----- > From: Charles Miles [mailto:[EMAIL PROTECTED] > Sent: 29 January 2007 17:44 > To: CF-Talk > Subject: Odd error when inserting records > > > Hello all, > > I'm running into an error I haven't seen before. Running CFMX 7.2 with > SQL server 2000. > > Basically, I'm retrieving an XML file, parsing it, then inserting the > contents into the database. Here's my code: > > <CFLOOP LIST="#VARIABLES.CHILD_LIST#" INDEX="ROLEITEMS"> > INSERT INTO > TBLTERMS (BIOGUIDEID, DISTRICT, STATE, STARTDATE, ENDDATE, PARTY, TYPE, > UPDATED) > VALUES( > > '#VARIABLES.BIOGUIDEID#', > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].DISTRICT#'<CFCATCH></CFCATCH></CFT > RY>, > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].STATE#'<CFCATCH>NULL</CFCATCH></CF > TRY>, > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].STARTDATE#'<CFCATCH>NULL</CFCATCH> > </CFTRY>, > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].ENDDATE#'<CFCATCH>NULL</CFCATCH></ > CFTRY>, > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].PARTY#'<CFCATCH>NULL</CFCATCH></CF > TRY>, > > <CFTRY>'#ROOT.PEOPLE[THIS][ROLEITEMS].TYPE#'<CFCATCH>NULL</CFCATCH></CFT > RY>, > GETDATE() > ) > > </CFLOOP> > > For each field (except the BIOGUIDEID field) the field accepts nulls, > and because any of the other items may or may not actually exist in any > given record, I catch the errors if its not found and insert NULL > instead. > > Unfortunately, starting very recently, I get a SQl syntax error when it > processes. Here is an example of what is actually being inserted for > one record. Obviously the unclosed single quotes would cause it error. > What I can't figure out is where they are coming from: > > INSERT INTO TBLTERMS (BIOGUIDEID, DISTRICT, STATE, STARTDATE, ENDDATE, > PARTY, TYPE, UPDATED) > VALUES( > 'B000944', > ', > 'NULL, > 'NULL, > 'NULL, > 'NULL, > 'NULL, > GETDATE() > ) > > Has anyone encountered this? I just recently moved to 7.02, is there > some syntax change I'm unaware of? > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267989 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
