EasyCFM.COM - The ColdFusion Community! About Us!

New Member
Posts: 3
Joined: Nov 2004

I'm a bit confused and i'm not sure if I have found a Coldfusion Bug.

I am using Coldfusion Server 5.0 locally on a laptop with XP Professional and MSSQL desktop engine.

I write a query with the where string in a variable:
where_str = "fac_fac_number = 'SID-999999-S002'"

and the following query:

<CFQUERY NAME="ResultSet" DATASOURCE="#db_name#" DBTYPE="ODBC">
     Select fac_fac_number
       From facilities
      Where #where_str#
</CFQUERY>

When I execute the query the server seems to double the single quotes that appear in the variable where_str which causes the following error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'SID'.

SQL = "Select fac_fac_number From facilities Where fac_fac_number = ''SID-999999-S002''"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (5:1) to (5:69).

I really need to create the query using a variable.  Any thoughts, comments or work arounds would be appreciated.

Thank You

falconseye

11-05-2004 @ 10:00 AM

Senior Member
Posts: 1033
Joined: Mar 2004

could you try this instead?

fac_fac_number = 'SID-999999-S002'

<CFQUERY NAME="ResultSet" DATASOURCE="#db_name#" DBTYPE="ODBC">
     Select fac_fac_number
       From facilities
      Where #fac_fac_number#
</CFQUERY>

lpcunningham

11-05-2004 @ 10:11 AM

New Member
Posts: 3
Joined: Nov 2004

If I place fac_fac_number = 'SID-999999-S002' directly
into the query in place of the where_str variable, the query will work fine.
I can not leave the double quotes around the variable value off because this will cause its own error because of the spaces and equal sign in the variable value.

11-05-2004 @ 11:04 AM

Administrator
Posts: 4262
Joined: Oct 2002

WHERE #preserveSingleQuotes(where_str)#

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

lpcunningham

11-05-2004 @ 11:27 AM