SQL PHP HTML ASP JavaScript articles and free scripts to download

Dynamically listing drop down list options with table data

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>

Be the first to post comment on this article :


Google+

Further readings
Changing second list values based on selection of first list
Connecting string and recordset object for data handling of Access db
Recordset object properties like EOF, BOF, MoveNext etc in ASP to browse through the records
Displaying all the records of an access table by looping through the records
Dynamically listing drop down list options with formatted access table data
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer