We help IT Professionals succeed at work.

Saving more than one value from multiple drop down menu

Aleks
Aleks asked
on
106 Views
Last Modified: 2016-07-27
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.
Comment
Watch Question

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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>

Author

Commented:
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 MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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 MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
Change var to dim

Author

Commented:
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
Web Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ha!  Worked. Thanks master!

Author

Commented:
:)

Author

Commented:
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 MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
try changing

if val = valToCheck then found = true

to

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

Author

Commented:
Cool !  thanks  :)
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
that work?

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.