where clause as variable string

[RichardG]

I'm building a where clause as a string based on submited form variables. As
long as the form variable is an INTEGER the query works fine. If it is a
charcter or character string I get the error: "Incorrect syntax near 'n' "
where "n" was the content of the form variable.

for example, when the form variable form.explain value is "n":
.....
<cfset w= " WHERE t_QUESTIONS.q_section = dbo.t_sections.sect_ID ">
<cfif form.explain neq"">
<cfset w = w & " and t_QUESTIONS.q_explain = '#form.explain#' " >
</cfif>
<cfquery name="q" datasource="mydsn">
SELECT t_QUESTIONS.q_ID, t_QUESTIONS.q_section, t_QUESTIONS.q_explain,
t_sections.sect_name, dbo.t_sections.sect_ID
FROM dbo.t_QUESTIONS, dbo.t_sections
#w#
</cfquery>
...
returns the error: Line 5: Incorrect syntax near 'n'.

Outputting the var #w# content to the screen shows:

WHERE t_QUESTIONS.q_section = dbo.t_sections.sect_ID and t_QUESTIONS.q_explain
= 'n'

which looks perfect, and the query displayed in the debugging screen is:

SELECT t_QUESTIONS.q_ID, t_QUESTIONS.q_section, t_QUESTIONS.q_explain,
t_sections.sect_name, dbo.t_sections.sect_ID FROM dbo.t_QUESTIONS,
dbo.t_sections WHERE t_QUESTIONS.q_section = dbo.t_sections.sect_ID and
t_QUESTIONS.q_explain = ''n''

This has to be something simple, I just can't figure out what it is. I really
need to get this to work, anybody have a clue about what's going on? (this is
MX7)

[jdeline]

There is a function called PreserveSingleQuotes( ) that should solve your problem. Check out [url]http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Functions184.htm#1110445[/url]

[RichardG]

jdeline,

That did the trick - thanks very much!!