Populate Multi select LISTBOX from Database

[kashif73]

Hi,
How can I populate my multi select LISTBOX from access database. The values are seperated by a comma in the column of table. I want the respective OPTIONS in listbox to be SELECTED, when read from database. Thanks.

[GazMathias]

Hi there,

Can you show us what you've tried so far? We aim to "point in the right direction" rather than write code for you.

Gaz

[kashif73]

Hi,

this is what i have so far but it will only work if there is one value in the db table, what if there are 2 or more values (seperated by comma in the table), how to fetch them & then dispay in the LISTBOX. Offcourse the values should get SELECTED as well in the listbox. Thanks for your help.

<select name="needlist" size="5" multiple id="needlist">
                         <%IF rs("needlist")= "Water" Then%>
                         <option value="Water" selected>Water</option>
                         <%Else%>
                         <option value="Water">Water</option>
                         <%End IF%>

                         <%IF rs("needlist")= "Food" Then%>
                         <option value="Food" selected>Food</option>
                         <%Else%>
                         <option value="Food">Food</option>
                         <%End IF%>

[GazMathias]

Hi,

The way I see it you can either check for the existence of the value in the field using the insr() function, or you can split() apart the field and iterate it through a for each loop.

1st method:

<option value = "Food" <%if instr(rs("needlist"),"Food")>0 then response.write  " selected"%> >

Second method is harder to describe as I would probably have a loop within a loop.

That is to say, rather than having statically typed values and conditionals in the page I would probably loop those initial select box values from an array (or db, if that's where they are stored), then iterate through the ones in the recordset to select them if necessary.

Probably didn't describe that the best way, so:

1st for each loop (data = the array of values for the listbox)
Put value into <option>
2nd for each loop (data = the rs field)
Check value, write "selected" if found.
Repeat 2nd loop
Repeat 1st loop

This way, every value you enter into the array will be checked, otherwise you have to copy and paste bits of code for each instance. Can get messy if many people can manage those values.

Make sense?

Gaz

[kashif73]

Works well. Thanks for this Gaz.
Cheers.