EasyCFM.COM ColdFusion Forums / Coding Help! / SQL issue - cannot seem to run query!

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: SQL issue - cannot seem to run query!

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

mburke
10-10-2002 @ 3:07 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Oct 2002

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Oct 2002

yes, that was the solution!
thanks for the speedy reply.  Smile

Webmaster
10-11-2002 @ 9:57 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You're welcome..... Wink

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


Thanks,
Pablo Varando
EasyCFM.COM, LLC.


Website Designed and Developed by Pablo Varando.