Published: Monday, December 06, 1999
Building List Boxes Using the Results from a Database Query
By Scott Mitchell
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:
<![CDATA[
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>
<![CDATA[
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
<![CDATA[
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!
By Scott Mitchell
Attachments:
Download the source code for BuildComboBox in text format
