asked on

Saving more than one value from multiple drop down menu

I have a form that posts values from a multiple selection drop down menu. I am using SQL DB.
I am trying to figure out what is the best way to save those selections.

Example.

the options on the drop down menu are:

Text | Value
Approved | 1
Closed | 2
Pending | 3
Expired | 4

etc.

User can make more than one selection, and the values would end up being something like: 1,3,4

I can save those comma separated values into a nvarchar field for example. If I do this how can I then retrieve them to show those values in the edit page, so that the drop down menu shows all the selections that were made ? If I pass 1,3,4 will the drop down menu shows all those selections highlighted and understand the commas are separating each value ? Do I need to retrieve the values with a specific query ?

I haven't done this so far so I apologize if it sounds like a silly question.

Big Monty

a quick and dirty way would be to use the InStr() function, since your values are all unique (meaning, you don't have a 1 and a 14, where 1 appears in both values). Something like:

<%
dbValue = "1,3,4"
%>

<select multiple>
<option value="1" <%if InStr( dbValue, 1 ) > 0 then Response.Write "checked"%>>Approved</option>
<option value="2" <%if InStr( dbValue, 2 ) > 0 then Response.Write "checked"%>>Closed</option>
<option value="3" <%if InStr( dbValue, 3 ) > 0 then Response.Write "checked"%>>Pending</option>
<option value="4" <%if InStr( dbValue, 4 ) > 0 then Response.Write "checked"%>>Expired</option>
</select>

Aleks

ASKER

Well .. I checked the DB in my example and this is the value: 1, 5

The values are not hard coded, they come from a table in the database, so there could be unlimited number of options in the menu.

Then my menu code looks like this: (Does not show any selections highlighted)

  <select id = "tags" name="tags" size="5" multiple="MULTIPLE" class="form-control-yellow m-b">
                                            <%
While (NOT rs_tags.EOF)
%>
                                            <option value="<%=(rs_tags.Fields.Item("id").Value)%>" <%If (Not isNull((rs_note.Fields.Item("tags").Value))) Then If (CStr(rs_tags.Fields.Item("id").Value) = CStr((rs_note.Fields.Item("tags").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(rs_tags.Fields.Item("tag_text").Value)%></option>
                                            <%
  rs_tags.MoveNext()
Wend
If (rs_tags.CursorType > 0) Then
  rs_tags.MoveFirst
Else
  rs_tags.Requery
End If
%>
                                          </select>

Open in new window

Here is a screenshot of my menu, on the left I show the values from the DB, on the right the menu which shows with no selections made.
Capture.PNG

Big Monty

ok then, to be safe then don't use InStr() :) try this code:

select id = "tags" name="tags" size="5" multiple="MULTIPLE" class="form-control-yellow m-b">
                                            <%
While (NOT rs_tags.EOF)
%>
 <option value="<%=(rs_tags.Fields.Item("id").Value)%>" <%If (Not isNull((rs_note.Fields.Item("tags").Value))) Then If (checkValue( rs_tags.Fields.Item("id").Value, rs_note.Fields.Item("tags").Value ) ) Then Response.Write("selected=""selected""") %> ><%=(rs_tags.Fields.Item("tag_text").Value)%></option>
<%
  rs_tags.MoveNext()
Wend
If (rs_tags.CursorType > 0) Then
  rs_tags.MoveFirst
Else
  rs_tags.Requery
End If
%>
</select>

<%
function checkValue( val, valToCheck )
     var found : found = false

     if InStr( valToCheck, "," ) <= 0 then    '-- single value, see if it matches
          if val = valToCheck then found = true
     else
          dim arr, arrValue
          arr = Split( valToCheck, "," )
          for each arrValue in arr
              if CStr( arrValue ) = CStr( val ) then found = true
          next
     end if

     checkValue = found
end function
%>

Open in new window

Aleks

ASKER

I got an error message:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'var'
line 179

this is line 179:

var found : found = false

that is the second line in the function

Big Monty

Change var to dim

Aleks

ASKER

Unfortunately it didn't work. Only the first item shows highlighted, so in this case 1 and 5 are in the db, but only 1 shows highlighted and 5 doesn't.

<select id = "tags" name="tags" size="5" multiple="MULTIPLE" class="form-control-yellow m-b">
                                            <%
While (NOT rs_tags.EOF)
%>
 <option value="<%=(rs_tags.Fields.Item("id").Value)%>" <%If (Not isNull((rs_note.Fields.Item("tags").Value))) Then If (checkValue( rs_tags.Fields.Item("id").Value, rs_note.Fields.Item("tags").Value ) ) Then Response.Write("selected=""selected""") %> ><%=(rs_tags.Fields.Item("tag_text").Value)%></option>
<%
  rs_tags.MoveNext()
Wend
If (rs_tags.CursorType > 0) Then
  rs_tags.MoveFirst
Else
  rs_tags.Requery
End If
%></select>

Open in new window

This is the function:

function checkValue( val, valToCheck )
     dim found : found = false

     if InStr( valToCheck, "," ) <= 0 then    '-- single value, see if it matches
          if val = valToCheck then found = true
     else
          dim arr, arrValue
          arr = Split( valToCheck, "," )
          for each arrValue in arr
              if CStr( arrValue ) = CStr( val ) then found = true
          next
     end if

     checkValue = found
end function

Open in new window

This example I selected 2,3,4,5 only 2 shows highlighted
example.PNG

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

Ha! Worked. Thanks master!

Aleks

ASKER

Aleks

ASKER

After some more testing I realized this code works when there is more than ONE selection made, but if I only make one selection then the selection is not displayed. I checked the database value and it is there, in this case the value is '5' ... but the selection is not displayed. If I check 5, 2 then both are displayed just fine, only selections of ONE element won't display. Help is appreciated.

Big Monty

try changing

if val = valToCheck then found = true

to

if CStr( val ) = CStr( valToCheck ) then found = true

Aleks

ASKER

Cool ! thanks :)

Big Monty

that work?

Aleks

ASKER

It did. I just still need help displaying the names of the tags in my query instead of the ID. that is in my other open question. So far no luck.