2 tables and 2 dropdown lists

collie · October 20th, 2003, 07:20 AM

I have 2 tables:
SUB TABLE that contains the fields: SUB_ID, CAT_ID, SUB_NAME
CATS TABLE that contains CAT_ID, CAT_NAME

I have a ASP page that contains 2 listboxes. One of the listbox is populated the minute the page is loaded. It receives the CAT_NAME from the CATS table.
The second listbox is supposed to be populated in accordance to what was selected in the CATS listbox.
I am not sure how to do that. I have included the code that i wrote which is wrong:

<%@ Language=VBScript %>

<%'Frame1.asp -This contains the dropdown box that people choose the entry's.
%>
<%
set rsCat = Server.CreateObject("ADODB.Recordset")
rsCat.ActiveConnection = MM_connDUclassified_STRING
rsCat.Source = "SELECT * FROM CATS ORDER BY CAT_DATED DESC"
rsCat.CursorType = 0
rsCat.CursorLocation = 2
rsCat.LockType = 3
rsCat.Open()
rsCat_numRows = 0
%>

<% If Instr(request.servervariables("http_user_agent"), "MSIE") Then %>

<html>

<head>
<title>Demo Drop Down</title>
</head>

<body>
<script language="vbscript">
sub submitthis
'document.write "change"
'form1.submit
document.write "<select>"
end sub
</script>

<p>Select from the dropdown menu</p><br>
<form ACTION="" METHOD="POST" NAME="form1">
<p><select ONCHANGE="submitthis" SIZE="1" NAME="CAT_ID">
<%
While (NOT rsCat.EOF)
%>
                <option value="<%=(rsCat.Fields.Item("CAT_ID").Value)%>" <%if (CStr(rsCat.Fields.Item("CAT_ID").Value) = CStr(rsCat.Fields.Item("CAT_NAME").Value)) then Response.Write("SELECTED") : Response.Write("")%> ><%=(rsCat.Fields.Item("CAT_NAME").Value)%></option>
                <%
  rsCat.MoveNext()
Wend
If (rsCat.CursorType > 0) Then
  rsCat.MoveFirst
Else
  rsCat.Requery
End If
%>
              </select>
</p>
</form>
</body>
</html>


<% Else %>

<html>
<head><title>dude></title></head>
<body>

<p>Select from the dropdown menu</p><br>
<form METHOD="POST" NAME="form1">
<p><select SIZE="1" NAME="cat_id">
<%
While (NOT rsCat.EOF)
%>
                <option value="<%=(rsCat.Fields.Item("CAT_ID").Value)%>" <%if (CStr(rsCat.Fields.Item("CAT_ID").Value) = CStr(rsCat.Fields.Item("CAT_NAME").Value)) then Response.Write("SELECTED") : Response.Write("")%> ><%=(rsCat.Fields.Item("CAT_NAME").Value)%></option>
                <%
  rsCat.MoveNext()
Wend
If (rsCat.CursorType > 0) Then
  rsCat.MoveFirst
Else
  rsCat.Requery
End If
End If
%>
              </select>

<%
rsCAT.close
'conn.close
set rsCAT = nothing
'set conn = nothing

%>

<%
'Good coding practices to declare all your local variables
'Does an If statement to see if the request("cat_id") dropdown menu has a value or not
If request("cat_id") <> "" Then
%>
<%
set rsSub = Server.CreateObject("ADODB.Recordset")
rsSub.ActiveConnection = MM_connDUclassified_STRING
rsSub.Source = "SELECT SUB_NAME FROM SUBS WHERE ((SUB_name)='" & request("cat_id") & "');"
rsSub.CursorType = 0
rsSub.CursorLocation = 2
rsSub.LockType = 3
rsSub.Open()
rsSub_numRows = 0

%>
<html>
<head>
<title>Load when Empty</title>
</head>
<body>
<form id=form2 name=form2>
<p>The results of what was selected from the first dropdown.</p><br>
<p><select name="d2" size="1">
<% Do While Not rsSub.EOF %> <option value="<% = rsSub("SUB_NAME") %>"><% = rsSub("SUB_NAME") %></option>
<% rsSub.movenext %><% loop %> </select><br>
</p>
</form>
</body>
</html>
<%
rsSubclose
'coM.close
set rsSub = nothing
'set coM = nothing
%>

This part gets loaded when the page first loads, when it first loads it is just a blank dropdown box.

<%
Else
%>
<html>
<head>
<title>Load when Empty</title>
</head>
<body>

<form id=form3 name=form3>
<p><select name="d1" size="1">
<option value=""></option>
</select><br>
</p>
</form>
</body>
</html>
<% End If %>