...

View Full Version : Dropdown change on input of another dropdown??



rlawrason
10-26-2007, 03:51 PM
Hi guys, I'm a Windows/Unix server systems admin who kind of got web application development dropped in my lap, and I really don't know the first thing about javascript and asp (as you can probably see from my other post on this forum, lol).

I'm having a bit of trouble getting this part of my form to work...when the user selects Teacher1 from the first dropdown, I need the second dropdown to only display the students in Teacher1's class...the same in Teacher2 case.

I know that to do this, I need to change line 37 to include the variable input from the first dropdown...but I have no idea how to go about doing that. Please help :(

Here's the piece of code I've got so far:



<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
Option Explicit

dim objConn, objRS, objRS2, strSQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")

objConn.ConnectionString = Server.MapPath("dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

Sub showDropDownList(c, r, table)

strSQL = "SELECT Teacher FROM " & table & " ORDER BY Teacher ASC"
Set r = c.Execute(strSQL)

if r.eof = false then
Response.Write "<form name='form1'>"
Response.Write "<select name='dropdownlist'>" & vbCrLf
Response.Write "<option selected='selected' value='blank'>Choose....</option>"
While r.EOF = false
Response.Write "<option value='" & r.Fields("Teacher").Value & "'>" & r.Fields("Teacher").Value & "</option>" & vbCrLf
r.movenext
Wend
end if
Response.Write "</select>" & vbCrLf

End Sub

Sub showDropDownList2(c, r, table)

strSQL = "SELECT Student FROM " & table & " WHERE Teacher = 'Teacher1' ORDER BY Student ASC"
Set r = c.Execute(strSQL)

if r.eof = false then
Response.Write "<select name='dropdownlist2'>" & vbCrLf
Response.Write "<option selected='selected' value='blank'>Choose....</option>"
While r.EOF = false
Response.Write "<option value='" & r.Fields("Student").Value & "'>" & r.Fields("Student").Value & "</option>" & vbCrLf
r.movenext
Wend
end if
Response.Write "</select></form>" & vbCrLf

End Sub
%>


<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Dropdown Testing</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<h1>Dropdown Testing</h1>

<table width='300' border='0'>
<tr>
<td width='75'>Teachers:</td>
<td width='225'><% call showDropDownList(objConn, objRS, "Teachers") %></td>
</tr>
<tr><td width='300'>&nbsp;</td></tr>
<tr>
<td width='75'>Students:</td>
<td width='225'><% call showDropDownList2(objConn, objRS2, "Students") %></td>

</body>
</html>


And Here's a link to my little test database so you can load up the site: http://www.mediafire.com/?8tv2b0dzj9g

I really appreciate any insight you guys might have :)

Spudhead
10-26-2007, 05:44 PM
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
Option Explicit

Sub listTeachers(cn)
dim strSQL, oRs, sOutput
strSQL = "SELECT Teacher FROM Teachers ORDER BY Teacher ASC"
sOutput = "<select name=""selTeachers"" onchange=""repopulateDropDowns(this.options[this.selectedIndex].value)"">" & vbCrLf
sOutput = sOutput & "<option selected=""selected"" value=""blank"">Choose....</option>"
set oRs = cn.Execute(strSQL)
if not oRs.eof then
While not oRs.EOF
sOutput = sOutput & "<option value=""" & oRs.Fields("Teacher").Value & """>" & oRs.Fields("Teacher").Value & "</option>" & vbCrLf
oRs.movenext
Wend
end if
sOutput = sOutput & "</select>" & vbCrLf
oRs.close
Set oRs = nothing
listTeachers = sOutput
End Sub


Sub listStudents(cn, sTeacher)
dim strSQL, oRs, sOutput
if sTeacher = "" then sTeacher = "Teacher1"
strSQL = "SELECT Student FROM Students WHERE Teacher = '" & sTeacher & "' ORDER BY Student ASC"
sOutput = "<select name=""selStudents"">" & vbCrLf
sOutput = sOutput & "<option selected=""selected"" value=""blank"">Choose....</option>"
Set oRs = cn.Execute(strSQL)
if oRs.eof = false then
While not oRs.EOF
sOutput = sOutput & "<option value=""" & oRs.Fields("Student ").Value & """>" & oRs.Fields("Student ").Value & "</option>" & vbCrLf
oRs.movenext
Wend
end if
sOutput = sOutput & "</select>" & vbCrLf
oRs.close
Set oRs = nothing
listStudents = sOutput
End Sub


'=========================================================================

dim objConn, sTeachers, sStudents, sSelectedTeacher

sSelectedTeacher = request.querystring("teacher") & ""


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = Server.MapPath("dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open
sTeachers = listTeachers(objConn)
if sSelectedTeacher <> "" then
sSelectedTeacher = replace(sSelectedTeacher, "'", "''")
sStudents = listStudents(objConn, sSelectedTeacher)
end if
objConn.Close
set objConn = nothing

%>


<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Dropdown Testing</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<script type="text/javascript">
function repopulateDropDowns(selectedTeacher){
sHref = "mypage.asp?teacher=" + selectedTeacher;
window.location.href = sHref;
}
</script>

</head>

<body>
<h1>Dropdown Testing</h1>

<form name="form1">

<table width='300' border='0'>
<tr>
<td width='75'>Teachers:</td>
<td width='225'><%=sTeachers%></td>
</tr>
<tr><td width='300' colspan="2">&nbsp;</td></tr>
<tr>
<td width='75'>Students:</td>
<td width='225'><%=sStudents%></td>
</tr>
</table>

</form>


</body>
</html>


Ok, it's not perfect but it's the nearest I could get it to your code, hopefully it still makes sense. The modern way of updating content dynamically from databases is to use XMLHTTP to fire off requests for data to a separate ASP script, rather than force the entire page to reload, which is what this script does. But we'll go with it for now.

So... a few changes. This is basically the traditional approach to the 'dynamic dropdown' problem. First, the javascript bit. You put an onchange handler in your "Teachers" select box; now you can respond when the user selects something in it. The handler calls a javascript function that reloads your page but with the selected teacher added as a querystring value.

Your ASP, in the meantime, is looking for that value. Whenever the page is called, it always calls listTeachers() to create a select box of teachers. If it finds something in the querystring, it cleans it a bit to make sure it won't break your database (this is worthy of further investigation BTW - google "SMTP injection") and then runs listStudents() with it. Simple.

:)

Sorry if this reads like nonsense, it's Friday afternoon and my brain is a bit mushy. Hope it helps.

rlawrason
10-26-2007, 06:00 PM
Type mismatch: 'listTeachers'

Line 57
??

rlawrason
10-26-2007, 06:56 PM
I didn't really understand everything that was going on in your code, but the paragraph you wrote about the javascript setting a querystring variable and reloading with onchange was very helpful.

It's not the best code in the world, but I got it working using that bit of information:



<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
Option Explicit

dim objConn, objRS, objRS2, strSQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")

objConn.ConnectionString = Server.MapPath("dropdowns.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

Sub showDropDownList(c, r, table)

strSQL = "SELECT Teacher FROM " & table & " ORDER BY Teacher ASC"
Set r = c.Execute(strSQL)

if request.querystring("teacher") = "" then
if r.eof = false then
Response.Write "<form name='form1'>"
Response.Write "<select name='dropdownlist' onchange=""repopulateDropDowns(this.options[this.selectedIndex].value)"">" & vbCrLf
Response.Write "<option selected='selected' value='blank'>Choose....</option>"
While r.EOF = false
Response.Write "<option value='" & r.Fields("Teacher").Value & "'>" & r.Fields("Teacher").Value & "</option>" & vbCrLf
r.movenext
Wend
end if
else
if r.eof = false then
Response.Write "<form name='form1'>"
Response.Write "<select name='dropdownlist' onchange=""repopulateDropDowns(this.options[this.selectedIndex].value)"">" & vbCrLf
Response.Write "<option selected='selected' value='" & request.querystring("teacher") & "'>" & request.querystring("teacher") & "</option>"
Response.Write "<option value='blank'>---------------</option>"
While r.EOF = false
Response.Write "<option value='" & r.Fields("Teacher").Value & "'>" & r.Fields("Teacher").Value & "</option>" & vbCrLf
r.movenext
Wend
end if
end if
Response.Write "</select>" & vbCrLf

End Sub

Sub showDropDownList2(c, r, table)

strSQL = "SELECT Student FROM " & table & " WHERE Teacher = '" & request.querystring("teacher") & "' ORDER BY Student ASC"
Set r = c.Execute(strSQL)

if r.eof = false then
Response.Write "<select name='dropdownlist2'>" & vbCrLf
Response.Write "<option selected='selected' value='blank'>Choose....</option>"
While r.EOF = false
Response.Write "<option value='" & r.Fields("Student").Value & "'>" & r.Fields("Student").Value & "</option>" & vbCrLf
r.movenext
Wend
else
end if
Response.Write "</select></form>" & vbCrLf

End Sub
%>

<script type="text/javascript">
function repopulateDropDowns(selectedTeacher){
sHref = "database.asp?teacher=" + selectedTeacher;
window.location.href = sHref;
}
</script>
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Dropdown Testing</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>

<body>
<h1>Dropdown Testing</h1>

<table width='300' border='0'>
<tr>
<td width='75'>Teachers:</td>
<td width='225'><% call showDropDownList(objConn, objRS, "Teachers") %></td>
</tr>
<tr><td width='300'>&nbsp;</td></tr>
<tr>
<td width='75'>Students:</td>
<td width='225'><% call showDropDownList2(objConn, objRS2, "Students") %></td>

</body>
</html>


:thumbsup:

Spudhead
10-29-2007, 07:22 PM
Yep that'll work too :)

BUT

google "SQL Injection" - if I hit your page with:

page.asp?teacher=x';DROP TABLE Teacher;--

then you're in a whole world of pain. That's an extreme example - but there are lots of things that people could even unwittingly put into the querystring that will break your application. Always validate user input.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum