Form: dropdowns: writing from db

[jamiescott]

Hi all,
I'm writing an asp page for a car dealership and am trying to get multiple drop downs with the fields being dragged from an access db

the code I've used for first selection box is:
________________________________________________________
<%
Dim sqlStatement, connData, rS
sqlStatement = "SELECT * FROM Marque ORDER by MarqueID"

Set connData=Server.CreateObject("ADODB.Connection")
connData.ConnectionTimeout=40
connData.Open "DSN=cqvwdb"

Set rS=Server.CreateObject("ADODB.Recordset")
rS.Open sqlStatement,connData,1,2

Response.Write("&nbsp;<br>")
Response.Write("<form name='drops' method='get' action='#'>")
Response.Write("<table border=0>")
Response.Write("<tr><td>Select a make</td>")
Response.Write("<td><select name='master' size=1 onchange='changeList(this)'>")
Response.Write("<option value='make'>Select a Make")
Do while Not rS.EOF
Response.Write("<option value='")
Response.Write(rS("marqueName"))
Response.Write("'>")
Response.Write(rS("marqueName"))
Response.Write("</option>")
rS.MoveNext
Loop
connData.Close
Set connData=Nothing
%>
</select>
________________________________________________________

but I can't figure out a way to then create the options in a second dropdown also from the db accordingly

any ideas would be much appreciated

Jamie Scott

[brouse]

Just extend what you already have (Note: If the values are already present in the initial SQL statement, then you do not need to create the additional SQL statement and resultset):


Dim sqlStatement, connData, rS1, rs2
sqlStatement1 = "SELECT * FROM Marque ORDER by MarqueID"
sqlStatement2 = "SELECT * FROM Model ORDER by Model"

Set connData=Server.CreateObject("ADODB.Connection")
connData.ConnectionTimeout=40
connData.Open "DSN=cqvwdb"

Set rS1=Server.CreateObject("ADODB.Recordset")
rS1.Open sqlStatement,connData,1,2
Set rS2=Server.CreateObject("ADODB.Recordset")
rS2.Open sqlStatement,connData,1,2

Response.Write(" <br>")
Response.Write("<form name='drops' method='get' action='#'>")
Response.Write("<table border=0>")
Response.Write("<tr><td>Select a make</td>")
Response.Write("<td><select name='master' size=1 onchange='changeList(this)'>")
Response.Write("<option value='make'>Select a Make")
Do while Not rS1.EOF
Response.Write("<option value='")
Response.Write(rS("marqueName"))
Response.Write("'>")
Response.Write(rS("marqueName"))
Response.Write("</option>")
rS.MoveNext
Loop
Response.Write("</Select></td>")

Response.Write("<td><select name='Model' size=1 onchange='changeList(this)'>")
Response.Write("<option value='make'>Select a Model")
Do while Not rS2.EOF
Response.Write("<option value='")
Response.Write(rS("ModelName"))
Response.Write("'>")
Response.Write(rS("ModelName"))
Response.Write("</option>")
rS.MoveNext
Loop
Response.Write("</Select></td>")

connData.Close
Set connData=Nothing


Hope this helps...