Solved

DropDown Box and SQL

Posted on 2003-11-18
7
399 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Technology Resume 7 69
Non-Resizable Pharagraph 2 19
html form layout 4 36
Blending HTML output from R Studio into a ASP.Net/C# project 4 20
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In this tutorial viewers will learn how to position items using CSS's three positioning types Create a new HTML document with an internal stylesheet.: Create another div in CSS and name it Absolute : Type "position:absolute;" and "top:10px; left:50p…
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…

733 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