Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DropDown Box and SQL

Posted on 2003-11-18
7
393 Views
Last Modified: 2010-04-09
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
0
Comment
Question by:swilli6926
7 Comments
 
LVL 17

Accepted Solution

by:
dorward earned 32 total points
ID: 9771249
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9779576
HI
You want to do that with or without calling the DB again?
Javier
0
 
LVL 2

Assisted Solution

by:jjerome00
jjerome00 earned 31 total points
ID: 9791933

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
 
LVL 8

Assisted Solution

by:nishikanth
nishikanth earned 31 total points
ID: 9910645
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
 
LVL 1

Assisted Solution

by:doctornick0
doctornick0 earned 31 total points
ID: 10034109
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Someone recently asked me about how to display a progress indicator on a page while an iframe is loading. And I remember when I first came across this myself. It was a bit tricky to get my head around, but really, it's very simple. The most impor…
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
In this tutorial viewers will learn how to style transparent/translucent elements using alpha transparency in CSS Start with a normal styled element, such as a div.: Define its "background-color" property as "rgba (255, 255, 255, .5): The numbers in…
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …

837 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