Solved

3 dependent drop down menu's + sql query

Posted on 2000-03-29
23
257 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
A multi-dimensional array. - You won't get it "for free".
0
 
LVL 11

Expert Comment

by:kmartin7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:ruperts
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Could be done using RDS if you have that luxury - we do 5 listboxes with rds
0
 
LVL 15

Expert Comment

by:robbert
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
will e-mail you!
0
 

Expert Comment

by:gkostov
Comment Utility
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
Comment Utility
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
Comment Utility
thanks a lot!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

7 Experts available now in Live!

Get 1:1 Help Now