Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dropdown change on input of another dropdown??

    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:

    Code:
    <%@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

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Code:
    <%@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.

  • #3
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Type mismatch: 'listTeachers'

    Line 57
    ??

  • #4
    New to the CF scene
    Join Date
    Oct 2007
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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:

    Code:
    <%@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>

  • #5
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •