Update Recordset using a MultiSelect Listbox

A common scenario that developers may encounter is to associate many database records with a record from another table. The example that I'll use is to associate a number of facilities with a particular property. On a webpage, the user can insert a property into the database. The page will also have a multiselect listbox for the user to associate many facilities with that property. The facilities are then stored as a comma delimited string in the Properties table.

The main problem that developers have is updating the property record. On the update page, the associated facilities in the listbox should be pre-selected. It's not immediately obvious on how to do this. You can download the support files by clicking the link at the bottom of this article. The important code is for the listbox on the edit.asp page

<select name="Facilities" size="5" multiple id="Facilities">
While (NOT FacilitiesRS.EOF)
    Facilities=ListingsRS("Facilities") & ""
    'Put the comma separated facilities into an Array
    for i=0 to ubound(Arr)
        if trim(Arr(i))=FacilitiesRS("Facility") then
            Exit for
        end if
  <option <% if IsSelected then Response.Write "selected" %> value="<%=(FacilitiesRS.Fields.Item("Facility").Value)%>">
If (FacilitiesRS.CursorType > 0) Then
End If

Note how the original value is split into an array. The code then loops through the arrayt to compare it to the value in the listbox. If there is a match, we can set the option to be selected.

Also, note that this is a quick'n'dirty solution. Technically, one should use a separate table to store the relationships.

You might also like...


Julian Roberts I've been a freelance web developer since Feb '00. Prior to that, I'd spent most of my working life in the pub trade. I'd also worked as a security guard in a local factory. Luckily, I was able to ...


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.” - Dennis Ritchie