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") & ""
    IsSelected=false
    'Put the comma separated facilities into an Array
    Arr=Split(Facilities,",")
    for i=0 to ubound(Arr)
        if trim(Arr(i))=FacilitiesRS("Facility") then
            IsSelected=true
            Exit for
        end if
    next
    %>
  <option <% if IsSelected then Response.Write "selected" %> value="<%=(FacilitiesRS.Fields.Item("Facility").Value)%>">
  <%=(FacilitiesRS.Fields.Item("Facility").Value)%>
  </option>
    <%
    FacilitiesRS.MoveNext()
Wend
If (FacilitiesRS.CursorType > 0) Then
    FacilitiesRS.MoveFirst
Else
    FacilitiesRS.Requery
End If
%>
</select>

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

Comments

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

Contribute

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.

“Most software today is very much like an Egyptian pyramid with millions of bricks piled on top of each other, with no structural integrity, but just done by brute force and thousands of slaves” - Alan Kay