Using 'IN' clause with mutli-select listbox

[TheresaK]

Hi All,

Common sense tells me I'm missing something, but I need help here!

I have a dynamically populated listbox that I'd like to use to query an Access
table. This is easy if you select 'all' or select only one item, but when I
select multiple items, the query string looks like this;

sel=Item1&Sel=Item2&Sel=Item3 etc...

I was expecting the values to be comma delimited (i.e. sel='1,2,3'), and
therefore searchable using the 'IN' clause on my results page. However, listed
the way they are, if a user selects multiple items, no results are returned. Am
I missing something?

Should I be looking for a way to take the values and parse them into a comma
delimited string, or is there something wrong with the way I've configured by
listbox? I've looked all over the web for the answer and I just can't find it!

Thanks!

Theresa

[Julian Roberts]

I've a 'multiselect listbox' search example here

[url]http://www.charon.co.uk/files.aspx?categoryid=15&filecategoryid=10[/url]

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[TheresaK]

Hi Julian,

Thanks for your reply, and the demo. I looked over your code and tried it on
my server and it works beautifully. However, I can't see any differences
between your results query and my own. What exactly did you do to get the query
to filter on multiple values from one listbox?
There was some code related to 'keep url' etc - is that what I should be
looking at?

Thanks, and sorry if this is simple - I'm a relative newbie.

Theresa

[CMBergin]

It may be even simpler than you think. :)
When passed in the URL, the values may look like
list=1&list=2&list=3&list=4, but when they're retrieved via
Request.QueryString("list"), then you get "1, 2, 3, 4" back from the Request
object. Same for Request.Form.


"TheresaK" <webforumsuser@macromedia.com> wrote in message
news:d0ighi$gap$1@forums.macromedia.com...

> Hi Julian,
>
> Thanks for your reply, and the demo. I looked over your code and tried it

on

> my server and it works beautifully. However, I can't see any differences
> between your results query and my own. What exactly did you do to get the

query

> to filter on multiple values from one listbox?
> There was some code related to 'keep url' etc - is that what I should be
> looking at?
>
> Thanks, and sorry if this is simple - I'm a relative newbie.
>
> Theresa
>
>

[TheresaK]

Even if they are being passed correctly, my query will still not return any
records when multiple values are selected. I played around a bit using Julian's
database and it was straightforward to get the correct results. One thing I
noticed with my database is that when I try to use the exact syntax - i.e. not
having single quotes in the sql as follows;

select * from dt_location where sys_loc_code in (MMColParam)

then I get a data type mismatch error. When I use the quotes i.e.

select * from dt_location where sys_loc_code in ('MMColParam')

then the query works, but returns 'no data' when multiple values are selected.

My value 'sys_loc_code' is a text field, whereas Julian's value field is an
autonumber. Can you still use the IN clause with text values? Is there some
trick to correctly building the query?? It's driving me mad!

Thanks everyone,

Theresa

[CMBergin]

Each value in the IN clause must have quotes. Your second method (with
quotes) did
WHERE sys_loc_code IN ('code1, code2, code3')

In other words, a single value constructed from all of the above. You'll
need to edit the recordset outside of the dialog box, in code view.

rsName.Source = "SELECT ... snip ... WHERE sys_loc_code IN ('" +
Replace(Replace(rsName__MMColParam,"'","''"),", ","', '") + "')"

Mind the space on the second replace. If you replace "," instead of ", ",
then none of your codes after the first will match.

"TheresaK" <webforumsuser@macromedia.com> wrote in message
news:d0kh0u$gd2$1@forums.macromedia.com...

> Even if they are being passed correctly, my query will still not return

any

> records when multiple values are selected. I played around a bit using

Julian's

> database and it was straightforward to get the correct results. One thing

I

> noticed with my database is that when I try to use the exact syntax -

i.e. not

> having single quotes in the sql as follows;
>
> select * from dt_location where sys_loc_code in (MMColParam)
>
> then I get a data type mismatch error. When I use the quotes i.e.
>
> select * from dt_location where sys_loc_code in ('MMColParam')
>
> then the query works, but returns 'no data' when multiple values are

selected.

>
> My value 'sys_loc_code' is a text field, whereas Julian's value field is

an

> autonumber. Can you still use the IN clause with text values? Is there

some

> trick to correctly building the query?? It's driving me mad!
>
> Thanks everyone,
>
> Theresa
>

[TheresaK]

Thanks so much for answering my question. It all works using your code.

It's pretty straightforward when you're using numbers with the 'IN' clause,
but not when using text or dates. There's another discussion I found on the 4
Guys from Rolla website - FAQ # 153 which goes through this too.

Thanks again to Jules and CMBergin for their help.

[TheresaK]

Thanks!