Codefixer - How to Populate a HTML DropDown Box in ASP

Our example below uses a table 'tblFriends' in an Access database 'Friends.mdb'. The table has 3 columns, firstly an 'ID' field which is an autonumber, secondly a field called 'FirstName' which is a textfield and lastly another textfield called 'SurName'.

Our SQL statement uses the wildcard % and looks for those records where the FirstName's first letter begins with a T and populates the drop down with the records returned. If the form has not been submitted then the dropdown list will be displayed and if it is submitted then the selected option will be displayed.

If there are no records that begin with the T (i.e. If oRecordset.Eof) then the drop down box will show "No Suitable Options" and if submitted the page will display "No suitable option was selected."

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Dropdown</title>
</head>

<body>
<%
'Check if our form and our dropdown box has been submitted
If Request.Form="" Then
%>
<form action="dropdown.asp" method="post" name="form1" id="form1">
<select name="select">
<%
Dim oConnection, oRecordset, sConnString, sSQL
Dim sFirstName, sSurName, sFullName, sDropDownOption

Set oConnection = Server.CreateObject("ADODB.Connection")
Set oRecordset = Server.CreateObject("ADODB.Recordset")
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Friends.mdb")
oConnection.Open sConnString
sSQL="SELECT * FROM tblFriends WHERE FirstName LIKE 'T%'"
oRecordset.Open sSQL, oConnection

If oRecordset.Eof Then
%>
<option value="">No Suitable Options</option>
<%
Else
Do while NOT oRecordset.EOF
sFirstName=oRecordset("FirstName")
sSurName=oRecordset("SurName")
sFullName=sFirstName & " " & sSurName
%>
<option value="<%= sFullName %>"><%= sFullName %></option>
<%
oRecordset.MoveNext
Loop
End If

oRecordset.Close
oConnection.Close
Set oRecordset=nothing
Set oConnection=nothing
%>
</select>
<input name="Submit" type="Submit" value="Submit">
</form>
<%
'If our form has been submitted run the code below
Else
'Next assign the value of the dropdown box to our variable sDropDownOption sDropDownOption=Request.Form("select")
'If value of sDropDownOption isn't empty then display on browser
If sDropDownOption<>"" Then
Response.write sDropDownOption
Else
Response.write "No suitable option was selected."
End If
End If
%>
</body>
</html>

If you have any code snippets to share with full credit given then send an email to Codesnippets - You'll receive full credit and a link back to your site.

How to populate a HTML DropDown box in ASP