> I have a list of cities in a form with multiple enabled.
> Selected list is submitted (as form.by_city) to a template
> with the following query:
>
> <cfquery name="lis_by_city" datasource="xyz">
> SELECT ID, sale_price,Address, City
> FROM properties
> WHERE City IN ('#PreserveSingleQuotes(form.by_city)#')
> </cfquery>
>
> If only one city is selected in the form, the processing
> template works fine, displaying all properties for a selected
> city.
>
> But if two or three are selected, then nothing is displayed,
> i.e. query does not retrieve any records.
>
> If I remove single quotes around ##, I get "Invalid Number of
> Parameters, Expected 1"
>
> I have similar queries that work OK with IDs in the same way:
>
> WHERE ID IN (#PreserveSingleQuotes(form.by_type)#)
>
> //by_type is a list genetrated by a multiple <select> where values
> are integers. In the City example above, <option> values are,
> naturally, strings.
The PreserveSingleQuotes function won't automatically add quotes for you, it
just keeps the ones that you might already have. If I understand your
problem correctly, what you really need to do is actually wrap single quotes
around each city value returned by the form. For example, if the user picked
the cities New York, Baltimore, and Orlando, you'd want your query to look
like this:
... WHERE City IN ('New York','Baltimore','Orlando')
However, if you simply output the variable Form.by_city, it would probably
look like this:
New York,Baltimore,Orlando
The function you want to use instead is the ListQualify function, which as
used below will wrap each item with single quotes:
... WHERE City IN (#ListQualify(Form.by_city, "'")#)
An even better answer, but one which would require a bit more work at this
point, would be to store cities in their own table, then reference them by
ID when needed. Normalizing your data is very important.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
