Please Click Here, and Help Keep This Site FREE to Use
Please click on our sponsors advert to the right if you download or use anything from this page.
Thank you for your help.
This article has been kindly written
for WebDesignHelper.co.uk by AndyRed, Insight2Design Ltd,
http://www.insight2design.com/
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.
tbldropdown_country
Unid
Varchar_country
Show
Int - Identity
Varchar(50)
Int(4)
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.
Unid
Varchar_country
Show
1
United Kingdom
1
2
United States Of America
1
3
France
0
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 =
Server.CreateObject("ADODB.Connection") connTest.Open
"dsn=mynewdsn;Uid=username;Pwd=password;" Set rsTest
= connTest.Execute(sTestSQL)
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
our table.
Do While Not
rsTest.EOF Response.Write
"<Option value='" & rsTest("varchar_country")
& "'>" &
_ 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.
Response.write
"</select>" rsTest.Close Set
rsTest = Nothing connTest.Close Set
connTest =
Nothing %> </form> </table> </BODY>
</HTML>
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
database connection
Conclusion
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
HTML page
advertisment
advertisement
Host With Us: HostingHelper.co.uk From Only $5.00p/m
AndyRed
