As Dominic said, putting the entire sql statement in as a variable in
ColdFusion isn't necessary. Many languages like php that sort of thing is
necessary but in ColdFusion, as Dominic said, it's just redundant.
However, just for future knowledge sake, the reason your receiving this
error is because when you feed in a variable with single quotes, for some
reason they become duplicated when you feed it in sql. I've actually never
seen this before mostly because it's not good practice in ColdFusion to do
this.
However, Just FYI though... the below query should work.
<cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')">
<cfquery name="addpersonaleventtome" dataSource="cf_WikiData">
#replace(sqlToRun,"''","'","ALL")#
</cfquery>
Paul Alkema
-----Original Message-----
From: Dominic Watson [mailto:watson.domi...@googlemail.com]
Sent: Tuesday, June 30, 2009 5:22 AM
To: cf-talk
Subject: Re: SQL Help
Basically, the <cfset sqlToRun = "..." /> is kind of redundant and
negates the benefit of the cfquery tag. Put all you SQL inside the
cfquery tag. cfqueryparam is only valid within cfquery tags.
Dominic
2009/6/28 Jason Slack <applesl...@gmail.com>:
>
> CF 8.01 OS X.
>
> I have:
>
> <cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')">
>
> <cfoutput> #sqlToRun# </cfoutput>
>
> <cfquery name="addpersonaleventtome" dataSource="cf_WikiData">
> #sqlToRun#
> </cfquery>
>
> the <cfoutput> is:
> INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1,
1, 'jason', '2009-06-27')
>
> Which runs fine in a SQL Editor, but running that in the above <cfquery> I
get:
>
> You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '1'', ''1'',
''jason'', ''2009-06-27'')' at line 1
>
> There are a number of extra single quotes that I dont see why.
>
> If I try converting to <crqueryparam> I have something messed up because I
get an error:
>
> I did:
> <cfset sqlToRun = "INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(<cfqueryparam value="#FORM.eventidentity#"
cfsqltype="CF_SQL_INTEGER">, <cfqueryparam value="#FORM.whois#"
cfsqltype="CF_SQL_INTEGER">, <cfqueryparam value="#FORM.juser#"
cfsqltype="CF_SQL_VARCHAR">, <cfqueryparam value="#FORM.eventdate#"
cfsqltype="CF_SQL_DATE">)">
>
> and I get an error: Invalid CFML construct found on line 22 at column 120.
>
> Does anyone see my issue?
>
> -Jason
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324062
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
