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:267985 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
