[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

DropDown Box and SQL

Posted on 2003-11-18
7
Medium Priority
?
412 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
[X]
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
7 Comments
 
LVL 17

Accepted Solution

by:
dorward earned 128 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 124 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 124 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 124 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
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…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

650 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