Code Snippets » ASP » How to filter data from an Access database by using a drop-down list
How to filter data from an Access database by using a drop-down list
This example uses a database to fill a context menu (<select>), and uses a query to filter the data in the Customers table.
Code:
<html>
<head>
<title>List of Customers - MdmSoft</title>
<style type="text/css">
body {
font-family: "verdana";
font-size:14px;
}
#customers {
border:1px solid #777;
border-collapse:collapse;
font-family: "verdana";
font-size:14px;
width:468px;
}
th {
background-color:#CCCCCC;
color:#0066FF;
text-align:left;
}
tr {
border:1px solid #777;
}
</style>
</head>
<body>
<div>
<%
' Create a Connection
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
' Open the connection
conn.Open "driver={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("App_Data/Northwind.mdb")
' Create a Recordset Customers
Dim rsCustomers
set rsCustomers = server.createobject("ADODB.Recordset")
' Create a Recordset Cities
Dim rsCities
set rsCities = server.createobject("ADODB.Recordset")
' Create a SQL query
Dim SqlCities
SqlCities = "SELECT * FROM spGetCities"
' Open the Recordset
rsCities.open SqlCities, conn, 3 , 3
%>
<div>
Select the city and click on the button Search<br />
<form method="post" action="customers.asp" >
<select name="city" size="5">
<%
Do While not rsCities.EOF
Response.Write("<option value='" & rsCities.Fields(0) & "'>" & rsCities.Fields(0) & "</option>")
rsCities.MoveNext
Loop
rsCities.close
%>
</select>
<input id="btnSearch" type="submit" value="Search">
</form>
</div>
<%
' Get the value of the City
Dim city
city = Request.form("city")
Dim where
If city = "" Then
where = " "
Else
where = " WHERE City='" & city & "'"
End If
' Create a SQL query
Dim SqlCustomers
SqlCustomers = "SELECT * FROM spSelectCustomers" & where
rsCustomers.open SqlCustomers, conn, 3 , 3
' Create a HTML table
Response.write( "<table id='customers' border='0'>" )
Response.write( "<tr>")
Response.write( "<th>Name</th>")
Response.write( "<th>Company</th>")
Response.write( "<th>City</th>")
Response.write( "</tr>")
' Loop the Recordset and print the information
Do While not rsCustomers.EOF
Response.write( "<tr>")
Response.write( "<td>" & rsCustomers.Fields(0) & "</td>")
Response.write( "<td>" & rsCustomers.Fields(1) & "</td>")
Response.write( "<td>" & rsCustomers.Fields(2) & "</td>")
Response.write( "</tr>")
rsCustomers.MoveNext
Loop
Response.write( "</table>" )
' Close all
rsCustomers.close
conn.close
%>
</div>
</body>
</html>
Sample result:
If our work has been of help, you can help us with a small donation...
Our programmers will thank you!
Related code snippets posted in
ASP:
The best
ASP
recommended books:
All information contained in this web site are the property of MdmSoft.
The information is provided "as is", MdmSoft will not be liable for any misuse of the code contained in these pages,
nor can it be for inaccuracies, grammatical errors or other factors that may have caused damage or lost earnings.
MdmSoft is not responsible for the content of comments posted by users.
The examples in this area have the educational and demonstration purposes only,
and may be copied only for your reference, but cannot be used for commercial purposes,
or for any other purpose, without the express written consent of MdmSoft.
MdmSoft also reserves the right to change, without notice, to your liking this web site,
the pages and its sections, and may suspend temporarily or definitely the various services included on this site.
While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.