ASPkey - A Resource Site for Web Developers

Your basket has: Total items: 0
Subtotal: $0.00

Article
Populating a droplist from a database

To keep your pages in sync with the database and produce a user-friendly result:

* populate the droplist boxes with current information;
* make the droplist box a subroutine that can be reused;
* sort and filter the list before showing it as ouput.

Here's how to do it:

Build the subroutine that will generate the droplistbox.

<%
'************************************
'Subroutine for droplist box
'
'PURPOSE: Reusable code to be called for placement of droplist box
'        in a form or page
'
'PARAMETERS:
'strSQL = Defines SQL statement
'strFieldName = Defines field name in database table
'strDefault = Defines default value in droplist
'StrBoxName = Defines name of droplistbox
'strBoxTitle = Defines the title shown next to droplist box on screen
'strConn = Defines the database connection string
'*************************************

Sub Droplist(strSQL,strFieldName,strDefault,StrBoxName,strBoxTitle,strConn)

'Set Cursor
'-------------------------------------------------------------------------
Const adOpenStatic=3

' create the recordset, open it, and move to first record
'-------------------------------------------------------------------------

    Set rs = Server.CreateObject ("ADODB.Recordset")
    rs.Open strSQL, strConn,adOpenStatic

rs.movefirst

'Ouput result to droplist box
'-------------------------------------------------------------------------

strBoxTitle%>
<SELECT Name = <%=StrBoxName%> SIZE="1">
<OPTION SELECTED> <%=strDefault%> </OPTION>
<%do until rs.EOF%>
<OPTION> <%=rs(strFieldName)%> </OPTION>
<%rs.movenext
loop%>
</Select>

<%
'Close and clean up
'-------------------------------------------------------------------------
    rs.close
    set rs=nothing
End sub
%>

Next, create a form with a call to the subroutine named "Droplist".

<HTML>
<HEAD>
<TITLE>Droplist Box Examples</TITLE>
<META name="description" content="">
<META name="keywords" content="">
<META name="generator" content="VisualN++">

</HEAD>

<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">

<CENTER>
<H2>Calling A Droplist Box Subroutine</H2>
<H3>For a form with multiple droplists, consider using a reusable subroutine</H3>
<HR>

<FORM name="userfrm" action="droplist.asp" method = "post" >
<TABLE border=0 bgcolor="#ffffff" width=100%><TR>
<TD align="left" width=35% valign="top" >

<%

strSQL = "SELECT orders.[CustomerID] FROM orders ORDER BY orders.[orderid] "
strFieldName = "CustomerID"
strDefault = "default value"
StrBoxName = "name"
strBoxTitle = "<B>title: </B>"
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; pwd=; DBQ="& Server.MapPath("northwind.mdb")

Response.write ("Droplist box No. 1" & "<BR>")
Call Droplist(strSQL,strFieldName,strDefault,StrBoxName,strBoxTitle,strConn) %>

<BR>
<BR>
<HR>

<%

strSQL = "SELECT orders.[orderid] FROM orders ORDER BY orders.[orderid] "
strFieldName = "orderid"
strDefault = "default value"
StrBoxName = "name"
strBoxTitle = "<B>title: </B>"
strConn = "DRIVER={Microsoft Access Driver (*.mdb)}; pwd=; DBQ="& Server.MapPath("northwind.mdb")

Response.write ("Droplist box No.2" & "<BR>")
Call Droplist(strSQL,strFieldName,strDefault,StrBoxName,strBoxTitle,strConn)

%>
</TD></TR></Table></Form>

Place this file in a directory with the Northwind.mdb file and see the results!

Return To Top

Article