Solved

Saving more than one value from multiple drop down menu

Posted on 2016-07-25
14
50 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.
0
Comment
Question by:amucinobluedot
  • 8
  • 6
14 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 41728545
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>
0
 

Author Comment

by:amucinobluedot
ID: 41728553
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
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41728594
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

0
 

Author Comment

by:amucinobluedot
ID: 41728596
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
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41728597
Change var to dim
0
 

Author Comment

by:amucinobluedot
ID: 41728600
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
0
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 41728652
try changing

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

to

if CStr( Trim( arrValue ) ) = CStr( val ) then found = true
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:amucinobluedot
ID: 41728658
Ha!  Worked. Thanks master!
0
 

Author Closing Comment

by:amucinobluedot
ID: 41728659
:)
0
 

Author Comment

by:amucinobluedot
ID: 41731216
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.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41731225
try changing

if val = valToCheck then found = true

to

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

Author Comment

by:amucinobluedot
ID: 41731237
Cool !  thanks  :)
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41731241
that work?
0
 

Author Comment

by:amucinobluedot
ID: 41731250
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now