Man a bit to quick on the gun me thinks. Firstly yes, I believe that the
native driver misses the single quotes. I have yet to test this however.
Matt, Most of my queries are set out the sameway, but in this case the
JobDesc is rarely blank if at all. The issue being that the extra
character damn that quote mark!!! Its evil I tell ya!
J.
> Be interested in best practice for testing for variables, I know there
> is a better way, but most of my updates look like:
>
> --------------------------------------------------
> <cfset Disp1 = 0>
>
> <cfif isdefined("JobDesc")>
> <cfif JobDesc NEQ "">
> <cfset Dips1 = 1>
> </cfif>
> </cfif>
>
>
> UPDATE tbl_JobOrder
> SET JobTitle = '#JobTitle#'
> <cfif Disp1 EQ 1>, JobOrderDesc = '#JobDesc#'</cfif>
> --------------------------------------------------
>
> And as far as the PreserveSingleQuotes, I only had to use it when I did
> things like:
>
> --------------------------------------------------
> <cfset SQLWhere = " AND JobOrderDesc = '" & MyPassedString & "'">
>
> UPDATE tbl_JobOrder
> SET JobTitle = '#JobTitle#'
> #PreserveSingleQuotes(SQLWhere)#
> --------------------------------------------------
>
> Chad
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy
> Sent: Thursday, 17 February 2005 10:20 AM
> To: CFAussie Mailing List
> Subject: [cfaussie] Re: Single Quotes
>
>
> We got it...
>
> You will never believe this. In our Update statement. Kym your right,
> cold fusion should be able to handle this. Where I was having issues is
> that the statement wasn't. What I mean by this is the below following.
>
> This is what I had. pretty simple I thought.
> UPDATE tbl_JobOrder
> SET JobTitle = '#JobTitle#',
> JobOrderDesc = '#JobDesc#'
>
> The issue here is that if JobTitle was missing or never changed it did
> not fill the variable thus throwing wierd errors. So I removed the desc
> all together and put it in its own statement. And it worked perfectly.
>
> UPDATE tbl_JobOrder
> SET JobTitle = '#JobTitle#',
>
> Now I know thats another query to run, but at least I know this way its
> working correctly.
>
> Thanks everyone who has helped me on this journey of CF'ing. Its been
> truely painful.
>
> Jeremy (not at MXDU either but I'm coping....theripst tonight...whoohoo.
> )
>
>
>
> > Hi Jeremy,
> >
> > > "I don't want to 'miss' you. OR I would like's to have Beer's"
> > >
> > > What about a string like the above. See the issues is I want the
> > > single quotes to remain where they are but in my SQL statement it
> > > reads the ' mark as an end tag to the SQL statement. This is
> > > incorrect so therefor it throws thy error.
> > >
> > > Does that make sense?
> >
> > yes it does, it is a common problem as a single quote often appears in
> > strings as you have shown. CF is designed to automatically escape such
>
> > quotes in a string entered as data in a CF query. IE you don't have to
>
> > do a thing, CF will handle it. eg, this will work:
> >
> > <cfset thisString = "I don't want to 'miss' you.">
> >
> > UPDATE myTable
> > set txt = '#thisString#'
> > where blah
> >
> > that will be automagically translated by CF into:
> >
> > UPDATE myTable
> > set txt = 'I don''t want to ''miss'' you.'
> > where blah
> >
> > so it will work fine.
> >
> > The PreserveSingleQuotes() function is for when you do want the single
> > quotes to stay as such in the string and not get escaped. This might
> be
> > when you use a CF variable in an IN clause or similar, eg:
> >
> > <cfset ListOfNames = "'fred','joe','bert'">
> >
> > SELECT something
> > WHERE name IN (#PreserveSingleQuotes(ListOfNames)#)
> >
> > in such a case you want the single quotes to stay as single quotes
> > because the end result that you want is:
> >
> > SELECT something
> > WHERE name IN ('fred','joe','bert')
> >
> > Note, the PreserveSingleQuotes() function only works on simple
> > variables so if you are getting the string from an array or structure
> > then you need to make it "simple" first by using a cfset, eg:
> >
> > <cfset thisString = ArrayofQuotes[2]>
> >
> >
> > HTH
> >
> > Kym (one of those rare folk not at MXDU <g>)
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
> unsubscribe send a blank email to
> [EMAIL PROTECTED]
> Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
