Professional Web Applications Themes

Using 'IN' clause with mutli-select listbox - Dreamweaver AppDev

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 ...

  1. #1

    Default Using 'IN' clause with mutli-select listbox

    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 p 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



    TheresaK Guest

  2. #2

    Default Re: Using 'IN' clause with mutli-select listbox

    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


    Julian Roberts Guest

  3. #3

    Default Re: Using 'IN' clause with mutli-select listbox

    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 Guest

  4. #4

    Default Re: Using 'IN' clause with mutli-select listbox

    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" <webforumsusermacromedia.com> wrote in message
    news:d0ighi$gap$1forums.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
    >
    >

    CMBergin Guest

  5. #5

    Default Re: Using 'IN' clause with mutli-select listbox

    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 Guest

  6. #6

    Default Re: Using 'IN' clause with mutli-select listbox

    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" <webforumsusermacromedia.com> wrote in message
    news:d0kh0u$gd2$1forums.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
    >

    CMBergin Guest

  7. #7

    Default Re: Using 'IN' clause with mutli-select listbox

    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 Guest

  8. #8

    Default Re: Using 'IN' clause with mutli-select listbox

    Thanks!
    TheresaK Guest

Similar Threads

  1. Updating table with multi-select listbox
    By MikeyJ in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 23rd, 07:20 AM
  2. Multiple select listbox values in query
    By DC Gringo in forum ASP.NET Building Controls
    Replies: 2
    Last Post: April 14th, 06:28 AM
  3. DataRow[] and Select(filter with a like clause) question
    By Ron Vecchi in forum ASP.NET General
    Replies: 2
    Last Post: July 30th, 01:04 PM
  4. Help a newbie! How do I SELECT using a string as a WHERE clause?
    By Pablo Contreras in forum ASP Database
    Replies: 1
    Last Post: July 21st, 05:41 PM
  5. Automatic select of first item in listbox
    By Ron Spiegel in forum Microsoft Access
    Replies: 0
    Last Post: July 21st, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139