On December 20th, 2000 Peter McMahon submitted a code sample titled Populating a
Selectbox. The methods that he describes for selecting the correct item for the current
record works quite well for small recordsets.
If you have the need for a larger recordset or perhaps many select boxes that are read
from a database on a page it can run into a performance issue with the conditional
check that is done for each loop. The following method can be used in the case where
you have larger recordsets or multiple select boxes on a form and you need that little
performance boost. It does result in a duplicate item in your select box at the top of the
list, but I've found that this is usually acceptable.
<SELECT NAME="cboMyfield">
<%
Dim objConn, objRs, objRs2
Dim lItemId, sItemDesc
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=MyDSN;"
lItemId = Request("ThisId")
objRs = objConn.Execute("SELECT Description FROM LookupTable " & _
"WHERE SomeIdField = " & lItemId)
sItemDesc = objRs.Fields("Description")
objRs2.Close
%>
<OPTION VALUE="<% = lItemId %>"><% = sItemDesc %></OPTION>
<%
Set objRs2 = objConn.Execute("SELECT CategoryID, Description FROM " & _
"DataTable")
While Not objRs2.EOF
%>
<OPTION
VALUE="<%=objRs2("CategoryID")%>><%=objRS2("Description")%></OPTION>
<%
objRs2.MoveNext
Wend
objRs2.Close
Set objRs = Nothing
Set objRs2 = Nothing
%>
</SELECT>
<!--More select boxes and HTML code.......-->
...
...
<%
objConn.Close
Set objConn = Nothing
%>
Submitted by Joel Gray
