Populating drop down list box options from table data in ASP

We can populate a drop down list box by taking data from an access database. This way we get the advantage of handling the data in database itself and managing the data through query. From this we will understand how data is collected from a database and added as elements of a drop down list box. For example we have used our employee table. We have tried here to collect the department's names from the table and created the list box.

Note that in our employee table there are many employees in any one department so same department name appears many time inside the table. To display the department name only once we have to use distinct query command in our select query .

Try to understand how html drop down list box works in our html section. Now we will try to format our drop down list box with the returned data from the table. There is a tutorial on how to get data from database and display them in browser. We will combine this and develop our list box. We are not discussing the connecting string, and data objects and on how to fetch data here.

Data is collected by record set object and looped to display all records. Within this loop we will try to format the list box. Out side the loop we will declare the list box and then within the loop we will add the formatted list box options.


Response.Write "<select name=dept><option value='''' selected>Departments</option>"
Do While Not objRS.EOF
	Response.Write "<option value=''" & objrs("dept") &"''>"& objRs("dept") &"</option>"
     objRS.MoveNext
 Loop
Response.Write "</select>"

The above code will format the data returned from the table for the drop down list box. This is how we can create a list box with the value form a table. Now let us see the full code of the file.


<%@ Language = VBscript %>
<% Option Explicit %>
<!-- #include virtual = "/adovbs.inc" -->
<% Response.Buffer = True %>
<html>
<head>
<title>Database Search</title>
</head><body>

<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/emp.mdb")
objconn.Open

Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT  distinct(dept) FROM emp_m "
objRS.Open strSQL, objconn
Response.Write "<select name=dept><option value='''' selected>Departments</option>"
Do While Not objRS.EOF 
	Response.Write "<option value=''" & objrs("dept") &"''>"& objRs("dept") &"</option>"
     objRS.MoveNext
 Loop
Response.Write "</select>"
objRs.Close
objconn.Close

     %>
</body>
</html>

Dynamically listing drop down list options with table data