ASP - How to assign "selected" values to a combo box

I'm pulling data from a recordset, but I would like the combo box values on my ASP page to automatically "select" the proper value, as listed in the recordset.  This page is used for users to go back and "edit" their account.

For example: The recordset value for the field is "Blue" ... Therefore, in the combo box, the value that's selected will be "Blue".  The order of the combo box values must maintain consistency.

If there's a simple way of doing this without using multiple "If Then" statements, that would be cool.  Thank you!
tommy10101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arthuryeungCommented:
I think it is not possible to do it without if..then statement, but you can write a function like this to help you:

'============================================================================================================
' Function          : MakeOption(String,String,Boolean)
' Description     : Make an HTML FORM SELECT OPTION with specified label, value and selected state
' Input(s)          : label                    = the LABEL attribute of the OPTION
'                      value                    = the VALUE attribute of the OPTION
'                      selected_value     = if the value match with the select value the this option is SELECTED
' Return          : Boolean               = whether the input date is valid for SQL datetime
'============================================================================================================
sub MakeOption(label, value, selected_value)
     response.write "<option value=""" & value & """"
     if value = selected_value then response.Write " selected"
     response.write ">" & label & "</option>" & vbcrlf
end sub

In your ASP:

<SELECT NAME="color">
<% MakeOption "Blue","Blue",rs("color") %>
<% MakeOption "Yellow","Yellow",rs("color") %>
<% MakeOption "Green","Green",rs("color") %>
</SELECT></TD>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwvsmartCommented:
<%
Dim con, rs

'Database connection info and driver (if this driver does not work then comment it out and use 'one of the alternative drivers)
'con = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath(yourdatabase.mdb)


'This one is if you convert the database to Access 97    
'strCon = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=" & Server.MapPath(yourdatabase.mdb)
     
'If you wish to use DSN then comment out the driver above and uncomment the line below (DSN is 'slower than the above drivers)
'con = "DSN=yourDSNname" 'Place the DSN name after the DSN=


'Creating a connection with the Access 2000 Database
'I'm using ACCESS 2000
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("yourdatabase.mdb")


SQL = "SELECT * FROM yourTableName"
Set rs = con.Execute(SQL)
While Not rs.EOF
%>
<Select Name="DropDown">
<option value="<%=rs("fldCategory")%>"><%=rs("fldCategory")%>
     <%
     rs.MoveNext
     Wend
     rs.Close
     %>
</Select>
Try whether this suits you if i understand correctly....
0
arthuryeungCommented:
cwvsmart:
i am not sure whether you got the meaning of the question but your code does go wrong:

While Not rs.EOF
%>
<Select Name="DropDown">
<option value="<%=rs("fldCategory")%>"><%=rs("fldCategory")%>
    <%
    rs.MoveNext
    Wend
    rs.Close
    %>
</Select>

you shouldn't put <select xxx> inside the while loop, and it is better to add the close tag </option>, it should be like that:

<Select Name="DropDown">
<%
While Not rs.EOF
%>
<option value="<%=rs("fldCategory")%>"><%=rs("fldCategory")%></option>
    <%
    rs.MoveNext
    Wend
    rs.Close
    %>
</Select>

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

tommy10101Author Commented:
Thanks to both of you!  It's working now. Arthur, I accepted your answer as it was the first one that worked for me.  Very simple solution too...

0
CRagsdellCommented:
Create a recordset of all colors (rsColors), and another recordset of the user info (rsUsers) that includes the color field, then use similar code to the following to determine if the user color equals the particular color being built into the select option list, and then set the selected tag if they are equal.

Response.Write "<select size=""1"" name=""Color"">"

Response.Write "<option value=""0"">-Select A Color-</option>"

rsColors.MoveFirst

Do While Not rsColors.EOF

Response.Write "<option "

If rsUsers.Fields("Color") = rsColors.Fields("Color") Then
Response.Write "selected "
End If

Response.Write "value="
Response.Write "'" & rsColors.Fields("Color") & "'"">" & rsColors.Fields("Color") & "</option><br>"

rsColors.MoveNext

Loop

Response.Write "</select>"

You will note that I have a "Select A Color" as the first option. You cal leave it out if you want.

Of course, be sure to close the recordsets, etc...

CR
0
CRagsdellCommented:
Ooops, too slow on the Submit!

Sorry...

CR
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.