Library code snippets
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.
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 ...
Related articles
Related discussion
-
Read eMails from Outlook express using ASP
by kumaravelu (1 replies)
-
Help to Call ASP function from onclick event in HTML to pass an array
by vka (0 replies)
-
Binary Studio | software development outsourcing Ukraine
by shane124 (4 replies)
-
Variable In Vb.Net
by chia (0 replies)
-
ideas in building a captive portal
by sjranjan (2 replies)
Related podcasts
-
Scott Guthrie
Scott catches up with Scott Guthrie in an interview covering Ajax, Asp 2.0, extender controls, CSS adapters and more.
This thread is for discussions of Update Recordset using a MultiSelect Listbox.