#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2004
    Posts
    8
    Rep Power
    0

    Angry Multi Drop Down Filtering Selection Issues


    I was trying to set up 3 menue drop downs for filtered relational selections of 1st-(Buildings), 2nd (Departments) followed by (Projects). I foillowed all the links and can not see how to get a onchange to work as relational menue. This would have been best. But 3 days later I am tight for time so I was attempting to just setup of the time being 3 seperate drop downs with submit. I can only get the departments to work. The building and projects will not filter as the departments.

    What am I missing ? (This is my first attempt at an ASP using Access 2000)

    I have also gone thru all postings on 3 or 4 seperate forums and tutorials and having difficulty figuring how to get on-change to work. I am totally lost. I have revied and rereviewed the dataset and code. All 3 of the catagory codes are identical except for its link to the dataset which are correctly linked. I am ??????????


    Code:
     
    ((((((((( Code Below is selection of the The Buildings {{ Does Not work }} ))))))))))
     
    <form action="default.asp" method="get" name="form1" target="_self">
    		<p>Building:
    		 <select name="Bldg" id="Bldg">
    			<option value="0">Sel-Bldg</option>
    			<%
    While (NOT rsFac.EOF)
    %>
    			<option value="<%=(rsFac.Fields.Item("txtBuildingLocation").Value)%>"><%=(rsFac.Fields.Item("txtBuildingLocation").Value)%></option>
    			<%
    rsFac.MoveNext()
    Wend
    If (rsFac.CursorType > 0) Then
    rsFac.MoveFirst
    Else
    rsFac.Requery
    End If
    %>
    		 </select>
    <input name="SubmitBldg" type="submit" id="SubmitBldg" value="Get-Bldg"></p>
    	 </form>
     
     
    ((((((((( This is my first successful dropdown menue that allowed populations of info from an access dataset and shown only filtered requested itrems on the page )))))))))
     
    <form action="default.asp" method="get" name="form2" target="_self">
    		<p>Department:
    		 <select name="Dept" id="Dept">
    			<option value="0">Sel-Dept</option>
    			<%
    While (NOT rsDepartment.EOF)
    %>
    			<option value="<%=(rsDepartment.Fields.Item("txtDepartmentName").Value)%>"><%=(rsDepartment.Fields.Item("txtDepartmentName").Value)%></option>
    			<%
    rsDepartment.MoveNext()
    Wend
    If (rsDepartment.CursorType > 0) Then
    rsDepartment.MoveFirst
    Else
    rsDepartment.Requery
    End If
    %>
    		 </select>
    <input name="SubmitDept" type="submit" id="SubmitDept" value="Get-Dept"></p>
    	 </form>
     
     
    ((((((((( Code Below is selection of the Project {{ Does Not work }}))))))))))
     
    <form action="default.asp" method="get" name="form3" target="_self">
    </p>Project Name:
    <select name="Proj" id="Proj">
    <option value="0">Sel-Proj</option>
    <%
    While (NOT rsProjSpec.EOF)
    %>
    <option value="<%=(rsProjSpec.Fields.Item("txtProjectName").Value)%>"><%=(rsProjSpec.Fields.Item("txtProjectName").Value)%></option>
    <%
    rsProjSpec.MoveNext()
    Wend
    If (rsProjSpec.CursorType > 0) Then
    rsProjSpec.MoveFirst
    Else
    rsProjSpec.Requery
    End If
    %>
    		</select>
    		<input name="SubmitProj" type="submit" id="SubmitProj" value="Get-Project">
    	 </form>
  2. #2
  3. The Laughing Moderator
    ASP Scholar (3000 - 3499 posts)

    Join Date
    Apr 2004
    Location
    Sydney, Australia
    Posts
    3,268
    Rep Power
    41
    Hi, hopefully this response isn't too late to help you with your problem.

    The code below sets up a triple linked list box, so that a selection in box 1 will populate box 2, and a selection in box 2 will populate box 3. Haven't had time to comment too well so if you have any questions just post back.

    This is the JavaScript that controls the listbox population and clearing:
    Code:
    <SCRIPT LANGUAGE="JavaScript">
    <!-- 
    function clearcombo(elem){
     var i;
     for (i = elem.options.length; i >= 0; i--) elem.options[i] = null;
     elem.selectedIndex = -1;
    }
    function populatecombo2(elem, index){
     if (array1.length >= index){
      if (array1[index]){
       for (var i = 0; i < array1[index].length; i= i + 2){
    	elem.options[elem.options.length] = new Option(array1[index][i + 1], array1[index][i]);
       }
      }
      else{
       elem.options[elem.options.length] = new Option("[none available]", 0);
      }
     }
     else{
      elem.options[elem.options.length] = new Option("[none available]", 0);
     }
    }
    function populatecombo3(elem, index){
     if (array2.length >= index){
      if (array2[index]){
       for (var i = 0; i < array2[index].length; i= i + 2){
    	elem.options[elem.options.length] = new Option(array2[index][i + 1], array2[index][i]);
       }
      }
      else{
       elem.options[elem.options.length] = new Option("[none available]", 0);
      }
     }
     else{
      elem.options[elem.options.length] = new Option("[none available]", 0);
     }
    }
    function clickcombo(nWhich,elem1,elem2,elem3){
     if (nWhich == 1){
      clearcombo(elem2);
      clearcombo(elem3);
      populatecombo2(elem2, elem1[elem1.selectedIndex].value);
     }
     if (nWhich == 2){
      clearcombo(elem3);
      populatecombo3(elem3, elem2[elem2.selectedIndex].value);
     }
     return true;
    }
    // -->
    </script>
    Then the VBScript function that controls the display of the listboxes:
    Code:
    <%
    ' this section of code is self-contained and suitable to be moved to an include file
    Function TripleLinkedList(oCon, sQuery, sFormFieldName, nSize, sDBField1, sDBField2, sDBField3, sDBFieldResult)
     ' this is a general-purpose routine that implements triple-linked
     ' listboxes. here is the drill
     Dim sTemp  ' general-purpose temp variable
     Dim sScript  ' bucket for holding the script structure
     Dim sSelect  ' bucket for the <SELECT> statement
     Dim sArray1  ' bucket to store the DBField2 array
     Dim nField1  ' counter for the primary array
     Dim sArray2  ' bucket to store the DBField3 array
     Dim nField2  ' counter for the secondary array
     Dim rs   ' recordset
     Dim sLastVal1 ' comparison string to test for record changes
     Dim sLastVal2 ' comparison string to test for record changes
     On Error Resume Next
     If Not IsObject(oCon) Then
      sScript = "error processing triplelist -- need a connection object."
     ElseIf oCon.State <> 1 Then
      If Err.number <> 0 Then
       sScript = "error processing triplelist -- invalid connection object."
      Else
       sScript = "error processing triplelist -- connection is not open."
      End If
     Else
      Set rs = oCon.Execute(sQuery)
      If Err.number <> 0 Then
       sScript = "error processing query. Error " & Hex(Err.number) & ": " & Err.Description
      ElseIf rs.EOF Then
       sScript = "no records found -- seems wrong"
      Else
       On Error Goto 0 
       sScript = "<SCR" & "IPT LANGUAGE=""JavaScript"">" & vbCrlf
       sScript = sScript & "var array1 = new Array();" & vbCrlf
       sScript = sScript & "var array2 = new Array();" & vbCrlf
       sSelect = "<SELECT NAME=""" & sFormFieldName & "1"" SIZE=""" & nSize & _
    	""" ONCHANGE=""return(clickcombo(1,document.forms[0]." & _
    	sFormFieldName & "1,document.forms[0]." & sFormFieldName & _
    	"2,document.forms[0]." & sFormFieldName & "3));""><OPTION>Select a value</OPTION>" & vbCrlf
       sLastVal1 = "empty" ' set up a default test value...
    	  Do Until rs.EOF
    		  If rs(sDBField1) <> sLastVal1 Then
    	 If Right(sArray1, 3) = "," & vbCrlf Then
    	  ' pull off any trailing commas
    	  sArray1 = Left(sArray1, Len(sArray1) - 3)
    	 End If
    	 If Len(sArray1) > 0 Then
    	  sArray1 = sArray1 & ");" & vbCrlf
    	 End If
    	 ' pick up new information for this row...
    	 nField1 = nField1 + 1
    	 sLastVal1 = rs(sDBField1)
    	 ' write the new contents of field 1 to the select statement
    	 sSelect = sSelect & "<OPTION VALUE=" & nField1 & ">" & sLastVal1 & "</OPTION>"
    	 ' write a new entry in array1 for the field 2 values...
    	 sArray1 = sArray1 & "array1[" & nField1 & "] = new Array(" & vbCrlf
    	 ' and reset the test values for field 2
    	 nField2 = 0
    	 sLastVal2 = "empty"
    	End If
    	If sLastVal2 <> rs(sDBField2) Then
    	 If Right(sArray2, 3) = "," & vbCrlf Then
    	  ' pull off any trailing commas
    	  sArray2 = Left(sArray2, Len(sArray2) - 3)
    	 End If
    	 If Len(sArray2) > 0 Then
    	  sArray2 = sArray2 & ");" & vbCrlf
    	 End If
    	 ' pick up new information for this row...
    	 sLastVal2 = rs(sDBField2)
    	 nField2 = nField2 + 1
    	 ' write a new entry in array1 containing this set of field 2 values...
    	 sArray1 = sArray1 & "	" & 1000 * nField1 + nField2 & ",""" & sLastVal2 & """," & vbCrlf
    	 ' write a new entry in array2 for this set of field 3 values...
    	 sArray2 = sArray2 & "// values for " & sLastVal2 & vbCrlf
    	 sArray2 = sArray2 & "array2[" & 1000 * nField1 + nField2 & "] = new Array(" & vbCrlf
    	End If
    	' write the field3 values to the field2 array...
    	sArray2 = sArray2 & "	" & rs(sDBFieldResult) & ",""" & rs(sDBField3) & """," & vbCrlf
    	rs.MoveNext ' move on to the next record...
       Loop
       ' if these arrays weren't previously closed out, then close them now
       If Right(sArray2, 3) = "," & vbCrlf Then
    	' pull off any trailing commas
    	sArray2 = Left(sArray2, Len(sArray2) - 3)
       End If
       If Len(sArray2) > 0 Then
    	sArray2 = sArray2 & ");" & vbCrlf
       End If
       ' if these arrays weren't previously closed out, then close them now
       If Right(sArray1, 3) = "," & vbCrlf Then
    	' pull off any trailing commas
    	sArray1 = Left(sArray1, Len(sArray1) - 3)
       End If
       If Len(sArray1) > 0 Then
    	sArray1 = sArray1 & ");" & vbCrlf
       End If
       ' close out the listbox/combobox and add the second and third listbox/combobox entries...
       sSelect = sSelect & "</SELECT>" & vbCrlf
       sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "2"" SIZE=""" & nSize & _
    	""" ONCHANGE=""return(clickcombo(2,document.forms[0]." & sFormFieldName & _
    	"1,document.forms[0]." & sFormFieldName & "2,document.forms[0]." & _
    	sFormFieldName & "3));""><OPTION>Select a value</OPTION></SELECT>" & vbCrlf
       sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "3"" SIZE=""" & nSize & _
    	"""><OPTION>Select a value</OPTION></SELECT>" & vbCrlf
       ' finally clean up the script and write the whole thing out as a block
       sScript = sSelect & vbCrlf & _
    	sScript & vbCrlf & _
    	sArray1 & vbCrlf & _
    	sArray2 & vbCrlf & _
    	"</SCR" & "IPT>" & vbCrlf
      ' -----------------------------------------------------------------
      End If
      ' close and free the recordset
      rs.Close
      Set rs = Nothing
     End If
     ' and get the fleep outta here
     TripleLinkedList = sScript
    End Function
    %>
    Then the normal db connections, form and call to the functions:
    Code:
     <% 
    Dim Conn
    Dim sQuery
    Set Conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("yourdbname.mdb")
    conn.Open ' connect to the database
    ' -----------------------------------------------
    ' then set up a query
    ' -----------------------------------------------
    sQuery = "SELECT DISTINCT * from tablename Order By columnA, columnB " ' << insert your own sql here
    %>
    <FORM id=form1 name=form1>
    <CENTER>
    <%
    Response.Write TripleLinkedList(Conn, sQuery, "listboxname", 1, "dbfieldname-for-1st-listbox", "dbfieldname-for-2nd-listbox", "dbfieldname-for-3rd-listbox", "name-of-the-result-from-sql")
    Conn.Close
    Set Conn = Nothing
    %>
    <P>
    </FORM>
    Hope you can follow through and fit to your requirements. Have fun
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2009
    Posts
    4
    Rep Power
    0
    Originally Posted by Lafinboy
    Hi, hopefully this response isn't too late to help you with your problem.

    The code below sets up a triple linked list box, so that a selection in box 1 will populate box 2, and a selection in box 2 will populate box 3. Haven't had time to comment too well so if you have any questions just post back.

    This is the JavaScript that controls the listbox population and clearing:
    Code:
    <SCRIPT LANGUAGE="JavaScript">
    <!-- 
    function clearcombo(elem){
     var i;
     for (i = elem.options.length; i >= 0; i--) elem.options[i] = null;
     elem.selectedIndex = -1;
    }
    function populatecombo2(elem, index){
     if (array1.length >= index){
      if (array1[index]){
       for (var i = 0; i < array1[index].length; i= i + 2){
    	elem.options[elem.options.length] = new Option(array1[index][i + 1], array1[index][i]);
       }
      }
      else{
       elem.options[elem.options.length] = new Option("[none available]", 0);
      }
     }
     else{
      elem.options[elem.options.length] = new Option("[none available]", 0);
     }
    }
    function populatecombo3(elem, index){
     if (array2.length >= index){
      if (array2[index]){
       for (var i = 0; i < array2[index].length; i= i + 2){
    	elem.options[elem.options.length] = new Option(array2[index][i + 1], array2[index][i]);
       }
      }
      else{
       elem.options[elem.options.length] = new Option("[none available]", 0);
      }
     }
     else{
      elem.options[elem.options.length] = new Option("[none available]", 0);
     }
    }
    function clickcombo(nWhich,elem1,elem2,elem3){
     if (nWhich == 1){
      clearcombo(elem2);
      clearcombo(elem3);
      populatecombo2(elem2, elem1[elem1.selectedIndex].value);
     }
     if (nWhich == 2){
      clearcombo(elem3);
      populatecombo3(elem3, elem2[elem2.selectedIndex].value);
     }
     return true;
    }
    // -->
    </script>
    Then the VBScript function that controls the display of the listboxes:
    Code:
    <%
    ' this section of code is self-contained and suitable to be moved to an include file
    Function TripleLinkedList(oCon, sQuery, sFormFieldName, nSize, sDBField1, sDBField2, sDBField3, sDBFieldResult)
     ' this is a general-purpose routine that implements triple-linked
     ' listboxes. here is the drill
     Dim sTemp  ' general-purpose temp variable
     Dim sScript  ' bucket for holding the script structure
     Dim sSelect  ' bucket for the <SELECT> statement
     Dim sArray1  ' bucket to store the DBField2 array
     Dim nField1  ' counter for the primary array
     Dim sArray2  ' bucket to store the DBField3 array
     Dim nField2  ' counter for the secondary array
     Dim rs   ' recordset
     Dim sLastVal1 ' comparison string to test for record changes
     Dim sLastVal2 ' comparison string to test for record changes
     On Error Resume Next
     If Not IsObject(oCon) Then
      sScript = "error processing triplelist -- need a connection object."
     ElseIf oCon.State <> 1 Then
      If Err.number <> 0 Then
       sScript = "error processing triplelist -- invalid connection object."
      Else
       sScript = "error processing triplelist -- connection is not open."
      End If
     Else
      Set rs = oCon.Execute(sQuery)
      If Err.number <> 0 Then
       sScript = "error processing query. Error " & Hex(Err.number) & ": " & Err.Description
      ElseIf rs.EOF Then
       sScript = "no records found -- seems wrong"
      Else
       On Error Goto 0 
       sScript = "<SCR" & "IPT LANGUAGE=""JavaScript"">" & vbCrlf
       sScript = sScript & "var array1 = new Array();" & vbCrlf
       sScript = sScript & "var array2 = new Array();" & vbCrlf
       sSelect = "<SELECT NAME=""" & sFormFieldName & "1"" SIZE=""" & nSize & _
    	""" ONCHANGE=""return(clickcombo(1,document.forms[0]." & _
    	sFormFieldName & "1,document.forms[0]." & sFormFieldName & _
    	"2,document.forms[0]." & sFormFieldName & "3));""><OPTION>Select a value</OPTION>" & vbCrlf
       sLastVal1 = "empty" ' set up a default test value...
    	  Do Until rs.EOF
    		  If rs(sDBField1) <> sLastVal1 Then
    	 If Right(sArray1, 3) = "," & vbCrlf Then
    	  ' pull off any trailing commas
    	  sArray1 = Left(sArray1, Len(sArray1) - 3)
    	 End If
    	 If Len(sArray1) > 0 Then
    	  sArray1 = sArray1 & ");" & vbCrlf
    	 End If
    	 ' pick up new information for this row...
    	 nField1 = nField1 + 1
    	 sLastVal1 = rs(sDBField1)
    	 ' write the new contents of field 1 to the select statement
    	 sSelect = sSelect & "<OPTION VALUE=" & nField1 & ">" & sLastVal1 & "</OPTION>"
    	 ' write a new entry in array1 for the field 2 values...
    	 sArray1 = sArray1 & "array1[" & nField1 & "] = new Array(" & vbCrlf
    	 ' and reset the test values for field 2
    	 nField2 = 0
    	 sLastVal2 = "empty"
    	End If
    	If sLastVal2 <> rs(sDBField2) Then
    	 If Right(sArray2, 3) = "," & vbCrlf Then
    	  ' pull off any trailing commas
    	  sArray2 = Left(sArray2, Len(sArray2) - 3)
    	 End If
    	 If Len(sArray2) > 0 Then
    	  sArray2 = sArray2 & ");" & vbCrlf
    	 End If
    	 ' pick up new information for this row...
    	 sLastVal2 = rs(sDBField2)
    	 nField2 = nField2 + 1
    	 ' write a new entry in array1 containing this set of field 2 values...
    	 sArray1 = sArray1 & "	" & 1000 * nField1 + nField2 & ",""" & sLastVal2 & """," & vbCrlf
    	 ' write a new entry in array2 for this set of field 3 values...
    	 sArray2 = sArray2 & "// values for " & sLastVal2 & vbCrlf
    	 sArray2 = sArray2 & "array2[" & 1000 * nField1 + nField2 & "] = new Array(" & vbCrlf
    	End If
    	' write the field3 values to the field2 array...
    	sArray2 = sArray2 & "	" & rs(sDBFieldResult) & ",""" & rs(sDBField3) & """," & vbCrlf
    	rs.MoveNext ' move on to the next record...
       Loop
       ' if these arrays weren't previously closed out, then close them now
       If Right(sArray2, 3) = "," & vbCrlf Then
    	' pull off any trailing commas
    	sArray2 = Left(sArray2, Len(sArray2) - 3)
       End If
       If Len(sArray2) > 0 Then
    	sArray2 = sArray2 & ");" & vbCrlf
       End If
       ' if these arrays weren't previously closed out, then close them now
       If Right(sArray1, 3) = "," & vbCrlf Then
    	' pull off any trailing commas
    	sArray1 = Left(sArray1, Len(sArray1) - 3)
       End If
       If Len(sArray1) > 0 Then
    	sArray1 = sArray1 & ");" & vbCrlf
       End If
       ' close out the listbox/combobox and add the second and third listbox/combobox entries...
       sSelect = sSelect & "</SELECT>" & vbCrlf
       sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "2"" SIZE=""" & nSize & _
    	""" ONCHANGE=""return(clickcombo(2,document.forms[0]." & sFormFieldName & _
    	"1,document.forms[0]." & sFormFieldName & "2,document.forms[0]." & _
    	sFormFieldName & "3));""><OPTION>Select a value</OPTION></SELECT>" & vbCrlf
       sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "3"" SIZE=""" & nSize & _
    	"""><OPTION>Select a value</OPTION></SELECT>" & vbCrlf
       ' finally clean up the script and write the whole thing out as a block
       sScript = sSelect & vbCrlf & _
    	sScript & vbCrlf & _
    	sArray1 & vbCrlf & _
    	sArray2 & vbCrlf & _
    	"</SCR" & "IPT>" & vbCrlf
      ' -----------------------------------------------------------------
      End If
      ' close and free the recordset
      rs.Close
      Set rs = Nothing
     End If
     ' and get the fleep outta here
     TripleLinkedList = sScript
    End Function
    %>
    Then the normal db connections, form and call to the functions:
    Code:
     <% 
    Dim Conn
    Dim sQuery
    Set Conn = Server.CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("yourdbname.mdb")
    conn.Open ' connect to the database
    ' -----------------------------------------------
    ' then set up a query
    ' -----------------------------------------------
    sQuery = "SELECT DISTINCT * from tablename Order By columnA, columnB " ' << insert your own sql here
    %>
    <FORM id=form1 name=form1>
    <CENTER>
    <%
    Response.Write TripleLinkedList(Conn, sQuery, "listboxname", 1, "dbfieldname-for-1st-listbox", "dbfieldname-for-2nd-listbox", "dbfieldname-for-3rd-listbox", "name-of-the-result-from-sql")
    Conn.Close
    Set Conn = Nothing
    %>
    <P>
    </FORM>
    Hope you can follow through and fit to your requirements. Have fun

    -------------

    Would you mind posting javascript version for this vbscript?
    I had a hard time trying to understand the script...TwT

Similar Threads

  1. need help regarding drop down box
    By rajmehta in forum ASP Development
    Replies: 1
    Last Post: April 4th, 2004, 03:17 AM
  2. How to response.write the selection of drop down ?
    By gcoxy in forum ASP Development
    Replies: 2
    Last Post: April 2nd, 2004, 04:32 AM
  3. Replies: 1
    Last Post: March 19th, 2004, 02:05 PM
  4. Field disabling based on drop down list selection....
    By Steve Schofield in forum SQL Development
    Replies: 0
    Last Post: December 13th, 2001, 05:01 PM
  5. DROPDOWN MENU THAT DOES MULTI DATA ENTRIES WITH EACH SELECTION ?
    By Steve Schofield in forum ASP Development
    Replies: 1
    Last Post: November 5th, 2000, 02:20 PM

IMN logo majestic logo threadwatch logo seochat tools logo