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>
Get the best asp web hosting provider now and save 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
