Topic: SQL issue - cannot seem to run query!


mburke    -- 10-10-2002 @ 3:07 PM
  Can anyone help me troubleshoot the following???

I have a session variable called SelectString:

<cfset session.SelectString = "B.DIST_LIST = '#Form.DistrictType#' OR B.DIST_LIST LIKE '#Form.DistrictType#,%' OR B.DIST_LIST LIKE '%,#Form.DistrictType#'>

**Note the percent symbol used as a wildcard.  The field DIST_LIST in table B is of type
text (sample values are: "1,2,3,5,9" or "1" or "6,15").  
**The value of Form.DistrictType is a number between 1..15.

The query below, when run, will search for all records with the occurence of
#Form.DistrictType# in the field B.DIST_LIST.


This is my query:

<cfquery name="qMotionInstrItems" datasource="CMTS">
  SELECT A.*, B.*
  FROM  A INNER JOIN B ON A.FILE_ID = B.FILE_ID
  WHERE #session.SelectString#
</cfquery>

I keep getting a 37000 (Syntax error or access violation) error!!!
But when I plug the value of session.SelectString DIRECTLY into the SQL code,
it gives me the right result.


I've tried everything...like the following, for instance:

<cfset session.SelectString = #ToString("MOTIONS.AFFECTED_DIST_LIST = #ToString(Form.AffectedDistrictType)# OR MOTIONS.AFFECTED_DIST_LIST LIKE #ToString("#Form.AffectedDistrictType#,%")# OR MOTIONS.AFFECTED_DIST_LIST LIKE #ToString("%,#Form.AffectedDistrictType#")#")#>

...because I was thinking to get rid of the repeating single quotes wrapped around the operators
after the LIKE word in the query...to no avail.

I even tried replacing the single quotes with Chr(39) --> ANSI character for a single quote --
but when it runs the query, same error.  It wraps an extra set of single quotes around the
operators after the LIKE word.

Can anyone give me any feedback???  I'd appreciate it!


Webmaster    -- 10-10-2002 @ 10:01 PM
  Have you tried the PreserveSingleQuotes() variable?

Try this and tell me if it works:

<cfquery name="qMotionInstrItems" datasource="CMTS">
  SELECT A.*, B.*
  FROM  A INNER JOIN B ON A.FILE_ID = B.FILE_ID
  WHERE #PreserveSingleQuotes(session.SelectString)#
</cfquery>

Let me know if that gets you what you need, if not I'll find another workaround for ya! Smile



Thanks,
Pablo Varando
EasyCFM.COM, LLC.

This message was edited by Webmaster on 10-11-02 @ 7:44 AM


mburke    -- 10-11-2002 @ 9:02 AM
  yes, that was the solution!
thanks for the speedy reply.  Smile


Webmaster    -- 10-11-2002 @ 9:57 AM
  You're welcome..... Wink

If you need further help, let me know...


Thanks,
Pablo Varando
EasyCFM.COM, LLC.


EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=12&Topic=28