[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Need help with SQL IN clause regarding a multiple selection box

Posted on 2007-04-02
13
Medium Priority
?
185 Views
Last Modified: 2013-12-24
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.  
0
Comment
Question by:andrewaiello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18839813
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
 
LVL 1

Author Comment

by:andrewaiello
ID: 18839833
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
 
LVL 1

Author Comment

by:andrewaiello
ID: 18839842
(reading up on coldfusion stored procedures btw ;)
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 36

Expert Comment

by:SidFishes
ID: 18839906
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
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 200 total points
ID: 18839912
err...i did miss a )

sb

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

Author Comment

by:andrewaiello
ID: 18839917
hmmm, pretty slick!  I'll give that a try.  Thanks for your info on stored procedures btw.  Take care.
0
 
LVL 3

Expert Comment

by:cyrk
ID: 18841096
You should also be able to use #QuotedValueList(FORM.Categories)#
0
 
LVL 25

Accepted Solution

by:
dgrafx earned 300 total points
ID: 18842584
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 18842953
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 18842964
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
 
LVL 1

Author Comment

by:andrewaiello
ID: 18843158
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
 
LVL 25

Expert Comment

by:dgrafx
ID: 18843362
andrew ...
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 18844371
Sid - I posted in your other post ...
waiting for your reply ...
0

Featured Post

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question