Solved

Multiple Dynamic Drop-Downs

Posted on 2006-06-15
19
305 Views
Last Modified: 2012-06-22
I am sure this will be a doozy.

here's what I have:
http://test.apenloversparadise.com/untitled.asp

it is generated via the following table structure:

tableName
icID <- INT(4) identity
parentID <-INT(4)
invCat <-NVARCHAR(255)

i need to have multiple drop-downs, for the depths of the categories (it's a hierarchal category system)

so:
if the data in the table contains

1   0   Games
2   0   Music
3   1   Doom
4   1   Quake
5   3   Doom I
6   3   Doom II
7   5   Soundtrack

then I need to have:
1st drop-down containing:
<option value="1">Games</option>
<option value="2">Music</option>

if Games is selected, a second drop-down should appear:
<option value="3">Doom</option>
<option value="4">Quake</option>

if Doom is selected in the second one, then a 3rd needs to be displayed containing:
<option value="5">Doom I</option>
<option value="6">Doom II</option>

and so on down the list.  This may get pretty deep in the hierarchy, so it needs to allow for an unlimited depth, trick is once the 3 drop-downs are there, if I go back to the 1st and select Music, the process should start all over again generating the next drop-down, etc...

I am not oppose to an AJAX solution or straight javascript solution, however it would need to be cross-browser compatible

here is the code I currently have to generate the drop-down on the link above:
<script language="javascript" runat="server">
function jssort(a)
{
   return (new VBArray(a)).toArray().sort().join(String.fromCharCode(0));
}
</script>
<%
Sub write(strString)
      response.write(strString)
End Sub
inrsConn = "myConnectionString"
class invcat
   dim id, category, parentid, description
end class
class invcats
   dim cats
   dim sortindex()  'holds sorted key index
   sub class_initialize
        set cats = server.createobject("Scripting.Dictionary")
   end sub
   sub class_terminate
        set cats = nothing
   end sub
   sub BuildFromDB(connstr)
       LoadFromDB connstr
       SetLabels
       BuildSortIndex
   end sub
   sub BuildFromArray(array)
       LoadFromArray array
       SetLabels
       BuildSortIndex
   end sub
   sub LoadFromArray(a)
       dim i,n,line,cat
       for i = lbound(a) to ubound(a)
            line = split(a(i),"^")
            set cat = new invcat
            cat.id = line(0)
            cat.parentid = line(1)
            cat.category = line(2)
            cats.add cat.id, cat
       next
   end sub
  sub LoadFromDB(conn)
        dim rs, cat
        set rs = server.createobject("ADODB.Recordset")
        rs.open "select icID, parentID, invCat from invCat", conn
        do while not rs.eof
            set cat = new invcat
            cat.id = rs("icID")
            cat.parentid = rs("parentID")
            cat.category = rs("invCat")
            cats.add clng(cat.id), cat
            rs.movenext
        loop
        rs.close
        set rs = nothing
   end sub
   sub SetLabels
      dim i
      for each i in cats.keys
          cats(i).description = mid(BuildLabel(i),4)
      next
   end sub
   function BuildLabel(id)  
       if id = 0 then
          BuildLabel = ""
          exit function
       else
          dim curcat
          set curcat = cats(id)
          BuildLabel = BuildLabel(curcat.parentid) & " - " & curcat.category
       end if
   end function
   sub BuildSortIndex
       dim key,descripts,desc
       set descripts = server.createobject("Scripting.Dictionary")
       for each key in cats.keys
           desc = cats(key).description
           if descripts.exists(desc) then
              descripts(desc) = descripts(desc) & "|" & key
           else
              descripts(desc) = key
           end if
       next
       dim sorted
       sorted = split(jssort(descripts.keys), chr(0))
       dim i,n,keys,index
       index = 0
       redim sortindex(cats.count - 1)
       for i = lbound(sorted) to ubound(sorted)
           keys = split(descripts(sorted(i)), "|")
           for n = lbound(keys) to ubound(keys)
              sortindex(index) = keys(n)
              index = index + 1
           next
       next
   end sub
  function SelectBox
        dim i,out,cat
        out = "<select name=""cat"">" & vbcrlf
        for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
                out = out & "<option value=""" & cat.id & """>" & cat.description & vbcrlf
        end if
        next
        out = out & "</select>" & vbcrlf
        SelectBox = out
   end function
end class
dim myCats
set myCats = new invcats
myCats.BuildFromDB(inrsConn)
%>
<br>
<br>
New Drop-Down<br>
<%= myCats.SelectBox %>
0
Comment
Question by:kevp75
  • 10
  • 9
19 Comments
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
kevp75,  

I have several examples of multiple Dynamic drop-down's using javascript, AJAX, and straight asp at my site: www.applicationgroup.com/tutorials/asp

I know that Fritz-the-blank also has one on his site : http://www.fairfieldconsulting.com/asp_multipleSelects.asp

I haven't looked closely at the code you use to show the drop-down boxes, but my question for you is, looking at the sample data you posted how do you link the categories.  I see how you have them broken out to show in seperate lists, but from a programming perspective how do I know that when the option selected is games that I next want to display the drop-down with Doom and Quake?  It would appear that you need a field that links them together.

1   0   1  Games
2   0   5  Music
3   1   3  Doom
4   1   8  Quake
5   3   9  Doom I
6   3   9  Doom II
7   5  10 Soundtrack

This extra column allows me to say that when option 1 is picked I want to show a drop-down with the options in group 1 (in this case Doom and Quake) and if the click on Doom then I want to open a drop-down with the options in group 3 (Doom I and Doom II) I added some fake numbers for Quake, Doom I, Doom II and Soundtrack since I couldn't really see where you would go from there.  But hopefully you get the idea.

Hope this helps,

Neal.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
that's what the second column does.

column 2 relates to the value of column 1, so if it's:
1   0   Games
2   0   Music
3   1   Doom
4   1   Quake

quake and doom are both related to Games via the 2nd column


the extra column you propose does almost the exact same thing as I am already doing, except it seems more of a cross relationship, where Doom I may be related to Music or Doom, however what I have works for how I need it to0...   please have a look at http://www.portalfanatic.com/gallery/?pCat=35

the NAVIGATION is pulled from this one table with a SP, and I can add/edit/delete at any level

good articles on both sites, however it seems limited as you have to code all the drop-downs, this needs to be dynamically generated as I do not know how deep the categories will go
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
fritz's article here:http://www.fairfieldconsulting.com/js_multipleSelects.asp

mentions that it can be done by passing 2 parameters, however I am a complete noob when it comes to javascript, so I wouldn't even know where to begin
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
>> that's what the second column does.

OK, I guess I was thrown off because in your example it would have made Soundtrack a subcategory of DOOM1 and I wasn't expecting that.  No problem though.

So if I understand correctly, you do not want to pre-define the drop downs, they will simply be added as need be and be filled in with the appropriate data.  If this is correct I will say that AJAX is definitely the way to go.  Javascript works well, but a straight javascript process would require that all of the possible options be loaded into an array before the page loads.  It sounds like you may have quite a bit of data and that would affect the page loading time.

Let me know if this is what you are after and I'll be happy to put something together that will at least point you down the right path.

--- I haven't looked at fritz's article in a while so I'll have to look at it again to see what you mean.  

Neal.

0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
>>OK, I guess I was thrown off because in your example it would have made Soundtrack a subcategory of DOOM1 and I wasn't expecting that.  No problem though.   -----suprisingly enough, I actually know someone with the cd  LOL

>>So if I understand correctly, you do not want to pre-define the drop downs, ------correct

i currently don't have a lot of data, however, I will within the next few weeks (at least for the category system, I'll be importing the entire DMOZ category dump, last time I tried it took 4 days, and had over 6million records, just in the categories....), so it will definatly have a lot of data

fritz has some good reads, you too  :)...I gotta start finding time to do stuff like that, maybe I'll get more visitors  ;-)

I appreciate it
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
OK, I'll start work on something for you.  Thanks for the comments on my samples.  Sorry if some of them aren't working right now.  I'm hanging around the office as a major software update is being installed so my SQL server is up and down at the moment so the examples may be a little sporadic in how the work tonight.  

Neal.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
:)

no prob.   Do you mind if I add your site in mine?
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
Not at all.
0
 
LVL 19

Accepted Solution

by:
nschafer earned 500 total points
Comment Utility
OK, here's something that seems to work.  It's a bit rough around the edges, but it seems to work quickly and do exactly what you were asking for.  It uses AJAX so there are two pages.

You can see this working here:  http://www.applicationgroup.com/ee_examples/Q_21888212-1.asp

The main page is here:

q_21888212-1.asp
-------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Untitled Page</title>
    <script type="text/javascript">
    var currMenuID = 0;
    function getHTTPObject() {
      var req = false;
      // branch for native XMLHttpRequest object
      if(window.XMLHttpRequest) {
        try { req = new XMLHttpRequest(); }
        catch(e) {req = false;}
        // branch for IE/Windows ActiveX version
      }
      else if(window.ActiveXObject) {
        try { req = new ActiveXObject("Msxml2.XMLHTTP"); }
        catch(e) {
          try {req = new ActiveXObject("Microsoft.XMLHTTP");}
          catch(e) {req = false;}
        }
      }
      return req;
    }
    function getMenu(itemID,menuID) {
      currMenuID +=1;
      document.getElementById("selValue").value = itemID;
      oHttp = getHTTPObject();
      if (oHttp.readyState != 0) {oHttp.abort();}
      // get rid of select boxes below the changed select box if
      // the user is changing an option above the current level
      if (menuID > 0 && menuID < currMenuID - 1) {
        for (i=menuID+1;i<currMenuID-1;i++) {
          document.getElementById('sel'+ i).outerHTML = '';
        }
        currMenuID = menuID + 1
      }

      // URL to the file that will access the data
      var now = new Date()  // We'll use the valueOf fuction of the date
                            // to ensure that we don't get a cached
                            // version of the page
      var sURL = "q_21888212-2.asp?id=" + itemID + "&x=" + now.valueOf();
      oHttp.open("get", sURL , true);
       oHttp.onreadystatechange = function () {
        if (oHttp.readyState == 4) {
          var options = oHttp.responseText.split("~");
          if (options.length > 0 && options[0] > '') {
            var sm = document.getElementById("selMenu");
            sm.innerHTML += "<select name='sel" + currMenuID + "' id='sel" + currMenuID + "' onchange='getMenu(this.options[this.selectedIndex].value," + currMenuID + ");' style='display:block;'></select>"
            var sel_to_fill = document.getElementById('sel' + currMenuID);
            sel_to_fill.options[0] = new Option("Choose One:","");
            for (i=0;i<options.length;i++) {
              aOptions = options[i].split("`")
              sel_to_fill.options[i+1] = new Option(aOptions[1],aOptions[0]);
            }
          }
        }
      }
      oHttp.send(null);
    }

    function frmSubmit() {
      alert(document.getElementById("selValue").value);
    }
    </script>
</head>
<body onload="getMenu(0,0);">
<form name="form1" method="post" >
<input type="hidden" name="selValue" id="selValue" />
<div id="selMenu">
Please Select the item you are interested in:
<input type="button" value="Submit" onclick="frmSubmit();" />
<br />
</div>
</form>
</body>
</html>

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

The Second page is here.  This is the one that actually does the database calls.

q_21888212-2.asp
-------------------------------------------------------------
<%
dim nID
nID = request.querystring("id")
if nID = "" then
  response.End
end if
if isnull(nID) then
  response.End
end if
if not isnumeric(nID) then
  response.End
end if
dim conn,connStr,datapath,rs,sql,x
set conn = server.createobject("ADODB.Connection")
datapath = server.mappath("./data") & "\db2.mdb"
ConnStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & datapath
conn.Open(connStr)
sql = "Select * from invCat where parentID = " & nID
set rs = conn.Execute(sql)
x = 0
do until rs.eof
  if x > 0 then
    response.Write("~")
  end if
  response.Write(rs("icID") & "`" & rs("invCat"))
  rs.movenext
  x = x + 1
loop
rs.close
set rs=nothing
conn.Close
set conn = nothing
%>    
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Hope this gets you started.  Feel free to send back if you have problems or need explinations.

Neal.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 25

Author Comment

by:kevp75
Comment Utility
NICE!!!

god I wish I had more points to give ya!
http://www.portalfanatic.com/temp/temp1.asp    (games, guild wars, guild halls, etc...)

2 questions for you though  (they may be so obvious that I see them before you can get back to me.)

when I use this to submit the form, is it selValue that I will be requesting?
is selValue going to be the value I get even if I don't select something from the next drop-down?
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
yes and yes!

simply awesome!!!
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
I guess you answered your own questions.  

I'm glad I could help.

Neal.
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
I think I'll add this one to my sample code page :)  

You'll want to put in some validation though.  If the user changes the value of a drop-down and then changes it back to Choose One, then the value will reset to blank.

Just something to keep in mind.

Neal.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
absolutly awesome man, you couldn't have been a bigger help...this thing's been driving me bonkers for a month.  I only recently learned what AJAX is, let alone what it could do..LOL

(looks like I have a ton of reading to do)

thanks again!
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
No Problem.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
ahh...I see what you mean.  I wonder if temporarily storing the previous ID in a session variable or cookie, and then updating the session or cookie on anything other than a Choose One: would cure that?  I'll experiment a bit.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
i'm going to put that in a new question as you have answered this one, and well earned the grade and points
0
 
LVL 19

Expert Comment

by:nschafer
Comment Utility
I would simply check to see if the value was blank before submitting the form and if so alert the user to make a choice.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
i will do that too.

I have opened another question though about the issue where if 4 drop-downs are displayed then you change the 1st, the others don't disappear....

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21888438.html
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now