Inspiring

Answered

Comments field does not accept apostrophe

Forum|Forum|14 years ago
November 9, 2011
5 replies
4012 views

Does anyone know why my Comments field will not accept an apostrophe? It's just a memo field in Access. I have this same field in my other databases and it works fine with apostrophies. Here's what I have for the code on the first page:

<textarea name="Comments#ItemID#" cols="38" rows="2">#Trim(Comments)#</textarea>

Here's the code I have on the action page that inserts the Comments field into the database:

Update ECO_Items

Set <cfif isDefined("form.Comments#id#") and evaluate("form.Comments#id#") neq "">

Flag_Comments = #Evaluate("form.Flag_Comments#id#")#,

Comments='#Evaluate("form.Comments#id#")#'

Flag_Comments = '',

Comments = ''

</cfif>

Where ItemID=#id#

</cfquery>

This inserts text, periods, commas, etc., and blank info. into the Comments field, but when I put an apostrophe in some text such as "It's fixed", it will not insert that. What would be causing this? How do I fix it?

Thanks.

Andy

This topic has been closed for replies.

Correct answer insuractive

It should work in CF7. My guess is you have a syntax problem in using the associated array notation. Try this (added some cfquery goodness):

<cfquery Datasource="#application.DataSource#">
        Update ECO_Items
        Set
        <cfif isDefined("form.Comments#id#") and form["Comments#id#"] neq "">
             Flag_Comments = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form["Comments" & id]#">,
             Comments=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form["Comments" & id]#">
        <cfelse>
             Flag_Comments = '',
             Comments = ''
        </cfif>
        Where ItemID=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>

Assuming that it works, take a closer look at how I'm dynamically referring to the variables in the FORM scope and also how I'm using <cfqueryparam> to wrap my query parameters.

R

Reed_Powell-ttnmOb

Inspiring

Cannot beliefve that changing those single quotes to double quotes would fix it - they would just change the error from the database engine. And as Dan mentions, preservesinglequotes is for an entirely different problem. What people are trying to tell you when they say to use associative arrays is to change

#Evaluate("form.Comments#id#")#

to something like this

#form["Comments" & ID]#

Most of the not-so-recent books, as well as most of the CFWACK books, are laden with code examples that use the evaluate approach to handling dynamically created form field names, because that's how we used to have to do it in older versions of CF. But a few versions back (V7 I think) the contents of the FORM scope because addressable as associative arrays, so if you can address them simply as FORM[formfieldname]. Part of your problem is that you just have too many evaluates where you don't need them - remember that evaluate is going to try and look at the contents as if it were a meaningful CF expression.

Hope this helps,

-reed

J

jamie61880Author

Inspiring

Reed,

I tried changing this to the way you have it, but that still did not work. We have CF MX 7. Does this code not work until version 8?

Andy

I

insuractiveCorrect answer

Inspiring

It should work in CF7. My guess is you have a syntax problem in using the associated array notation. Try this (added some cfquery goodness):

<cfquery Datasource="#application.DataSource#">
        Update ECO_Items
        Set
        <cfif isDefined("form.Comments#id#") and form["Comments#id#"] neq "">
             Flag_Comments = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form["Comments" & id]#">,
             Comments=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form["Comments" & id]#">
        <cfelse>
             Flag_Comments = '',
             Comments = ''
        </cfif>
        Where ItemID=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>

Assuming that it works, take a closer look at how I'm dynamically referring to the variables in the FORM scope and also how I'm using <cfqueryparam> to wrap my query parameters.

D

Dan_Bracuk

Inspiring

PreserveSingleQuotes would not work in this situation.

S

Steve Sommers

Legend

My first choice would be cfQueryParam. My second choice would be preserveSingleQuotes. I have used evaluate before like this but it bit me in the bottom side based on the field contents.

-

-__cfSearching__-

Inspiring

... and you may want to look into using associative array notation instead of evaluate().