Re: MS SQL - Syntax error for valid query

[Ian Skinner <iskinner@xxxxxxxxxxx>]

AWFrueh1808 wrote:
> OK, that's simple enough.  But then I'm really confused as to why the query in 
> my original post/example didn't work.  
>
>  The only single quotes in my #whereClause# variable were wrapped around each 
> varchar value in the WHERE clause (well, HAVING) as in HAVING formNum = '12345' 
> OR formNum = '54321'
>
>  I never used a single quote within the value itself.  And yet 
> preservesinglequotes fixed it.  So what was CF doing?  Why did the single 
> quotes still show up in the error msg - and in the right place?

As I said the default behavior is to escape the quotes in your variables 
as data.  So the default output of your SQL statement, before 
preserveSingleQuotes() would be like this.

HAVING formNum = ''12345'' OR formNum = ''54321''

As you can see that is not the desired results in your case.  In your 
case you have the proper number of single quotes in your string and you 
do not want any added.  You want to preserve them so to say.  Thus 
adding preserveSingleQuotes() around your whereClase variable, i.e. 
preserveSingleQuotes(whereClause) says do not add any quotes, keep them 
all single.