Populating a multiple select box from a database

[ozzii]

Hi

I have a HTML form with a multiple select box. The multiple select box is populated from a database. A user can select multiple options form this select box and the infomrtaion is then stored in a database.

The user then has the option to edit their saved record. How do i populate this multiple select box from a database with the users selected/saved options highlighted?

I can manage to get it to work if the user simply selected one option but not for multiple options. how do i iterate the record set with the saved options whilst at the same time comparing with a record set that populates the multiple select box? Sample code would be help full.

[shweta123]

Hi,

set rs=con.executes(sql)
<OPTION selected></OPTION>
<SELECT multiple>
<%while not rs.EOF%>
<%' Here you can put the condition to decide wheather to display it selected or not%>
<%'If yes%>
<OPTION selected><%=rs.Fields(0)%></OPTION>
<<%'else%>
<OPTION><%=rs.Fields(0)%></OPTION>

<%rs.MoveNext
Wend%>
</SELECT>

[ozzii]

Hi,

set rs=con.executes(sql)
<OPTION selected></OPTION>
<SELECT multiple>
<%while not rs.EOF%>
<%' Here you can put the condition to decide wheather to display it selected or not%>
<%'If yes%>
<OPTION selected><%=rs.Fields(0)%></OPTION>
<<%'else%>
<OPTION><%=rs.Fields(0)%></OPTION>

<%rs.MoveNext
Wend%>
</SELECT>

Hi,

I dont think the above code will work becuase its not comparing with a second record set with the saved options.

To clarify my point - i have one record set which is used to populate the multiple select box. A second record set has the saved options. The first recordset would need to compare each option value with each saved option value to decide if it should be selected or not.

[jhardman]

To clarify my point - i have one record set which is used to populate the multiple select box. A second record set has the saved options. The first recordset would need to compare each option value with each saved option value to decide if it should be selected or not.

In what format is the data in the two rs's? If I was starting this project I think I would first open the rs with the saved selected options and use that to populate an array. Then I would open the rs with all of the options. As I'm constructing the select box, I would check each value against the array to decide if it is selected or not. If the two lists should always be in the same order, you won't even have to scroll through the array more than once.

[shweta123]

Hi,

Does both the recordsets contain same no of records?
I am assuming that 2 recordsets are in the same order.
You can write it this way

set rs=con.executes(sql) 'rs contains data to fill the listbox
set rs1=con.executes(sql) 'rs1 contains data with saved options

<OPTION selected></OPTION>
<SELECT multiple>
<%while not rs.EOF%>
<%while not rs1.EOF%>
<% If rs1.fields("Options")="Yes"%>
<OPTION selected><%=rs.Fields(0)%></OPTION>
<%else%>
<OPTION><%=rs.Fields(0)%></OPTION>

<%rs.MoveNext
%rs1.Movenext
Wend%>
</SELECT>

[ozzii]

Hi,

Does both the recordsets contain same no of records?
I am assuming that 2 recordsets are in the same order.
You can write it this way

set rs=con.executes(sql) 'rs contains data to fill the listbox
set rs1=con.executes(sql) 'rs1 contains data with saved options

<OPTION selected></OPTION>
<SELECT multiple>
<%while not rs.EOF%>
<%while not rs1.EOF%>
<% If rs1.fields("Options")="Yes"%>
<OPTION selected><%=rs.Fields(0)%></OPTION>
<%else%>
<OPTION><%=rs.Fields(0)%></OPTION>

<%rs.MoveNext
%rs1.Movenext
Wend%>
</SELECT>

Hi, Thanks for the suggestions. but I've managed to get it to work using an array.
as suggested by hardman!

[polymorphic]

Hi,

I've run into the same situation described above. It seems that using an array as well as another recordset would be ideal for me. do you have a sample of the code you used to make this work?

Thanks,
cj

[shweta123]

Hi,

In case you are getting error in the the previous code?

Please refer the code below

<%
Dim rs,rs1,con
con.connectionstring ="........."

'Open connection
con.open

'Sql for Filling Listbox

sql = "Select ................"
set rs = con.execute(sql)

'Sql for extracting user selected options
sql = "Select ................"
set rs1 = con.execute(sql)

while not rs.Eof%>
<SELECT id=select1 multiple name=select1>
<%
'Fill the Listbox
'Put the condition to Keep it Selected ot not

If rs1.Fields(2) = 'yes' %>
<OPTION selected><%rs.Fields(0)</OPTION>
<%else%>
<OPTION ><%rs.Fields(0)</OPTION>
end if

rs.movenext
rs1.movenext
wend
%>