Any ASP developer who has been around the block a few times has written code that creates a list box containing the elements from a database query. A list box is comprised of a number of options. Each option has a displayed title, and a hidden value. It is this value of the selected list box option that is passed onto the ASP page that the form is submitted to.

The most common way to build a list box from a database query is to use code similar to:

Response.Write "<SELECT NAME=selListBox>" While Not objRS.EOF Response.Write "<OPTION VALUE=" & objRS("UniqueID") Response.Write ">" & objRS("DisplayTitle") & "</OPTION>" objRS.MoveNext Wend Response.Write "</SELECT>"

(As a side note, this is not the most efficient way to construct a list box from the results of a database query. The fastest way is to use GetString. For an article that presents code on how to build a list box from a database query using GetString, check out Charles Carroll's great GetString article.)

Why retype this code each time you need to create a list box? Why not use a nifty function? This function, provided by alert reader Jason Huber, demonstrates how to create a list box on the fly with the help of a reusable function. Here is the function!

<SCRIPT LANGUAGE=VBSCRIPT RUNAT=SERVER> Public Sub BuildComboBox(rs, dispname, val, name, selected) 'rs = the recordset 'val = fieldname to place in the val of the option 'dispname = fieldname to display in the user control 'name = name of the select (cboBox) 'selected is the item in the lst that should be selected rs.movefirst response.write("<SELECT NAME=" & name & ">") while not rs.eof if rs.fields(val) = selected then response.write("<OPTION VALUE=" & rs.fields(val) & " selected>") response.write(rs.fields(dispname) & "</OPTION>") else response.write("<OPTION VALUE=" & rs.fields(val) & ">") response.write(rs.fields(dispname) & "</OPTION>") end if rs.MoveNext wend response.write("</SELECT>") End Sub </SCRIPT>

That's it! Here is some example code on how to call this function. The table, Products, contains several columns, including the name of the product (ProductName) and a unique identifier for each product (ProductID). The following code will generate a list box named selProduct that will contain an option for each product in the Products table. The name of the product will be displayed, and the value of each option will be the unique indentifier for the product. The product with ProductID equal to 17 will be selected by default. where the

Dim objConn, objRS Set objConn = Server.CreateObject("ADODB.Connection") Set objRS = Server.CreateObject("ADODB.Recordset") objConn.Open "DSN=ProductInfo" Set objRS = objConn.Execute("SELECT * FROM Products") BuildComboBox objRS,"ProdutName","ProductID","selProduct",17

Happy Programming!