my guess is that the single quote is coming from the first character
(the single quote) within the <cftry> block.
what's being "caught" is the variable value in # signs...but the
literal preceding it is not.
try this out:
<cftry>
<cfoutput>This is a #variables.test#!!</cfoutput>
<cfcatch>oops!</cfcatch>
</cftry>
obviously, #variables.test# does not exist, so you might expect to see
"oops!". just "oops!". Actually, what you'll see is "this is a
oops!".
you might want to use a <cfqueryparam> with the null attribute set to
something like:
null="#yesNoFormat(NOT structKeyExists(ROOT.PEOPLE[THIS][ROLEITEMS],
'district'))#"
On 1/29/07, Charles Miles <[EMAIL PROTECTED]> wrote:
> 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:267987
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
