NEED HELP I try to update a fiel

[TC2000]

NEED HELP

I try to update a field (e.g. INT'L ) contain single quote to MS SQL Server
It updated successful.
BUT return (INT''L) two single quote.

What wrong......

 

[GunJack]

The single quote is an SQL delimiter and CF will escape it automatically. If what you're saying is that you try to update a field with a value "INT'L" and it gets inserted as "INT''L", then I would suspect that CF is somehow escaping your single quote when it shouldn't. If you can post the code for your update query, I or someone in the forums can tell you how to correct it. Depending on your syntax, the CF function preservesinglequotes() may be what you need.

GJ

 

[TC2000]

GUNJACK,
THE FOLLOWING IS MY QUERY,

<CFSET SQL = &quot;UPDATE MasterOut SET MethodBy =&quot;&quot;#MethodBy_Value#&quot;&quot;, DeliveryDate=&quot;&quot;#odbcdate#&quot;&quot;, &quot;>
<CFSET SQL = SQL &amp; &quot;Consignee=&quot;&quot;#Consignee_Value#&quot;&quot;, Destination=&quot;&quot;#Destination_Value#&quot;&quot;, &quot;>
<CFSET SQL = SQL &amp; &quot;Remarks=&quot;&quot;#Remarks_Value#&quot;&quot;, ContractNum=&quot;&quot;#ContractNum_Value#&quot;&quot; WHERE MasterOutKey=&quot;&quot;#M#&quot;&quot; AND CompanyID=&quot;&quot;#SESSION.CompanyID#&quot;&quot;&quot;>

 

[GunJack]

It looks like you just need to use the preservesinglequotes() function as CF will esacpe single quotes in dynamic sql statements. If you're executing your query like this,

<cfquery name=&quot;updateq&quot; ....>
#sql#
</cfquery>

you would just change it to this,

<cfquery name=&quot;updateq&quot; ....>
#preservesinglequotes(sql)#
</cfquery>

The only problem is that if any of your variables contain single quotes, you will have to manually escape them with the replace function like this.

<CFSET SQL = &quot;UPDATE MasterOut SET MethodBy =&quot;&quot;#replace(MethodBy_Value,&quot;'&quot;,&quot;''&quot;,&quot;all&quot#&quot;&quot;, ....>

Is there any reason why you're trying to build a dynamic sql statement instead of just doing the query with dynamic variables? If you can get by without having to put the whole query in a variable, it will make this a lot easier.

Let me know if you still have problems.
GJ

 

[TC2000]

GunJack

PROBLEM get solved. THANKS.......