multiple drop-down boxes populated from DB on one ASP page

[madman070578]

I have the following code to populate a dropdown box. I'm in need of anoter dropdown populated from the database on the same page.

<%
Dim DataConn
Dim CmdPopulateStates
Dim SQL

Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulateStates = Server.CreateObject("ADODB.Recordset")
%>

Take away the comment on Sytem DSN version below if you want to use a system DSN instead and add a comment to the DSN-LESS connection version below it

<%
DataConn.Open "DBQ=" & Server.Mappath("_database/zipcodes.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
SQL = "SELECT DISTINCT STATE_NAME FROM STATES"
CmdPopulateStates.Open SQL, DataConn
%>
<form method="POST" action="somepage.asp">
<Select Name="STATE_NAME" size="1">
<%While Not CmdPopulateStates.EOF%>
<option value="<%= CmdPopulateStates("STATE_NAME") %>"><%= CmdPopulateStates("STATE_NAME") %></option>
<%
CmdPopulateStates.MoveNext
Wend
CmdPopulateStates.Close
Set CmdPopulateStates = Nothing
DataConn.Close
Set DataConn = Nothing
%>
</Select>
<input type="submit" value="Submit">
</form>

Is it as simple as creating a second recordset, sql, and repeating the "CmdPopulateStates.Open SQL, DataConn" command calling the relevant sql name?

[ProWeb]

Yes, but this time change CmdPopulateStates to CmdPopulateSomethingElse.

[Chaze]

select another from the table if it is in thesame table, or select it using a alias for you to get the result and then create a another combo box and populate the another field you select from the connection

ex:

SELECT a.STATE_NAME, b.COUNTRY_NAME from STATE as a, COUNTRY as b


<form method="POST" action="somepage.asp">
<Select Name="STATE_NAME" size="1">
<%While Not CmdPopulateStates.EOF%>
<option value="<%= CmdPopulateStates("STATE_NAME") %>"><%= CmdPopulateStates("STATE_NAME") %></option>

<%
CmdPopulateStates.MoveNext
Wend

%>
</Select>

<Select Name="COUNTRY_NAME" size="1">
<%While Not CmdPopulateStates.EOF%>
<option value="<%= CmdPopulateStates("COUNTRY_NAME") %>"><%= CmdPopulateStates("COUNTRY_NAME") %></option>

<%
CmdPopulateStates.MoveNext
Wend
CmdPopulateStates.Close
Set CmdPopulateStates = Nothing
DataConn.Close
Set DataConn = Nothing
%>
</Select>

<input type="submit" value="Submit">
</form>

[Bullschmidt]

And for some related links:

Classic ASP Design Tips - Fill a Listbox Possibly With Multiple Columns
http://www.bullschmidt.com/devtip-filllistbox.asp

Classic ASP Design Tips - Dependent Listboxes
http://www.bullschmidt.com/devtip-de...tlistboxes.asp