asked on

dynamic dropdown listboxs extracted from database

if there are two listboxes(A & B), values of them are both extracted from the same table in the database. The listboxes are interactive that when I select value of one listbox, the corresponding value of the other will change. How should the code be??

thanks a lot!!

Mark Franz

This is a FAQ here, did you try to do a Google search on "dynamic listbox asp"?

iiff123

ASKER

ya...but still don't know how to deal with my case...here's the coding...

script side:
<script language="VBScript" RunAt="Server">

function show_corid()
Dim idArray(50), n, i, desArray(50)
n = 0
i = 0
SQLstr_id = "select * from tableA"
Set adocon1 = Server.CreateObject("ADODB.Connection")
adocon1.Open "Provider=SQLOLEDB.1;Server=x.x.x.x;UID=xxx;PWD=xxx;" & _
"database=xxx;"
Set sa1 = adocon1.Execute(SQLstr_id)
if sa1.EOF then
ok_id = False
else
Response.Write"<select name=a_id>"
Response.Write"<option value= selected>"
While Not sa1.EOF
Response.Write ("<option value=" & sa1("a_id") & ">" & sa1("a_id") & "</option>")
strID = sa1("a_id")
idArray(n) = strID
strdes = sa1("a_name")
desArray(i) = strdes
sa1.MoveNext
n = n + 1
i = i + 1
WEnd
Response.Write"</select><p>"
ok_id = True
end if
}
end function

function show_cordesc()
Dim idArray(50), n, i, desArray(50)
n = 0
i = 0
SQLstr_des = "select * from tableA"
Set adocon1 = Server.CreateObject("ADODB.Connection")
adocon1.Open "Provider=SQLOLEDB.1;Server=x.x.x.x;UID=xxx;PWD=xxx;" & _
"database=xxx;"
Set sa2 = Server.CreateObject("ADODB.Recordset")
'sa2.Open "tableA", adocon1, 1, 3
Set sa2 = adocon1.Execute(SQLstr_des)
sa2.MoveFirst
if sa2.EOF then
ok_des = False
else
Response.Write"<select name=a_des>"
Response.Write"<option value= selected>"
While Not sa2.EOF
Response.Write ("<option value=" & sa2.Fields("a_name") & ">" & sa2.Fields("a_name") & "</option>")
strID = sa2("a_id")
idArray(n) = strID
strdes = sa2("a_name")
desArray(i) = strdes
sa2.MoveNext
n = n + 1
i = i + 1
WEnd
Response.Write"</select><p>"
ok_des = True
end if
end function
</script>
form side:
<form name="Form1" method="post">
<tr>
<td>ID</td>
<td><% show_corid %></td>
</tr>
<tr>
<td>Name</td>
<td><% show_cordesc %></td>
</tr>
</Form>

sorry for my stupid...anybody can help??

gladxml

Try this it is very self explanatory.. The concept is to pass the pass to itself using href and passing the value using the quesry to the same for the second query on the second dropdown box. Just try to do the necessary adjustment on the databasename, fieldname, tablename.. Also the connection is aauming that you are using access db.

====save this as dropdown.asp
<%response.buffer=true%>
<%

Dim oConn, dbPath
dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & dbPath & ";"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Table1"
rs.Open sql, oConn

%>
<html>
<head>
<title>Virtual Hosting | Members Area</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
</head>
<script language="Javascript">
function doSel(obj)
{
for (i = 0; i < obj.length; i++)
if (obj[i].selected == true)
eval(obj[i].value);
}

</script>
<body>
<select name="Ref_ID" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs.eof)%>
<option value="location.href='dropdown.asp?id=<%=rs("ID")%>'" <%if request.querystring("id")=rs("ID") then response.write "selected" end if%>><%=rs("ID")%></option>
<%
rs.movenext
loop
%>
</select>

<%if request.querystring("id") <> "" then

set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Table1 WHERE ID = '"& request.querystring("id") &"'"
rs1.Open sql, oConn%>
<select name="Ref_ID2" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs1.eof)%>
<option value="<%=rs1("ID")%>" <%if request.querystring("id")=rs1("ID") then response.write "selected" end if%>><%=rs1("ID")%> </option>
<%
rs1.movenext
loop%>
</select>
<%rs1.Close()
Set rs1 = Nothing

<%else%>
<SELECT size="1" name="CboDate">
<OPTION VALUE=""> </OPTION>
</SELECT>

<%end if%>

</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>

HTH...

Happy programming...

gladxml

iiff123,

The concept is to pass the page to itself using href and passing the value using request.querystring to the same page for the second query on the second dropdown box. Just try to do the necessary adjustment on the databasename, fieldname, tablename.. Also the connection is aauming that you are using access db.

You can try to check out this link.

https://www.experts-exchange.com/questions/20398728/Dynamic-Dates.html

Happy programming...

iiff123

ASKER

what's the "eval()" for in the program??
Thanks!!

iiff123

ASKER

what's the "eval()" for in the program??
Thanks!!

iiff123

ASKER

what's the "eval()" for in the program??
Thanks!!

ASKER CERTIFIED SOLUTION

gladxml

membership