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.