Inside Technique : Building Lists from your Database : Using the BuildListBox Function

Before calling the BuildListBox function, you must open your database connection and instantiate a recordset. For all our database connections, we setup an ODBC Data Source in advance and then open it from our script: For example:

Set oCmd = Server.CreateObject("ADODB.Connection")
oCmd.open "DSN=MailingList;uid=UserName;password=PassWord" ' Open DSN named MailingList
set oRS = oConn.execute ("Select * from States")

Alternatively, you can create a DSN on the fly. This is useful if you do not have server permissions for creating DSN's:

Set oCmd = Server.CreateObject("ADODB.Connection")
oCmd.open "Driver={SQL Server};Server=MyServer;Database=MailingList;uid=UserName;password=PassWord" 
set oRS = oConn.execute ("Select * from States")

Once you have the recordset, you call the BuildListBox function:

Response.Write("<FORM>") 
Response.Write(BuildListBox("SELECT","States","False",0,"",oRS,"state","state_name")
Response.Write("</FORM>")

This generates a drop-down similar to the following:

<FORM>
<SELECT NAME="states">
<OPTION VALUE="AL">Alabama
<OPTION VALUE="AZ">Arizona
<OPTION VALUE="CO">Colorado
</SELECT>
</FORM>

As an alternative, you can display each item as a radio button (check boxes for multi-select):

Response.Write("<FORM>") 
Response.Write(BuildListBox("INPUT","States","False",0,"",oRS,"state","state_name")
Response.Write("</FORM>")

Generating the following HTML:

<FORM>
<INPUT TYPE=radio NAME="states" VALUE="AL">Alabama<BR>
<INPUT TYPE=radio NAME="states" VALUE="AZ">Arizona<BR>
<INPUT TYPE=radio NAME="states" VALUE="CO">Colorado<BR>
</FORM>

Regardless of how the items are displayed on the client, the server always receives the same data. This allows you to freely change from list boxes to radio buttons without having to modify your server processing.

Functions like BuildListBox are designed to be used as part of a general purpose form library. By building a standard library of functions for managing the output and creation of your forms, you can quickly update and modify all your pages by editing one file. In addition, any scripting problems can be scoped to a single source. On the SiteExperts site, we have a number of utility files that we include on most of our ASP pages. We include these files at the beginning of each page similar to the following:

<!-- Functions for managing the page header -->
<!-- #include virtual="/scripts/ahead.asp" -->

<!-- Functions for handling the body of the page -->
<!-- #include virtual="/scripts/abody.asp" -->

In future articles, we will explore other functions and techniques we use at SiteExperts to manage and maintain our web-site.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.