Richard Meredith-Hardy wrote:
>
> <CFQUERYPARAM only works after the WHERE, this is not the problem I
> have....
That is incorrect, you can use it anywhere.
> Second off, in a dynamic query like below, you need the
> preservesinglequotes() so the single quotes 'surrounding' the text value
> are not escaped automatically, however this will also preserve single
> quotes in the body of the text value which will cause an error. The fix
> is to escape any single quotes in the body of the text value before you
> give it to preservesinglequotes(). Once I realized what I wanted, a very
> simple UDF did the trick (easier to read and I will use it a lot):
>
> <CFSCRIPT>
> function EscapeSingleQuotes(x)
> {
> return replace(x,"'","''","all");
> }
> </CFSCRIPT>
>
> <CFLOOP LIST="#form.rowlist#" INDEX="idx">
> <CFSET tempVar = "fieldstr = '#EscapeSingleQuotes(form["fieldstr_" &
> idx])#'">
>
> <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> UPDATE testtable
> SET #preservesinglequotes(tempVar)#
> WHERE rowref = #idx#
> </CFQUERY>
> </CFLOOP>
Escaping single quotes often is not enough, many database have
escape characters like "\". You really should use cfqueryparam:
<CFLOOP LIST="#form.rowlist#" INDEX="idx">
<CFQUERY NAME="uddata" DATASOURCE="#myDS#">
UPDATE testtable
SET fieldstr = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form["fieldstr_" & idx]#">
WHERE rowref = <cfqueryparam cfsqltype="cf_sql_integer"
value="#idx#">
</CFQUERY>
</CFLOOP>
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137333
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
