DropDown Box and SQL

OK...Here goes.

I have three drop down boxes that I want to populate with data from my db.  This is no big deal,
But, I want to filter the results on each dropdown box.  For example, Row, Bay and Shelf (all in same table) are the columns I want
to display.  I created a dropdown box for each one.  They automatically display the contents of the entire db.  
When a user picks a specific Row, I want the Bay and Shelf information updated in their dropdown boxes.  Same thing if a user picks Bay.

Row      Bay      Shelf
1      A      1
1      A      2
1      B      1
2      C      3
2      C      3
2      C      3
3      D      4
3      D      4
3      D      2

Here is a example of the data.  When a user picks Row "1",  Bay A & B and shelves 1 & 2 should only be displayed.


Any Ideas?

Thanks.
sw
swilli6926Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jarasaCommented:
HI
You want to do that with or without calling the DB again?
Javier
0
jjerome00Commented:

I've done this a few times.  There are 2 ways I know: 1, after each selection of a dropdown, submit the form to call the page again (with the selection as one of the request parameters).  This will work, but if you don't have too much data, there is a much more elegant solution:

Use a combination of server-side script (ASP, JSP, PHP, etc) and javascript.  Create a JavaScript array with the results from the database (loop through the resulting recordset) at the beginning of the page.

For the dropdowns, use the onChange event to call JavaScript functions to repopulate all your dropdowns using the array you created at the top of the file.

It sounds hard, but it really isn't too bad.  I could go into it all, but I don't have my example right in front of me.  Feel free to contact me if your still having this issue.

0
nishikanthCommented:
I use ASP for using a triple linked drop down list.

I stored the whole category into an array and converted this into a javascroipt array.

On change event of this drop downlist i loop through the drop down list and fill the other .


SAmple Database table design of my sample.

Pls recreate this and check

CatID      CatName      ParentId      
1      Antiques      0      
2      Art      0      
8      Fiction      1      
10      Children      1      
11      Classics      1      
17      Romance      2      
18      Science      2      
84      Poster      8      
85      Sports       8      
229      Sports       17      
230      TV Shows      17      
231      Others      18      

Click Antiques ->     Fiction, Children & Classics will be displayed
Select Fiction  -> Poster & sports will be displayed


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

The validations are based on my table design , you use  opwn database table design and change the way validations take place

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


<%@ Language=VBScript %>

<HTML>
<HEAD>
</HEAD>
<BODY>


<%
            set rscat = server.CreateObject("adodb.recordset")    
            rscat.Open "select * from category",conn,1,3,1   '''''Pls load your entire table into this

            dim categoryarray

            if not rscat.EOF then
                  categoryarray = rscat.GetRows()
            end if


Call ConvertToJSArray2D(categoryarray,"categoryarrayJS")


%>

<form name = "test">

      <SELECT id=cat name=cat onchange = FillListSub()  class="selectbox">
            <OPTION value=0>Select</OPTION>
            <% for i = 0 to UBound(categoryarray,2)%>
                  <OPTION value = <%=categoryarray(0,i)%>><%=categoryarray(1,i)%></OPTION>
            <%next%>
      </SELECT>

      <SELECT id=subcat name=subcat onchange = FillListSubSub() disabled  class="selectbox">
            <OPTION value=0>Select
            </OPTION>
      </SELECT>

      <SELECT id=subsubcat name=subsubcat) disabled  class="selectbox">
            <OPTION value=0>Select
            </OPTION>
      </SELECT>
      <INPUT type="text" id=text1 name=text1>
      <INPUT type="submit" value="Submit" id=submit1 name=submit1 onsubmit = getcat1()>
      
</form>

<script language = javascript>


      function FillListSub()
      {
            i = 0;
            document.test.subcat.disabled = false;
            document.test.subcat.length=0;    
            document.test.subsubcat.disabled = true;
            document.test.subsubcat.length=0;
            document.test.subsubcat.options[0]=new Option("Select","Select");
            document.test.subcat.options[0]=new Option("Select","Select");
            if ((document.test.cat.selectedIndex)==0 ){
                  document.test.subcat.disabled = true;
                  return;
            }
             
            for ( ctr=0;ctr<<%=ubound(categoryarray,2)%>;ctr++)
            {
            if (categoryarrayJS[2][ctr] == document.test.cat.value )
                  {
                  i = i+1;
                  document.test.subcat.options[i] = new Option(categoryarrayJS[1] [ctr], categoryarrayJS[0][ctr]);
                  }

            }

            if ((document.test.cat.selectedIndex)==0 || document.test.subcat.length == 1)
               {
                 document.test.subcat.disabled = true;
                 document.test.subcat.length=0;  
                 document.test.subcat.options[0]=new Option("Select","Select");
                 document.test.subsubcat.disabled = true;
                 document.test.subsubcat.length=0;    
                 document.test.subsubcat.options[0]=new Option("Select","Select");
               }
      }

      function FillListSubSub()
      {
            i = 0;
            document.test.subsubcat.disabled = false;
            document.test.subsubcat.length=0;    
            document.test.subsubcat.options[0]=new Option("Select");

            for ( ctr=0;ctr<<%=ubound(categoryarray,2)%>;ctr++)
            {
                  if (categoryarrayJS[2][ctr] == document.test.subcat.value)
                  {
                        i = i+1;
                        document.test.subsubcat.options[i] = new Option(categoryarrayJS[1] [ctr], categoryarrayJS[0][ctr]);
                  }
            }

            if(document.test.subcat.selectedIndex == 0 || document.test.subsubcat.length == 1)
            {
                  document.test.subsubcat.disabled = true;
                  document.test.subsubcat.length=0;    
                  document.test.subsubcat.options[0]=new Option("Select");
            }
      }
      
      
</script>


<%

Function ConvertToJSArray2D(VBArray , ArrayName)
      Dim vb2jsRow, vb2jsCol , vb2jsStr, vb2jsi, vb2jsj
      vb2jsRow = Ubound(VBArray,1)
      vb2jsCol = Ubound(VBArray,2)
      %>
      <SCRIPT LANGUAGE = 'JAVASCRIPT' >
            var vb2jsi,vb2jsj
            <%=ArrayName%> = new Array(<%=vb2jsRow+1%>);
            for (vb2jsi=0; vb2jsi < <%=vb2jsRow+1%>; vb2jsi++)
            {
                <%=ArrayName%>[vb2jsi] = new Array(<%=vb2jsCol+1%>)
            for (vb2jsj=0; vb2jsj < <%=vb2jsCol+1%>; vb2jsj++) <%=ArrayName%>[vb2jsi][vb2jsj] = " "
            }
      </SCRIPT>
      <%
      Response.Write("<SCR"&"IPT LANGUAGE = 'JAVASCRIPT' >"&chr(13))
      for vb2jsi=0 to vb2jsRow
            for vb2jsj=0 to vb2jsCol
                vb2jsstr = "VBArray("&vb2jsi&","&vb2jsj&")"
            %>    
                <%=ArrayName%>[<%=vb2jsi%>][<%=vb2jsj%>] = "<%=trim(eval(vb2jsstr))%>"
            <%
            Next
      Next
      Response.Write("</SCR"&"IPT>")
End Function
%>

</BODY>
</HTML>
--------------------
0
doctornick0Regional Director of RevenueCommented:
sw --

You can use some snazzy JavaScript and ASP combinations.  See this wonderful article:  http://www.atgconsulting.com/triplelist.asp.  Also, which DB are you using?  If you're using SQL Server or IBM DB2 UDB version 8.x, you can generate the output as XML, which really works well (see the link on the ATG Consulting page to the Object-Oriented linked list boxes).  Remember to feed your data into some well thought-out arrays, otherwise you'll be querying the database too much, which will slow down your output to the point of obsolescence.

-- doctornick0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.