Link to home
Start Free TrialLog in
Avatar of kevp75
kevp75Flag for United States of America

asked on

Multiple Dynamic Drop-Downs

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 %>
Avatar of nschafer
nschafer
Flag of United States of America image

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.
Avatar of kevp75

ASKER

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
Avatar of kevp75

ASKER

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
>> 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.

Avatar of kevp75

ASKER

>>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
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.
Avatar of kevp75

ASKER

:)

no prob.   Do you mind if I add your site in mine?
Not at all.
ASKER CERTIFIED SOLUTION
Avatar of nschafer
nschafer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kevp75

ASKER

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?
Avatar of kevp75

ASKER

yes and yes!

simply awesome!!!
I guess you answered your own questions.  

I'm glad I could help.

Neal.
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.
Avatar of kevp75

ASKER

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!
No Problem.
Avatar of kevp75

ASKER

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.
Avatar of kevp75

ASKER

i'm going to put that in a new question as you have answered this one, and well earned the grade and points
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.
Avatar of kevp75

ASKER

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....

https://www.experts-exchange.com/questions/21888438/Multiple-Dynamic-Drop-Downs-part-Duh.html