2nd dropdown to auto populate? [Archive]

View Full Version : 2nd dropdown to auto populate?

jarv

05-14-2009, 02:57 PM

Hi,

I have 2 tables in my database,

Stores and Branches

I would like to allow a user to select a Store from a dropdown and then for another dropdown to appear for the user to select the Branch depending on the store selected using an INNER JOIN in my SQL on the StoreID

Can anyone help?

So far I have:

<%
Set rsStore = objConn.Execute("SELECT * FROM stores")

Dim StoreName : StoreName = request.QueryString("StoreName")

%>

Welcome <% = Session("rsUser")%>,

<form name="form3" method="post" action="">
<select name="menu2" onChange="MM_jumpMenu('parent',this,0)" class="textbox">
<option value="">Please choose a Store!</option>
<%Do While Not rsStore.EOF%>
<option value="add-report.asp?StoreName=<%=rsStore("StoreName")%>">
<%= rsStore("StoreName")%></option>
<%rsStore.MoveNext
Loop%>
</select>
</form>

Just like Example 1 here (http://www.mattkruse.com/javascript/dynamicoptionlist/)

jarv

05-14-2009, 04:38 PM

so now I have changed my code to the following:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Signature - Welcome</title>
<script type="text/javascript" src="js/jva.js"></script>
<link rel="stylesheet" type="text/css" href="css/style1.css" />


<%
logincheck()
opendb()

Dim StoreName

UserID = session("UserID")

Set rsStore = objConn.Execute("SELECT * FROM stores")
%>
<SCRIPT language=JavaScript>
function reload(form){
var val=form.StoreName.options[form.StoreName.options.selectedIndex].value;
self.location='emp.asp?StoreName=' + val ;
}
</script>
</head><body>

<%

StoreName=Request.QueryString("StoreName")

Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "SELECT * FROM stores"
objRS.Open strSQL, objconn
Response.Write "<form method=post name=f1 action=''><select name=StoreName onchange='reload(this.form)'><option value=''>Select Store</option>"
Do While Not objRS.EOF
Response.Write "<option value=" & objRs("StoreName") & ">" & objRs("StoreName") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "<br>----<br>"

''' Second drop down list starts here ''''

If len(StoreName) > 1 Then
dim rsStore1

strSQL1 = "SELECT * FROM stores WHERE StoreName ='" & StoreName & "'"
objRS1.Open strSQL1, objconn
StoreID = objRs1("StoreID")

strSQL = "SELECT * FROM branches INNER JOIN stores ON branches.StoreID=stores.StoreID WHERE branches.StoreID='" & StoreID &"'"
objRS.Open strSQL, objconn

Do While Not objRS.EOF
Response.Write objRs("BranchLocation") & " " & objRs("BranchLocation") & " " & objRs("BranchLocation") & "<br>"
objRS.MoveNext
Loop
Response.Write "</form>"
objRs.Close

objconn.Close
end if

%>
</body>
</html>

the error I get now is Object required: '' on line 54

Line54:

strSQL1 = "SELECT * FROM stores WHERE StoreName ='" & StoreName & "'"
objRS1.Open strSQL1, objconn

Spudhead

05-14-2009, 06:34 PM

Put:
Set objRS1 = Server.CreateObject("ADODB.Recordset")
directly above it.

Old Pedant

05-14-2009, 07:57 PM

Or, even better and faster and simpler:

strSQL1 = "SELECT * FROM stores WHERE StoreName ='" & StoreName & "'"
Set objRS1 = objconn.Execute( strSQL1 )