Also, you may also want to just put these values outside of teh sql
statement:
<cftry>
<cfset district = root.people[this][roleitems].district
<cfcatch>
<cfset district = "">
</cfcatch>
</cftry>
And then just use '#district#' in your insert statement. This will provide
the empty string as if you cfparam'ed it or provide the actual value from
your struct/array.
Teddy
On 1/29/07, Teddy Payne <[EMAIL PROTECTED]> wrote:
>
> 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:267990
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
