Multi Select populated from database

[ianj]

Hi All,

I am trying to populate a multi select dropdown from a string which comes from a database.

It works putting in the values in the format below into the database, but my problem is that they need to be able to amend the values, so I need to reverse the process. So I need the values to be show as they selected it in the first place then allow them to update them.

The string is as follows 13,14,15,16,17,99

The code on the asp page is as follows,

<select multiple="multiple" name="Region" size="2" >
<option value="">Select Region</option>
<%
While (NOT rsRegions.EOF)
vIDRegion = rsRegions.Fields.Item("IDRegions").Value
vRegion = rsRegions.Fields.Item("Region").Value

%>
<option value="<%=(vIDRegion)%>"><%=(vRegion)%></option>
<% If vfrmerror = 1 and vRegion = "" Then Response.Write("<span class = ""bodyRedlargeText"">*</span> ")%>
<%
rsRegions.MoveNext()
Wend
%>

</select>


Can anyone advise the best way to do this please.

Many thanks in advance

[Old Pedant]

Well, the big problem you have is bad DB design.

You should *NOT* store a delimited list like that into a single DB field. Period.

You should instead have a many-to-one table dedicated *just* to storing these multiple values.

Are you willing to change the bad DB design? Or do you really want to continue down this path? Yes, you can do it this way.

Oh, what the hell.

Set RS = conn.Execute( "SELECT thatDelimitedListField FROM someTable WHERE id = " & something )
theList = RS(0)
RS.Close

SQL = "SELECT vIDRegion, vRegion, IIF(vIDRegion IN (" & theList & "),'selected','') AS sel " _
& " FROM yourRegionsTable ORDER BY vRegion"
Set RS = conn.Execute(SQL)
%>
<select multiple name="Region">
<%
Do Until RS.EOF
%>
<option value="<%=RS("vIDRegion")%>" <%=RS("sel")%> ><%=RS("vRegion")%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>


That's for Access DB. For SQL Server, change the SQL to:

SQL = "SELECT vIDRegion, vRegion, " _
& " (CASE WHEN vIDRegion IN (" & theList & ") THEN 'selected' ELSE '' END) AS sel " _
& " FROM yourRegionsTable ORDER BY vRegion"

[ianj]

Many thanks, I will change the database to use the many to one database design as it seems the better way to progress.

[Old Pedant]

If you change the DB design, come back and ask how to do this multi-select again. Show your DB schema when you do.

With the proper schema, you can get the values for the <SELECT> in a single query *AND* you can put the update values back into the DB with only two queries (one of which is dirt simple...you just delete all values from the "many" table for the specific PrimaryKey of the main table and then insert all the newly <select>ed values).