Forms - Populating a Drop Down Menu with Info. from a Database
Surviving the basics
A drop down menu is the most popular way to cram a lot of
links into a small space. A drop down menu (also called a "drop down") is
a list of web pages. The user selects one of the options and presses, the
choice that has been selected will then stay selected permanently. For
example, this drop down gives you three options:
The code for the basic drop down list is like this
Using Active Server Page's (ASP) to our advantage
Drop Downs are tremendously useful and they are commonly
used for forms throughout the WWW. We can use our new found programming
language to our advantage to develop a easier way to manage our drop down
lists, and that method involves building a database
Firstly what we
need to do is build our database to hold our information for the drop down
list. In this example we will build a drop down which will contain a list
of countries around the world.
Our database table would look like.
Int - Identity
Entering the information
Once the table has been built, we need to enter our information.
We enter into the second column each different country we need to enter and for each country
we would like to appear on the page we enter a 1 into the show column. For
every country we do not want to appear we enter a 0 in the show column.
Something like this below.
United States Of America
Developing our Active Server Page
Once the table has been built, start to build our ASP page. The below code is the
top of the HTML code.
<% sTestSQL =
"SELECT * FROM tbldropdown_country where show = 1 order by
varchar_country asc" Set connTest =
"dsn=mynewdsn;Uid=username;Pwd=password;" Set rsTest
The variable sTestSQL set in the second line of the ASP
code holds the SQL statement which will pull all the information out
of the table we have previously created. The third line creates the
server object for the database, whilst the fourth line specifies the
link to our database using DSN. The last line of the code
executes the SQL statement contained in sTestSQL on
Do While Not
"<Option value='" & rsTest("varchar_country")
& "'>" &
"</option"> rsTest.MoveNext Loop
We know loop through all the values displaying the shown
fields from our table. The ASP code not only displays the drop down
information but also the drop down value. You can check this on the
finished article by viewing the source and seeing if the two values
are in the Option tag.
"</select>" rsTest.Close Set
rsTest = Nothing connTest.Close Set
Nothing %> </form> </table> </BODY>
The last lines of code finish off the drop down box. The
Response.write line of the code closes the select tag, the
second and third close the record set and four and five closes the
The advantages of adding a database driven drop down list are as follows:
Easy to maintain
Saves replicating data, that is stored in a database, onto a