Download
Academy
Current location: Downcodes.com -> Academy -> Programming -> SQL tutorial -> Populating a droplist from a database
Recommend
HOT TOP10
Populating a droplist from a database
Date: 2008-1-4 Author: Hit: View:[Large font Middle font Small font]
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!

(From: aspkey)
Relative article:
،¤Instant Guest Book - without a database
Relative software:
،¤PHP, MySQL database easily create website v2.0