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.  
Who is Participating?
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'
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
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"
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 ;)
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) doesn't -have- stored just uses sp's from your dbms

err...i did miss a )


WHERE location IN ('#replace(form.categories, ",", "','", "all")#')
andrewaielloAuthor Commented:
hmmm, pretty slick!  I'll give that a try.  Thanks for your info on stored procedures btw.  Take care.
You should also be able to use #QuotedValueList(FORM.Categories)#
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...
btw dg...i'd appreciate some input here.. (it's not really a mysql q...stupid zone cross post thing goes where ever)
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
andrew ...
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
Sid - I posted in your other post ...
waiting for your reply ...
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.