3 dependent drop down menu's + sql query

There are two ways this can be accomplished:  using javascript or resubmit the page.

Your decision should be based on the number of items in the boxes.  For instance, if the sum of all of the list box items is > 50 you should use the resubmit the form method.  If under 50, use JS.  This is my personal rule of thumb and it is based on file size.  Because with JS you will be forced to download all of the values which would lead to a large file.

Resubmitting the form is the easiest solution.

i prefer the JS option because i don't want to have to resubmit the page everytime i make a selection. and the sum of all items is less than 50. so how does the Js option work?

mmm, I did the resubmit solution and was told - nope we don't like that - go and do the javascript solution (that was last Friday), I found a starting point for the JS solution at:

http://www.wsabstract.com/script/cut183.shtml

But I still need to move from 2 lists to 3 lists!

Rupert

crucially thiugh i want the results to be coming from a db - please don't forget (as explained in question above)

what you need to do is use the link ruperts provided:  inside of that code you need to create the JS lines while scrolling through a result set.

mikeandbee,

the JS solution is very hard.
(a) Remote Scripting or,
(b) writing the records to a client-side array.
Then,
(c) on the onchange event of a select box, clearing and, writing to spans (cross-browser-compatibility).

I'ld say, you won't get the solution for those points. - You may get the traditional, page reloading method.

Another question: How many does each of the table hold?

A multi-dimensional array. - You won't get it "for free".

I can write a javascript to perform this task - but it is worth more than 100 points.

Kurt

Here is a link that should help you out.  You'll have to modify it to use an array though, populated by your recordset.

http://developer.irt.org/script/1046.htm

give me you email add I will mail you the code and database sample which is helpfull to you . it is created by javascript

FRehman, you mean the "onChange" routine that submits the form is JavaScript, not re-populating the select boxes -- don't you?

If there is no answer until then, I will provide a customized solution for preloading your tables to the client side and, re-populating the select boxes by pure JavaScript, this evening.

Well here is the code to generate the arrays for the JS! I will attempt to solve the rest of this problem today, any help with JS would be appreciated!

In my case the fields are category,type,item.

The sql in stored procedure is:
select distinct category,type,item from my_table

<%
dim conn,rs,sp_sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=mydsn"
Set RS = Conn.Execute("sp_dropdown")

dim string1,string2,string3
dim count1,count2,count3
dim newcat,newtype,newitem,oldcat,oldtype,olditem
count1 = -1
count2 = 0
count3 = 0

do until rs.eof
      newcat  = rs.fields("category").VALUE
      newtype = rs.fields("type").value
      newitem = rs.fields("item").value
      if newcat<>oldcat then
            count1=count1+1
            count2=0
            count3=0
            string1 = string1 & "listone[" & count1 & "]=new option""" & newcat & "" & chr(13)
            string2 = string2 & "listtwo[" & count1 & "][" & count2 & "]=new option""" & newtype & "" & chr(13)
            string3 = string3 & "listthree[" & count1 & "][" & count2 & "][" & count3 & "]=new option""" & newitem & "" & chr(13)
      else
            if newtype<>oldtype then
                  count2=count2+1
                  count3=0
                  string2 = string2 & "listtwo[" & count1 & "][" & count2 & "]=new option""" & newtype & "" & chr(13)
                  string3 = string3 & "listthree[" & count1 & "][" & count2 & "][" & count3 & "]=new option""" & newitem & "" & chr(13)
            else
                  count3=count3+1
                  string3 = string3 & "listthree[" & count1 & "][" & count2 & "][" & count3 & "]=new option""" & newitem & "" & chr(13)
            end if
      end if
      
      
      oldcat = newcat
      oldtype = newtype
      olditem = newitem
      rs.movenext
loop
%>
<html>
<head>
</head>
<body>
<PRE>
<%=string1%>

<%=string2%>

<%=string3%>
</PRE>
</body>
</html>

hi...
i did such thing ,but i used the iframe tag,and i passed the values of table 1 to table 2 and so on...
but,this is wotking only in IE5.

elad

my e-mail is mikevde@hotmail.com.

i didn't realise this was such a difficult question. but if it is feasible, i would be grateful.

thanks

yes robbet
  I give the eample which is onchange how you know that did you try it before

Could be done using RDS if you have that luxury - we do 5 listboxes with rds

FRehman,
because everyone does it that way.

mikeandbee,
building JavaScript arrays from server-side database queries; populating dependant select boxes from those arrays.
I didn't test the code but, if you change the references to your database, it should work, generally.

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

<%
'---create con, rec; open con
Set con = Server.CreateObject("ADODB.Connection")
Set rec = Server.CreateObject("ADODB.Recordset")

con.Open strConnectionString

'---build array-strings to be inserted in JavaScript
Function BuildClientSideArray(strSQ, strColIDName, strColValueName, intArrayNum)
    rec.Open strSQ, con

    strTmp = ""
    i = 0
    Do While Not rec.EOF
        strTmp = "" _
        & "arrSel" & intArrayNum & "_ID(" & int1 & ") = " & rec(strColIDName) & vbCrLf _
        & "arr" & intArrayNum & "_Val(" & int1 & ") = " & rec(strColValueName) & vbCrLf
       
        i = i + 1
        rec.MoveNext
    Loop

    rec.Close
    BuildClientSideArray = strTmp
End Function

strArray1 = BuildClientSideArray("SELECT * FROM table1 ORDER BY type_name", _
"ID", "type_name", 1)
strArray2 = BuildClientSideArray("SELECT * FROM table2 ORDER BY type_name", _
"ID", "type_name", 2)
strArray3 = BuildClientSideArray("SELECT * FROM table3 ORDER BY type_name", _
"ID", "type_name", 3)

'---close, null
con.Close
Set rec = Nothing
Set con = Nothing
%>

<html>
<head>
    <script language=javascript>
        // initialize arrays
        arrSel1_ID = newArray()
        arrSel1_Val = newArray()
        arrSel2_ID = newArray()
        arrSel2_Val = newArray()
        arrSel3_ID = newArray()
        arrSel3_Val = newArray()
       
        // array table1
        <%=strArray1%>
       
        // array table2
        <%=strArray2%>
       
        // array table3
        <%=strArray3%>
       
        // fill select box 1
        function fillSel1() {
            var frm = document.frm
           
            // clear
            frm.sel1.length = 0
           
            // fill
            frm.sel1.options[0].value = ""
            frm.sel1.options[0].text = "Chose below..."

            for (i in arrSel1_ID) {
                frm.sel1.options[i+1].value = arrSel1_ID[i]
                frm.sel1.options[i+1].text = arrSel1_Val[i]
            }
           
            // clear the other select boxes
            frm.sel2.length = 0
            frm.sel3.length = 0
        }
           
        // fill select box 2
        var a = 0
        function fillSel2() {
            var frm = document.frm
           
            // clear
            frm.sel2.length = 0
           
            // fill
            for (i in arrSel2_ID) {
                if (arrSel2_ID[i] == frm.sel1.options[frm.sel1.options.selectedIndex].value) {
                    frm.sel2.options[a].value = arrSel2_ID[a]
                    frm.sel2.options[a].text = arrSel2_Val[a]
                    a += 1
                }
            }
           
            // clear select box 3
            frm.sel3.length = 0
        }
           
        // fill select box 3
        var a = 0
        function fillSel3() {
            var frm = document.frm
           
            // clear
            frm.sel3.length = 0
           
            // fill
            for (i in arrSel3_ID) {
                if (arrSel3_ID[i] == frm.sel2.options[frm.sel2.options.selectedIndex].value) {
                    frm.sel3.options[a].value = arrSel3_ID[a]
                    frm.sel3.options[a].text = arrSel3_Val[a]
                    a += 1
                }
            }
        }
    </script>
</head>
<body onLoad="fillSel1()">

<form name=frm>
<select name=sel1 onChange="fillSel2()">
</select>
<select name=sel2 onChange="fillSel3()">
</select>
<select name=sel2 onChange='alert("Now, submit the form")'>
</select>

</body>
</html>

I've also come up with a solution which has solved my problem.

The only thing is there might be a better way to declare the array size!

Rupert


<%
dim conn,rs,sp_sql
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=" & application("dbDSN") & ";uid=" & application("dbuser") & ";pwd=" & application("dbpass")
Set RS = Conn.Execute("sp_dropdown")

dim string1,string2,string3
dim count1,count2,count3
dim total1,total2,total3
dim newcat,newtype,newitem,oldcat,oldtype,olditem
count1 = -1
count2 = 0
count3 = 0


do until rs.eof
      newcat  = rs.fields("CATEGORY").VALUE
      newtype = rs.fields("type").value
      newitem = rs.fields("item").value
      if newcat<>oldcat then
            count1=count1+1
            count2=0
            count3=0
            total2=total2+1
            total3=total3+1
            string1 = string1 & "list1_array[" & count1 & "]=new Option(""" & newcat & """);" & chr(13)
            string2 = string2 & "list2_array[" & count1 & "] [" & count2 & "]=new Option(""" & newtype & """);" & chr(13)
            string3 = string3 & "list3_array[" & count1 & "] [" & count2 & "][" & count3 & "]=new Option(""" & newitem & """);" & chr(13)
      else
            if newtype<>oldtype then
                  count2=count2+1
                  count3=0
                  
                  total2=total2+1
                  total3=total3+1
                  
                  string2 = string2 & "list2_array[" & count1 & "] [" & count2 & "]=new Option(""" & newtype & """);" & chr(13)
                  string3 = string3 & "list3_array[" & count1 & "] [" & count2 & "] [" & count3 & "]=new Option(""" & newitem & """);" & chr(13)
            else
                  count3=count3+1
                  total3=total3+1
                  string3 = string3 & "list3_array[" & count1 & "] [" & count2 & "] [" & count3 & "]=new Option(""" & newitem & """);" & chr(13)
            end if
      end if
      
      total1 = count1
      oldcat = newcat
      oldtype = newtype
      olditem = newitem
      rs.movenext
loop
%>
<html>
<head>
<script>
<!--

var list1_array=new Array();
var list2_array=new Array(99);
var list3_array=new Array(99);

for(i=0;i<99;i++)
{
list2_array[i]=new Array();
list3_array[i]=new Array(99);

for(k=0; k<13;k++)
{
list3_array[i][k]=new Array();
}
}

<%=string1%>



<%=string2%>



<%=string3%>


function fillall()
{
      fillone();
      filltwo(0);
      fillthree(0);
}

function fillone(){
      var lb1=document.doublecombo.listbox1
      var array1length = (list1_array.length)
      
      for (i=0;i<array1length;i++){
            lb1.options[i]=new Option(list1_array[i].text);
      }
      lb1.options[0].selected=true
}

function filltwo(x){
      var lb2=document.doublecombo.listbox2
      var array2length = (list2_array[x].length)
      
      for(m=lb2.options.length-1;m>0;m--){
      lb2.options[m]=null
      }
      
      for (i=0;i<array2length;i++){
            lb2.options[i]=new Option(list2_array[x][i].text);
      }
      lb2.options[0].selected=true
      fillthree(0)
}

function fillthree(y){
      var lb3=document.doublecombo.listbox3
      
      for(m=lb3.options.length-1;m>0;m--){
      lb3.options[m]=null
      }
      
      var x =document.doublecombo.listbox1.options.selectedIndex
      var array3length = (list3_array[x][y].length)
      
      for (i=0;i<array3length;i++){
            lb3.options[i]=new Option(list3_array[x][y][i].text);
      }
      lb3.options[0].selected=true
}
//-->
</script>
</head>
<body onload="fillall()">
<form name="doublecombo">
<select name="listbox1" size="1" onChange="filltwo(this.options.selectedIndex)"></select>
<select name="listbox2" size="1" onChange="fillthree(this.options.selectedIndex)"></select>
<select name="listbox3" size="1" </select>
</form>
</body>
</html>

ruperts,
thanks for example but am having some trouble getting it to work - can you tell me what i need to do? for example, the
Set RS = Conn.Execute("sp_dropdown")

doesn't do anything - i expect it to execute an sql query.
but if you can explain this it will probably become clear.
thanks,
mb

will e-mail you!

I have Country-State-City depending listboxes in my system. Increase the points to 1000 and I'll give you working code. You must just write your sql queries.

thanks a lot!