Need help with SQL IN clause regarding a multiple selection box

Hey All,

    In the middle of an SQL query i have the following line:  

    WHERE location IN ('#form.categories#')    where the form.categories is the list returned from a multiple selection list box.  This query is not behaving properly because that last term gets evaluated as, for instance, the following:           Where location in ('Long Island, New York')   Where what i really need is the following:    Where location in ('Long Island', 'New York')         So you see, the quotes are not in the right place for proper functionality.  Can anyone tell me what I am doing wrong or how to fix it?  thank you.  
LVL 1
andrewaielloAsked:
Who is Participating?
 
dgrafxCommented:
It's ok guys - I'm here now ...

cyrk - QuotedValueList is for a query column output only!!!

I believe what you're looking for is #ListQualify(FORM.Categories,"'")#
This accomplishes the same as Sid's replace statement, but I feel is slicker.
If the list(FORM.Categories) is "New York,Claire City,Melbourne"
then #ListQualify(FORM.Categories,"'")# would be 'New York','Claire City','Melbourne'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to put the quotes in your form.categories.
it would eventually be better to use a stored procedure for that, so it makes such complexity transparent to the (coldfusion) code
0
 
andrewaielloAuthor Commented:
Thanks for replying so fast.  

     The listbox is being populated from a query so i don't know how to put the quotes in without generating the error.  The code for the box is as follows where catText would be like New York:  

              <cfselect name="categories"
                  query="getCat"
                  value="catText"
                  display="catText"
                  required="No"
                  multiple="Yes"
            
                  size=4></cfselect>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
andrewaielloAuthor Commented:
(reading up on coldfusion stored procedures btw ;)
0
 
SidFishesCommented:
it's due to the way cfselect passes the data


since cf is parsed before sql, a quick way might be to simply

 WHERE location IN ('#replace(form.categories, ",", "','", "all"#')

which basically transforms this


('Long Island, New York')
by replacing  the , with ','

so you get

('Long Island', 'New York') (provided i got all my "'''''s in the right place)


btw...cf doesn't -have- stored procedures...it just uses sp's from your dbms



0
 
SidFishesCommented:
err...i did miss a )

sb

WHERE location IN ('#replace(form.categories, ",", "','", "all")#')
0
 
andrewaielloAuthor Commented:
hmmm, pretty slick!  I'll give that a try.  Thanks for your info on stored procedures btw.  Take care.
0
 
cyrkCommented:
You should also be able to use #QuotedValueList(FORM.Categories)#
0
 
SidFishesCommented:
oh dg...yer all about the slick ;)

where ya been...working or something??

of course this illustrates what's good and also what's bad about cf there's many ways to do a thing...but it can be confusing to know which one to choose...
0
 
SidFishesCommented:
btw dg...i'd appreciate some input here..

http://www.experts-exchange.com/Database/MySQL/Q_22479194.html (it's not really a mysql q...stupid zone cross post thing goes where ever)
0
 
andrewaielloAuthor Commented:
Wow you guys, thanks alot; it works now.  I used DG's method (sorry sid! ;) )   Btw, where do you guys go to look for these types of functions that don't seem to be in the standard docs.  Thanks
0
 
dgrafxCommented:
andrew ...
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
0
 
dgrafxCommented:
Sid - I posted in your other post ...
waiting for your reply ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.