[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic Cascading Drop Downs?

Posted on 2006-05-09
27
Medium Priority
?
349 Views
Last Modified: 2008-03-17
I have a table with which I have all my categories.  It is structured as such:

CategoryTableName
catID INT(4) PK
parentID INT(4)
gCat NVARCHAR(255)

The data in it may be something like this:
0 0 Top
1 0 Games
2 1 Doom
3 2 Doom 1
4 2 Doom 2

I can make a dynamic drop down, problem is I don't know what to do for cascading it.  I need to make it I can have a drop down for the child categories based on what is selected in the first drop down, and down the line of children (so it needs to generate a drop down based on every parent drop down?).

I am assuming some sort of javascript is needed for it, but I have very limited knowledge of javascript.

Please help?
0
Comment
Question by:kevp75
  • 14
  • 8
  • 4
  • +1
27 Comments
 
LVL 12

Expert Comment

by:peterxlane
ID: 16643344
Just to make sure I understand...

YOu would start with a dropdown that says:  TOP and then there would be a second dropdown listing all the items that have parentid of 0 (matching top) and then based on what is selected next would determine which items are displayed in the next dropdown and right on down the line.  Is there a particular number of levels deep that you would want to go, or could it be unlimited?
0
 
LVL 25

Author Comment

by:kevp75
ID: 16643438
exactly....unlimited
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16643791
Definitely a tricky one.  I am thinking that doing a SQL statment like outlined here would allow you to potentially create this with a single recordset:  http://www.aspfaqs.com/webtech/sqlguru/q120899-1.shtml

I was working on something but I have to head out so maybe I will have something tomorrow if nobody else responds by then...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:MsShadow
ID: 16645688
Why not use a treecontrol instead of cascading dropdownlistboxes?
0
 
LVL 25

Author Comment

by:kevp75
ID: 16647733
treecontrol?

It's not for navigation.  It is for editting a link directories listing.  I need to be able to select the category(s) the link is in.
0
 
LVL 25

Author Comment

by:kevp75
ID: 16647737
sorry.   That plus it needs to be component less
0
 
LVL 12

Accepted Solution

by:
peterxlane earned 750 total points
ID: 16649438
Here is the beginning of an AJAX solution... It consists of two pages and it also uses the JavaScript library here:  http://www.ajaxtoolbox.com/

I reconstructed your database (in Access), and had the following structure:

tblCat:
catID INT(4) PK
parentID INT(4)
gCat NVARCHAR(255)

catID      parentID      gCat
1      0      Top
2      1      Games
3      1      Fun
4      2      Checkers
5      2      Chess
6      3      Hopscotch
7      3      Jump Rope


Main Page:

<% @Language = VBScript %>
<html>
<head>
<title>Multiple Selects</title>
<script type="text/javascript" src="AjaxRequest.js"></script>
<script language="JavaScript">
function buildChildSelect(formfield) {
      page = 'selectbuilder.asp?id='+ formfield.value;
      target = document.getElementById('otherSelects');      
      AjaxRequest.get(
        {
          'url': page
          ,'onSuccess':function(req){ target.innerHTML += req.responseText;}
          ,'onError':function(req){ target.innerHTML += 'There was an error processing your request';}
          ,'onTimeout':function(req){ target.innerHTML += 'Page Timed Out'; }
        }
      );      
}
</script>
<style>
SELECT
{
      FONT-SIZE: 14px;
      MARGIN-BOTTOM: 10px;
      DISPLAY: block;
}
</style>
</head>
<body>

<form method="post">
<table border="0" cellpadding="5" cellspacing="0" align="left">
      <tr>
            <td>
<%
strSQL = "SELECT M1.catID, M1.gCat FROM tblCat M1 INNER JOIN tblCat M2 ON M1.catID = M2.catID WHERE M1.CatID = 1"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("test.mdb")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open(connStr)
Set oRS = oConn.Execute(strSQL)
Response.Write "<select name=""initialselect"" onChange=""buildChildSelect(this);"">" & vbcrlf
Response.Write "<option value="""">Select One</option>" & vbcrlf
Do While Not oRS.EOF
      If CStr(intStoreID) = CStr(oRS(0)) Then
            Response.Write "<option value=""" & oRS(0) & """ selected>" & oRS(1) & "</option>" & vbcrlf
      Else
            Response.Write "<option value=""" & oRS(0) & """>" & oRS(1) & "</option>" & vbcrlf
      End If                  
      oRS.MoveNext
Loop
Response.Write "</select>" & vbcrlf
Set oRS = Nothing      
oConn.Close
Set oConn = Nothing      
%>                  

                  <div id="otherSelects"></div>
            </td>
      </tr>
      <tr>
            <td align="center">
                  <input type="submit" value="Submit" name="cmdSubmit" />
            </td>
      </tr>      
</table>
</form>

</body>
</html>




selectbuilder.asp:

<% @Language = VBScript %>
<%
intID = Request.QueryString("id")
strSQL = "SELECT M1.catID, M1.gCat, M1.parentID FROM tblCat M1 INNER JOIN tblCat M2 ON M1.catID = M2.catID WHERE M1.parentID = " & intID

If intID <> "" Then

      connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("test.mdb")
      Set oConn = Server.CreateObject("ADODB.Connection")
      oConn.Open(connStr)

      
      Set oRS = oConn.Execute(strSQL)
      If Not(oRS.EOF) Then
            Response.Write "<select name=""select" & intID & """ onChange=""buildChildSelect(this);"">" & vbcrlf
            Response.Write "<option value="""">Select One</option>" & vbcrlf
      End If      
      Do While Not oRS.EOF
            If CStr(intStoreID) = CStr(oRS(0)) Then
                  Response.Write "<option value=""" & oRS(0) & """ selected>" & oRS(1) & "</option>" & vbcrlf
            Else
                  Response.Write "<option value=""" & oRS(0) & """>" & oRS(1) & "</option>" & vbcrlf
            End If                  
            oRS.MoveNext
      Loop
      If Not(oRS.EOF) Then
            Response.Write "</select>" & vbcrlf
      End If                        
      Set oRS = Nothing

      oConn.Close
      Set oConn = Nothing            


End If
%>



The only problem with this at this point is it needs to remove the child dropdowns for the menu items if the user goes back and changes their selection on one of the previous dropdowns...  Give it a try and you will see what I mean...

0
 
LVL 25

Author Comment

by:kevp75
ID: 16651714
http://www.portalfanatic.com/temp/temp1.asp

I don't get anything in the drop-down.  I've changed what I needed to change as far as the fieldnames, and tablenames
0
 
LVL 25

Author Comment

by:kevp75
ID: 16651817
here's what I have:

temp1.asp:
<% @Language = VBScript %>
<!--#include virtual="/includes/inc/db.asp"-->
<html>
<head>
<title>Multiple Selects</title>
<script type="text/javascript" src="/includes/js/AjaxRequest.js"></script>
<script language="JavaScript">
function buildChildSelect(formfield) {
     page = 'temp2.asp.asp?id='+ formfield.value;
     target = document.getElementById('otherSelects');    
     AjaxRequest.get(
       {
         'url': page
         ,'onSuccess':function(req){ target.innerHTML += req.responseText;}
         ,'onError':function(req){ target.innerHTML += 'There was an error processing your request';}
         ,'onTimeout':function(req){ target.innerHTML += 'Page Timed Out'; }
       }
     );    
}
</script>
<style>
SELECT
{
     FONT-SIZE: 14px;
     MARGIN-BOTTOM: 10px;
     DISPLAY: block;
}
</style>
</head>
<body>

<form method="post">
<table border="0" cellpadding="5" cellspacing="0" align="left">
     <tr>
          <td>
<%
strSQL = "SELECT M1.gcID, M1.gCat FROM ModLinksCats M1 INNER JOIN ModLinksCats M2 ON M1.gcID = M2.gcID WHERE M1.gcID = 0"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open(inrsConn)'inrsConn is from db.asp
Set oRS = oConn.Execute(strSQL)
Response.Write "<select name=""initialselect"" onChange=""buildChildSelect(this);"">" & vbcrlf
Response.Write "<option value="""">Select One</option>" & vbcrlf
Do While Not oRS.EOF
     If CStr(intStoreID) = CStr(oRS(0)) Then
          Response.Write "<option value=""" & oRS(0) & """ selected>" & oRS(1) & "</option>" & vbcrlf
     Else
          Response.Write "<option value=""" & oRS(0) & """>" & oRS(1) & "</option>" & vbcrlf
     End If              
     oRS.MoveNext
Loop
Response.Write "</select>" & vbcrlf
Set oRS = Nothing    
oConn.Close
Set oConn = Nothing    
%>              

               <div id="otherSelects"></div>
          </td>
     </tr>
     <tr>
          <td align="center">
               <input type="submit" value="Submit" name="cmdSubmit" />
          </td>
     </tr>    
</table>
</form>

</body>
</html>


temp2.asp:
<% @Language = VBScript %>
<script type="text/javascript" src="/includes/js/AjaxRequest.js"></script>
<%
intID = Request.QueryString("id")
strSQL = "SELECT M1.gcID, M1.gCat, M1.parentID FROM ModLinksCats M1 INNER JOIN ModLinksCats M2 ON M1.gcID = M2.gcID WHERE M1.parentID = " & intID

If intID <> "" Then

     connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("test.mdb")
     Set oConn = Server.CreateObject("ADODB.Connection")
     oConn.Open(inrsConn)

     
     Set oRS = oConn.Execute(strSQL)
     If Not(oRS.EOF) Then
          Response.Write "<select name=""select" & intID & """ onChange=""buildChildSelect(this);"">" & vbcrlf
          Response.Write "<option value="""">Select One</option>" & vbcrlf
     End If    
     Do While Not oRS.EOF
          If CStr(intStoreID) = CStr(oRS(0)) Then
               Response.Write "<option value=""" & oRS(0) & """ selected>" & oRS(1) & "</option>" & vbcrlf
          Else
               Response.Write "<option value=""" & oRS(0) & """>" & oRS(1) & "</option>" & vbcrlf
          End If              
          oRS.MoveNext
     Loop
     If Not(oRS.EOF) Then
          Response.Write "</select>" & vbcrlf
     End If                    
     Set oRS = Nothing

     oConn.Close
     Set oConn = Nothing          


End If
%>
0
 
LVL 25

Author Comment

by:kevp75
ID: 16651838
sorry...in temp2.asp  add in the <!--#include virtual="/includes/inc/db.asp"-->
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16651884
I have it working here:  http://www.functionland.com/ee/kev/ajaxmultiselect.asp

You can download all of my code at:  http://www.functionland.com/ee/kev/ajaxmultiselect.zip

That has the JavaScript, ASP, and database in it...
0
 
LVL 2

Assisted Solution

by:aianrnoens
aianrnoens earned 750 total points
ID: 16652973
This is good.  The problem is that if you change the catagory box it outputs a new box instead of changing the first one.

This is how I would handle the problem
You can see a working example of this here <a href="http://www.cjpattoncenter/other/index.asp">Link To Example</a>

Now you will have 2 modify it to use your own database as I have very little knowledge of sql databeses.

This one uses an access database structured in the following way.
DatabaseName:  data.mdb
Table:  Catagories
Field:  ID - autonumber
Field:  Name - text

Table:  Items
Field:   ID - autonumber
Field:  CatID - Number
Field:  Name - text


First create the following ASP page
<html>
<head>
<script language="javascript">
function DynamicInclude(url)
{
      var req = new ActiveXObject("Microsoft.XMLHTTP")
      req.open("GET", url, false)
      req.send("")
      return req.responseText
}
function selectCat()
{
      var catselect = document.getElementById("catagory")
      var spanselect = document.getElementById("items")
      spanselect.innerHTML = DynamicInclude("data2.asp?id=" + catselect.value)
}
function initfirstbox()
{
      var firstbox = document.getElementByID("firstbox")
      firstbox.innerHTML = DynamicInclude("data1.asp")
}
</script>
</head>
<body onload="initfirstbox()">
<span id="firstbox">
</span><br /><br />
<span id="secondbox">
</span>
</body>
</html>

DATA1.asp
<%
dim strConn
dim oConn
dim oRS
set oConn = server.createobject("ADODB.Connection")
set oRS = server.createobject("ADODB.Recordset")
strConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("data.mdb") & ";"
oConn.open strConn
oRS.open "Catagories", oConn, 3, 3
oRS.MoveFirst
response.write "<select id='catagory' onchange='selectCat()'>"
do while not oRS.eof
response.write "<option value='" & oRS.Fields("ID") & "'>" & oRS.Fields("Name") & "</option>"
oRS.MoveNext
loop
response.write "</select>"
%>

Data2.asp
<%
dim intID
intID = cint(request.querystring("id"))
dim strConn
dim oConn
dim oRS
set oConn = server.createobject("ADODB.Connection")
set oRS = server.createobject("ADODB.Recordset")
strConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("data.mdb") & ";"
oConn.open strConn
oRS.open "Items", oConn,  3,  3
oRS.MoveFirst
response.write "<select id='items'>"
do while not oRS.EOF
   if intID = oRS.fields("CatID") then
       response.write "<option value='" & oRS.Fields("ID") & "'>" & oRS.Fields("Name") & "</option>"
   end if
oRS.MoveNext
loop
response.write "</select>"
%>
0
 
LVL 2

Expert Comment

by:aianrnoens
ID: 16652990
Sorry about the link.

Here is the one that works
www.cjpattoncenter.com/other/index.asp

You can download the code here
www.cjpattoncenter.com/other/dropdown.zip
0
 
LVL 25

Author Comment

by:kevp75
ID: 16653093
@aianrnoens seems to work pretty good, I will try it out in a few.

Only thing is, will I be able to do this with 1 table as the category table.  I do it like that to do a breadcrumb nav.  You can see it at http://www.portalfanatic.com/links/ or http://www.portalfanatic.com/gallery/
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16658655
aianrnoens works but it only will do two predefined dropdowns... You need to be able to handle an unlimited amount of dropdowns/categories.

I think the only thing that needs to be changed in mine is the JavaScript, so that it will replace all the child dropdowns below the one that is being selected...
0
 
LVL 25

Author Comment

by:kevp75
ID: 16658739
i know very little about javascript
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16659063
yeah, me too... but I am hoping to figure this out...

I will work on it in my spare time today and let you know if I figure it out...
0
 
LVL 2

Expert Comment

by:aianrnoens
ID: 16664378
I will work out a solution to use unlimited levels.  Give me some time.  I unfortunatly dont have much time to be on the computer.
0
 
LVL 25

Author Comment

by:kevp75
ID: 16664380
ok
0
 
LVL 2

Expert Comment

by:aianrnoens
ID: 16667009
The problem is illuding me.

I cannot seem to get it to work for more then one or two levels with my method.  Kep75 works great. Just need to remove the child things if changed, not sure how.
Anyway I will keep working on it.   If I come up with a solution I will post it.
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16668898
My brain is hurting from thinking about this question...

I have the following thoughts now:

1. Use one of the classic solutions available such as:  http://home.att.net/~codelibrary/HTML/dropdown_p.htm or http://webdeveloper.earthweb.com/repository/javascripts/2002/07/91311/cascadeDrops_simple.html  This would require a database query to determine which dropdowns to display initially and then you would dynamically create the JavaScript from the database.

2. Back to the treeview solution that was proposed by MsShadow... Technically, all the dropdowns are doing is "navigating", the more I think about it; the more I think something like this makes sense. Click on a link on the left side and it expands and displays the items you want to edit on the right. Until you got to the lowest level, you would simply be editing the directory names, but once you got to the lowest level, you would then be able to edit the links. From a usability standpoint, I would think this would be a far better solution.
0
 
LVL 25

Author Comment

by:kevp75
ID: 16668981
(2) not a bad idea peter.

how would I go about getting the form to recognize that I've expanded the categories(directories), when I need to pass the gcID?

(1) good examples too, however they seem limited.  How would it go about generating the next drop-down?  

This link will give you an idea what I am attempting to accomplish
http://www.portalfanatic.com/gallery/?pCat=35

towards the top of the page you will see the Navigation.  These are the categories, and they are all based on a parentID to the previous.  What I'm trying to accomplish is making the edit page for the pics themselves.  So I can move them around the categories.  But, as you can see in the link above, the child-parent relationship...
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16669858
I always try to make the admin area very similar to the application itself.  If I were building this, I would use the existing interface and check to see if the user is an admin on the page that actually displays the picture.  If they are an admin, then you could display a category dropdown that would allow you to move the picture to a different category.  It could also be displayed on the page that has the thumbnail as well.  Then, editing the categories would be just as intuitive as the interface is now.

0
 
LVL 25

Author Comment

by:kevp75
ID: 16669902
taht's how it is now.  But the drop-down for the category is populated with all the categories (parent & child)

What i'd like to do is have the first drop-down be just the top level, and the generated drop-downs be based on the previous drop-down
0
 
LVL 25

Author Comment

by:kevp75
ID: 16738149
i'm wondering if I should say f-it, and go with tree-view method.

the edit page pops up to another window, from there the 1st field would be category.  Have a text field named category, with a link next to it to select the category from another popup.  I can make it drill down through the categories easy enough, but how would I make it so once a category is selected as the intended category update, update the text field in the edit page?
0
 
LVL 25

Author Comment

by:kevp75
ID: 16807849
bueller, bueller, ......  bueller?
0
 
LVL 25

Author Comment

by:kevp75
ID: 16902038
may I ask you all to participate in a different question.  It is similar to this question, without the multiple drop-downs.

i've conned my client out of going with the multi-drop down solution, but now I am having a problem getting the new solutions correct........

please see the question in this link:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21885888.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

831 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