Solved

Multidimensional Array Drop-Down?

Posted on 2006-07-19
21
281 Views
Last Modified: 2012-06-21
My issue is, I have set up a hierarchal category system based on 1 table in a SQL db, structured like this:

tableName
gcID <-int
parentID <-int (related to gcID)
gCat <-nvarchar

I have code to build myself a drop-down list so I can move stuff from one category to another.....problem is I link to the edit page with a querystring that is the categories ID#.  Q is how can I get the drop-down to auto select the correct category....

code follows:
this is an include file:
<script language="javascript" runat="server">
function jssort(a)
{
   return (new VBArray(a)).toArray().sort().join(String.fromCharCode(0));
}
</script>
<%
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 gcID, parentID, gCat from fileCats", conn
        do while not rs.eof
            set cat = new invcat
            cat.id = rs("gcID")
            cat.parentid = rs("parentID")
            cat.category = rs("gCat")
            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=""gcID"">" & vbcrlf
        for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
                  'if cat.id = pCat then
                  '      ll=" selected"
                  'end if

                out = out & "<option value=""" & cat.id & """"&ll&">" & cat.description &"</option>" & vbcrlf
        end if
        next
        out = out & "</select>" & vbcrlf
        SelectBox = out
   end function
end class
%>



and the drop-down is:
<%
                        dim myCats
                        set myCats = new invcats
                              myCats.BuildFromDB(inrsConn)
                              write(myCats.SelectBox)
                        set myCats = nothing
                        %>
0
Comment
Question by:kevp75
  • 14
  • 3
  • 3
21 Comments
 
LVL 25

Author Comment

by:kevp75
ID: 17139837
I guess it'd probably be a good idea to show what this does and the data in the table....so click here and you'll see.
http://www.festivalcos.com/filerepo/temp.asp
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17142603
Can't you just do this in the function SelectBox:

if cat.id = Request.Form("gcID") then
     ll=" selected"
end if

If that isn't it (which I doubt it is) you may need to explain the work flow.
0
 
LVL 25

Author Comment

by:kevp75
ID: 17142847
i've tried with:
if cat.id = Request.Form("pCat") then
     ll=" selected"
end if

if cat.id = Request.querystring("pCat") then
     ll=" selected"
end if

neither have worked.  I'm not sure what the work flow is, someone here helped me do it.

if you look here http://www.festivalcos.com/filerepo/temp.asp?pCat=10, i've displayed the value of the querystring and the categories id, however when I add in the if statement nothing happens (we're looking to get 10-10-Documents - Test - Test 2 - Test 1 selected for this particular querystring)
0
 
LVL 25

Author Comment

by:kevp75
ID: 17142864
here's the updated code:
<script language="javascript" runat="server">
function jssort(a)
{
   return (new VBArray(a)).toArray().sort().join(String.fromCharCode(0));
}
</script>
<%
pCat = request.querystring("pCat")
if pCat = 0 OR ISNULL(pCat) OR pCat = "" then
      pCat = 0
else
      pCat = cint(pCat)
end if
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 gcID, parentID, gCat from fileCats", conn
        do while not rs.eof
            set cat = new invcat
            cat.id = rs("gcID")
            cat.parentid = rs("parentID")
            cat.category = rs("gCat")
            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=""gcID"">" & vbcrlf
        for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
                  if cat.id = pCat then
                        ll = " selected"
                  end if
                out = out & "<option value=""" & cat.id & """"&ll&">"& pCat &"-" & cat.id &"-"& cat.description &"</option>" & vbcrlf
        end if
        next
        out = out & "</select>" & vbcrlf
        SelectBox = out
   end function
end class
%>


same method of using it...
0
 
LVL 25

Author Comment

by:kevp75
ID: 17169875
nothing's changed, however now I get an Object Required Error on this line of the class:
set curcat = cats(id)
0
 
LVL 25

Author Comment

by:kevp75
ID: 17187088
anyone, anyone......bueller, bueller?
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17194837
The object required error is probably because your SQL statement below is EOF, so it won't create the cat object and you can't create a new curcat object if cats is not already an object.

        rs.open "select gcID, parentID, gCat from fileCats", conn
        do while not rs.eof
            set cat = new invcat

So depending on what is in the pCat querystring it should pre-select a particular option in the Select box?  Is that what you are trying to do?
0
 
LVL 25

Author Comment

by:kevp75
ID: 17195372
correct.  not too sure why it would be an eof error though, because there are records in the table....
0
 
LVL 25

Author Comment

by:kevp75
ID: 17215796
i wonder if I add the " selected" to the dictionary object, but have it added on the id
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17217557
What did you end up doing?  I assume you got it working now?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Author Comment

by:kevp75
ID: 17217586
i have not got it working at all...

please see http://www.festivalcos.com/filerepo/temp.asp

and since there hasn't been a solution, I wanted to clean up my asked questions...
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 17217960
Here you go...  the problem is with the types.  clng(3) <> cstr(3)... force the types and your code should work fine:

Here's a modification to the class:

  function SelectBoxWithSelection(selection)
        dim i,out,cat
        out = "<select name=""gcID"">" & vbcrlf
        for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
               if cstr(cat.id) = cstr(selection) then
                    ll=" selected"
               end if

                out = out & "<option value=""" & cat.id & """"&ll&">" & cat.description &"</option>" & vbcrlf
        end if
        next
        out = out & "</select>" & vbcrlf
        SelectBoxWithSelection = out

  end function

  function SelectBox
      SelectBox = SelectBoxWithSelection("")
  end function

And then calling it:

                    dim myCats
                    set myCats = new invcats
                         myCats.BuildFromDB(inrsConn)
                         write(myCats.SelectBoxWithSelection(request("pCat"))
                    set myCats = nothing

0
 
LVL 25

Author Comment

by:kevp75
ID: 17218897
sorry clockwatcher.

have a look at the page:http://www.festivalcos.com/filerepo/temp.asp

clicking the link will initiate the querystring pCat=16

which should 'select'    16CADImages - AutoCAD
0
 
LVL 25

Author Comment

by:kevp75
ID: 17218910
(
p.s. to show the 16CADImages...etc, all I did was change this line:
out = out & "<option value=""" & cat.id & """"&ll&">" & cat.description &"</option>" & vbcrlf

to this:
out = out & "<option value=""" & cat.id & """"&ll&">" & cat.id & cat.description &"</option>" & vbcrlf
)
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 17220118
Paste your entire code... forcing the type match should fix the problem.  It does in my tests.
0
 
LVL 25

Author Comment

by:kevp75
ID: 17220474
the code is exactly how you had fisrt showed me, however I added in the value for pCat and the value of cat.id into what is displayed in the drop-down:

special.asp:
<script language="javascript" runat="server">
function jssort(a)
{
   return (new VBArray(a)).toArray().sort().join(String.fromCharCode(0));
}
</script>
<%
class invcat
   dim id, category, parentid, description
end class
class invcats
   dim cats
   dim sortindex()
   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 gcID, parentID, gCat from fileCats", conn
        do while not rs.eof
            set cat = new invcat
            cat.id = rs("gcID")
            cat.parentid = rs("parentID")
            cat.category = rs("gCat")
            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 SelectBoxWithSelection(selection)
        dim i,out,cat
        out = "<select name=""gcID"">" & vbcrlf
        for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
               if cint(cat.id) = cint(selection) then
                           lb = "yes"
                    ll=" selected"
               end if

                out = out & "<option value=""" & cat.id & """"&ll&">" & selection &"-"& cat.id & cat.description &"</option>" & vbcrlf
        end if
        next
        out = out & "</select>" & vbcrlf
        SelectBoxWithSelection = out

  end function

  function SelectBox
     SelectBox = SelectBoxWithSelection("")
  end function
end class
%>

temp.asp:
<!--#include virtual="/filerepo/inc/special.asp"-->
<p>If you <a href="/filerepo/temp.asp?pCat=16">click here</a>, you will see that it does not select a default..</p>
<p>this link selects the record that should be id of 16, as you can see in the select, I am displaying the value of the querystring (pCat) and the value of cat.id, and if they are equal, than that particular record should be selected, but you can see it is not.</p>
<%
                        dim myCats
                        set myCats = new invcats
                              myCats.BuildFromDB(inrsConn)
                              write(myCats.SelectBoxWithSelection(request("pCat")))
                        set myCats = nothing
                        %>
0
 
LVL 25

Author Comment

by:kevp75
ID: 17220486
p.s.   the lb in there was simply to show in the drop-down whether pCat was equal to cat.id, and serves no real purpose....
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 17220507
You need to reset your variable.  It's getting set and staying set.

  for i = lbound(sortindex) to ubound(sortindex)
           if cats.exists(clng(sortindex(i))) then
           set cat = cats(clng(sortindex(i)))
               ll = ""
               lb = ""
               if cint(cat.id) = cint(selection) then
                       lb = "yes"
                    ll=" selected"
               end if

                out = out & "<option value=""" & cat.id & """"&ll&">" & selection &"-"& cat.id & cat.description &"</option>" & vbcrlf
        end if
0
 
LVL 25

Author Comment

by:kevp75
ID: 17220550
DOH!

thanks a ton clock!
0
 
LVL 25

Author Comment

by:kevp75
ID: 17220588
one more time.....if anyone is interested....
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21938504.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
API Location, separating out information 10 46
Help with stored procedure 30 59
two submit buttons one form 15 75
Recordset containing single quotes 8 24
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

20 Experts available now in Live!

Get 1:1 Help Now