Comments field does not accept apostrophe

[jamie61880]

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:

<cfquery Datasource="#application.DataSource#">

        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#")#'

        <cfelse>

        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

[Owainnorth]

CFQueryParams should sort you out.

[jamie61880]

Owain,

     How do you use CFQueryParam? Do you have an example you could show me? Thanks.

Andy

[-__cfSearching__-]

Look up cfqueryparam in the online CF documentation. It should have usage examples at the bottom of the page.

[jamie61880]

I still don't understand how to use this, but I did change the single quotes to double quotes like this below and the data inserts into the database now:

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

    I did notice if I try and type something into this field with a double quote, it errors out again.

[-__cfSearching__-]

Did you read the documentation and examples for cfqueryparam?  They are pretty straightforward..

[Owainnorth]

Whilst this may work:

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

It's one of the most hideous solutions to a problem I've ever seen. I'd liken it to, perhaps, using Access as a database engine

You need to read up on CFQueryParams and associative array notation as suggested.

[-__cfSearching__-]

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

[Steve Sommers]

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.

[Dan_Bracuk]

PreserveSingleQuotes would not work in this situation.

[Reed_Powell-ttnmOb]

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

[jamie61880]

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

[insuractive · Accepted solution]

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.

[Reed_Powell-ttnmOb]

Not sure about using isDefined() on the FORM structure - usually you should use structKeyExists to see if an element exists in a structure.

Andy, can you post the code that is not working so that we can look at it and give you some hints?

-reed

[insuractive]

Reed,

Both would probably work in this case, however, this isDefined() method was specifically designed to test for the existance of variables in ColdFusion's variable scopes (in this case, the FORM scope).

[jamie61880]

Insuractive,

    I was finally able to try the code you sent with the cf_sql_longvarchar and cfqueryparam. It works! Thank you very much. I haven't been able to test the Structure["key"] code. How do I do that?

Andy

[insuractive]

I guess technically you already have.  By the Structure["key"] code I was reffering to the practice of referring to items in a CF scope (in this case the FORM scope) as items in a structure collection.

e.g. Form.first_name is equivalent to Form["first_name"]

the benefit is because your variable name is being passed into the syntax as a string, you can use variables to dynamically change what variable you are evaluating at runtime.

e.g. instead of Evaluate("Form.Comments#id#") (which is not a good idea for the reasons listed above) you can use:

Form["Comments#id#"]

or

Form["Comments" & id]

Both are essentially equivalend, the only difference is the first syntax cannot be used if you are already surrounding your variable with # signs.

e.g. #Form["Comments#id#"]# will give you a nice fat error message.

This syntax can be a very powerful tool in your CF coding skills.  I encourage you to get familiar with it and whenever possible, use it instead of the Evaluate() method.

Bonus Points: Did you know that all of CF's variable scopes can be referenced the using the same manner.  Particularly the VARIABLES scope, which is the default variables scope used by CF.  this pretty much means that almost every variable that you set or get in CF is going to be in some scope or another - all referencable through the Structure["Key"]/Associative Array notation.

[Reed_Powell-ttnmOb]

Check this link http://www.oreillynet.com/pub/a/oreilly/web/news/coldfusion_0701.html which is from 2001, so I'm pretty sure it's in MX7.  Other similar posts are available by googleing "coldfusion form scope structure notation"

-reed

[Adam Cameron.]

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)

It was CFMX6.0.  Which was released in 2002: almost ten years ago (and more than half the time than CF has even existed!).

There is no excuse for anyone to be using evaluate() like previously suggested in this thread; and I would be astounded if any CFWACK, or any other book, in that time has actually advocated such.  If so, the writer should be shot.

The only mitigating factor here is that evaluate() isn't the incredible processing bottleneck that it used to be (and that has been since CFMX7, I think.  Maybe CF8), so it's really just a case of butt-ugly, unnecessarily-complicated code, these days.  But still: the function is simply not needed in almost all situations, and accordingly should not be used.

--

Adam

[insuractive]

Also: MAJOR security risk, especially when dealing with a request variable as I am suspecting Andy's "ID" variable is.  If is not properly sanitized, some creative manipulation of that variable could cause BIG problems.

Andy, did you have a chance to try out the Structure["key"] notation in your code?