Solved

3 dependent drop down menu's + sql query

Posted on 2000-03-29
23
260 Views
Last Modified: 2010-05-18
Hello,
This question has 2 sections:
first, I want to have 3 dependent drop down menus - i have an access db with three tables which are relational. The first table (table1) has an id and type name. the second table (table2) has an id, type1_id (which references the id of type1 table), and type2_name. The 3rd table (table3) has an id, class_type_id (which references id of type2 table, and type3_name.

so when i pick a type from table1, i want it to retrieve all the values from table2 that match the id of table1. Then, i want the second menu to select all the values from table3 that match the id of table2.
how can i do this?
the second part is that when the value from the 3rd drop-down has been selected, i want to view the data from the table that is related to this value. so i need to write a dynamic sql query that will take the id of the value selectd from table 3.
i hope this explains clearly what i want, but let me know if more info is needed about the db.
thanks,
mb
0
Comment
Question by:mikeandbee
  • 5
  • 5
  • 4
  • +7
23 Comments
 
LVL 1

Expert Comment

by:fallon12345
ID: 2666522
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.
0
 

Author Comment

by:mikeandbee
ID: 2666704
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?
0
 
LVL 4

Expert Comment

by:ruperts
ID: 2666751
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
0
 

Author Comment

by:mikeandbee
ID: 2666769
crucially thiugh i want the results to be coming from a db - please don't forget (as explained in question above)
0
 
LVL 1

Expert Comment

by:fallon12345
ID: 2666953
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.
0
 
LVL 15

Expert Comment

by:robbert
ID: 2666992
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?
0
 
LVL 15

Expert Comment

by:robbert
ID: 2667002
A multi-dimensional array. - You won't get it "for free".
0
 
LVL 11

Expert Comment

by:kmartin7
ID: 2667026
I can write a javascript to perform this task - but it is worth more than 100 points.

Kurt
0
 
LVL 8

Expert Comment

by:drittich
ID: 2667378
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
0
 
LVL 4

Expert Comment

by:FRehman
ID: 2668016
give me you email add I will mail you the code and database sample which is helpfull to you . it is created by javascript
0
 
LVL 15

Expert Comment

by:robbert
ID: 2668137
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:ruperts
ID: 2668512
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>
0
 
LVL 1

Expert Comment

by:eladr
ID: 2668576
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
0
 

Author Comment

by:mikeandbee
ID: 2668793
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
0
 
LVL 4

Expert Comment

by:FRehman
ID: 2668850
yes robbet
  I give the eample which is onchange how you know that did you try it before
0
 
LVL 3

Expert Comment

by:Flubbadub
ID: 2671482
Could be done using RDS if you have that luxury - we do 5 listboxes with rds
0
 
LVL 15

Expert Comment

by:robbert
ID: 2672374
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>
0
 
LVL 4

Expert Comment

by:ruperts
ID: 2672594
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>


0
 

Author Comment

by:mikeandbee
ID: 2679947
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
0
 
LVL 4

Expert Comment

by:ruperts
ID: 2679993
will e-mail you!
0
 

Expert Comment

by:gkostov
ID: 2680524
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.
0
 
LVL 4

Accepted Solution

by:
ruperts earned 100 total points
ID: 2680553
Mike,

That is sql stored procedure - you can just use regular SQL if you wish!

There are two version you can use!

1. Basic!

select distinct category,type ,item from my_table

2 . Deluxe! This gives an "All" option as well! - The reason for the '*' is to ensure that the All options are at the top!

create proc sp_dropdown as
select distinct '*All' as category,'*All' as type,'*All' as item  union
select distinct '*All' as category,'*All' as type,item       from my_table union
select distinct '*All' as category,type,item  from my_table union
select distinct '*All' as category,type,'*All' as item       from my_table union
select distinct category,'*All' as type,'*All' as item   from my_table union
select distinct category,'*All' as type,item  from my_table union
select distinct category,type,'*All' as item  from my_table union
select distinct category,type,item from my_table
order by category,type,item

You will have to change category to field one of your drop down, type to field two and item to field three.

I've since modified my code and it is now slightly more efficient, but it is all below!

<%
'open your connection
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 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 language="javascript">
<!--

var list1_array=new Array();
var list2_array=new Array(<%=total1+1%>);
var list3_array=new Array(<%=total1+1%>);

for(i=0;i<<%=total2+1%>;i++)
{
list2_array[i]=new Array();
list3_array[i]=new Array(<%=total2+1%>);

for(k=0; k<<%=total3+1%>;k++)
{
list3_array[i][k]=new Array();
}
}

<%=string1%>

<%=string2%>

<%=string3%>

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

function fillone(){
      var lb1=document.selectfields.selectcategory
      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.selectfields.selecttype
      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.selectfields.selectitem
      
      for(m=lb3.options.length-1;m>0;m--){
      lb3.options[m]=null
      }
      
      var x =document.selectfields.selectcategory.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>
0
 

Author Comment

by:mikeandbee
ID: 2692691
thanks a lot!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now