Solved

Saving more than one value from multiple drop down menu

Posted on 2016-07-25
14
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 33

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 33

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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

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 33

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 33

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
 

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 33

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 33

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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

717 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