Passing multiple selections from drop down

[Guest_imported]

Hi!

I am having problems with my query reading multiple selections from a drop down. I would really appreciate any suggestions or advice.

Thanks!

Kimberly

 

[tleish]

Could you expand on this some more? What are you trying to accomplish with the form and the query? - tleish

 

[Silvano]

I'll take a wild guess here: if you are offering multiple selections, did you specify how many choices from your list is visible in the first place, so user can scroll and make multiple selection?

 

[ded]

Taking a guess it sounds like you have multiple selections coming from a drop-down and then you are using them as parameters in a query. The selections will come in the form of a list ie #form.multiselect# will contain 3,6,7. You will then have to process this to use in you query or if your DB supports the IN keyword you could use

SELECT *
FROM x
WHERE y IN (#form.multiselect#)

I hope my ramblings are of some use.

 

[Guest_imported]

Hi Guys!

Sorry about the late reply and thanks for your input! Anyways, just to clarify my problem, I want to send multiple selections (from a dropdown) as parameters in a query.

My WHERE clause of my query is:

WHERE City IN (#locations#)

I tried out your suggestion ded and I am getting an error:

ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3

After fiddling around by hardcoding the parameter (#locations#) in the Where clause, I found that the SQL statement only worked if the parameters already had single quotes around them: EX - WHERE City IN ('NewYork', 'Paris') etc.

The problem is that my form passes the multi select in this form: (NewYork,Paris) and that is why I get the above error. So I added single quotes "'" around my values in the dropdown but the sql statement reads it as: WHERE City IN ("NewYork", "Paris&quot. This also does not work.

I hope you can understand my babblings. I would really appreciate any suggestions or advice.

Thanks!
Kimberly

 

[scorniglia]

Hi Kimberly,
When passing numbers to a query as a list you don't need single quotes but when passing string values you do. In order to get the result you want you need to use the following syntax:

WHERE City IN (#PreserveSingleQuotes(locations)#)

Be sure the data from the multiselect list has single quotes around it when you pass it in and then all should work as you wish.

Regards,
Roy F.

 

[tleish]

I think the ColdFusion function you are looking for is:

ListQualify
Returns a list with a qualifying character around each item in the list, such as double or single quotes.

If:

FORM.cities = "New York,Paris"

WHERE City IN (#ListQualify(FORM.cities, "'")#)

would be:

WHERE City IN ('New York','Paris')

ListQualify has some other great parameters, like you can specify which elements to Qualify. In this case, we only want to qualify characters, and not quote numbers.

If:

FORM.cities = "New York,Paris,3"

WHERE City IN (#ListQualify(FORM.cities, "'",",","CHAR")#)

would be:

WHERE City IN ('New York','Paris',3)

- tleish

 

[Guest_imported]

Thanks a lot guys (Roy, tleish)!

I tried out your suggestions (PreserveSingleQuotes, ListQualify) and they both worked.

I really appreciate it!

Kimberly

 

[strantheman]

tleish!

Where did you find that function?!

Wow, thanks a lot.