Displaying hierarchical data for selection

Broodmdh · August 26th, 2008, 09:11 AM

I have some data that I need to display to the user, and I also need to be able to allow the user to select certain portions of the data. My db table is layed out as follows:

ID int
Title varchar
ParentID int (which just refers to an ID in this same table)

I was thinking about using a treeview (with a checkbox for each branch) to display the data, and I've got that working up to a point, but I'm not sure how to proceed from there. I am having a tough time figuring out how to update the database records when the user makes changes. Can I loop through all of the checkboxes on the page and determine whether they are checked or not? Can anybody make some suggestions, or perhaps point me in a better direction? Thanks for the help.

Old Pedant · August 26th, 2008, 12:50 PM

I'm guessing that, in order to even display your tree, you have to do recordsets within recordsets.

But maybe not.

Show us how you build the display tree, first, okay?

**************

Yes, JS code in the browser could loop through all checkboxes. To what end??

Broodmdh · August 26th, 2008, 01:06 PM

Ya, I am using multiple recordsets. What I've got works, but feels unnecessarily messy and complicated. I managed to loop through the request.form items and save the checked values to the db (I'll post that code at the bottom), but I would like to find a more refined, simpler solution if one exists.

Code:

ID=clng(request("ID"))
   sql="Select * From " & SQLDatabase & "Specializations Where ParentID IS NULL"
   set rs1 = Server.CreateObject("ADODB.Recordset")
   rs1.open sql,db,1,3
   if rs1.recordcount>0 then
      do while not rs1.eof
      ID=clng(rs1("ID"))
      Title=trim(rs1("Title"))
      if Ubound(specArray)>0 then
         for j = 0 to Ubound(specArray)
            if clng(specArray(j)) = ChildID then
           pCheckValue = "checked"
           exit for
        end if
     next
      end if
      sql2 = "Select * From " & SQLDatabase & "Specializations Where ParentID=" & ID
      set rs2 = Server.CreateObject("ADODB.Recordset")
      rs2.open sql2,db,1,3
      if rs2.recordcount>0 then %>
         <div style="margin-left:20px; margin-bottom:5px;"><img src="images/plus.gif" id="node<%=ID%>" alt="" onclick="SwitchImage(this.id);ShowDiv(this.id);" style="padding-bottom:5px;">&nbsp;&nbsp;<%=Title%><br></div>
         <div id="div<%=ID%>" style="margin-left:50px;display:none;">
     <%do while not rs2.eof
     ChildID=clng(rs2("ID"))
     ChildTitle=trim(rs2("Title"))
     if Ubound(specArray)>0 then
        for j = 0 to Ubound(specArray)
          if clng(specArray(j)) = ChildID then
             cCheckValue = "checked"
         exit for
          end if
        next
     end if
    %>
    <input type="checkbox" name="<%=ChildID%>" id="<%=ChildID%>" <%=cCheckValue%>>&nbsp;<%=ChildTitle%><br>
    <%
        rs2.movenext
    cCheckValue=""
     loop
%>
</div>
<% else %>
   <div style="margin-left:20px; margin-bottom:5px;"><input type="checkbox" name="<%=ID%>" id="<%=ID%>" <%=pCheckValue%>>&nbsp;<%=Title%><br></div>
<% end if
rs2.close
pCheckValue=""
rs1.movenext
loop

And to save the checked boxes:

Code:

for each item in request.form
   key = item
   value = request.form(item)
   if value="on" then
      sql="Select * From " & SQLDatabase & "AgentsSpecializations Where AgentID=0 And SpecializationID=0"
      rs.open sql,db,1,3
      rs.addnew
      rs("AgentID")=AgentID
      rs("SpecializationID")=key
      rs.update
      rs.close        
   end if
next

Thanks for the help.

Old Pedant · August 26th, 2008, 02:23 PM

Yeah, this is a rotten way to do it. Performance will suck, to say the least.

My own preference is to simply pass all the data to the browser and then use JavaScript code to build up the tree.

But the alternative is to convert your recordset to an array using GetRows and then do the recursion by rescanning the array. It's still not great, but it's better than making a DB call for each item.

********

Not quite sure what your problem is re handling the checked check boxes. Your code is horribly inefficient and would be unsafe if there were any other form fields that conflicted, but it looks like it should work.

Broodmdh · August 26th, 2008, 03:32 PM

So Javascript would be the best way to handle this? How would you pass the data to the browser? I would have to make a call to the db that pulled back all of the top-level, and all of their associated sub-level, items in one go? I'm not sure how to do that, unless I was using temp-tables. The checkboxes are to allow the user to select any of the top-level or sub-level items. I also have to be able to indicate (via the same checkboxes) any items that they had chosen previously. As I said, I've got it working, but I would rather do it properly (read: efficiently) than just get it done.

Old Pedant · August 26th, 2008, 04:26 PM

Made a nice long post & forum went South on me.

Well, let's ask most important question first:

How busy is this page? If you don't have hundreds of hits
per hour on this page, it may not be worth bothering with
a rewrite.

If we are going to try rewrite, I need to know where your
    specArray
is coming from.

I gather that has all the previously checked checkboxes.
But why aren't you just joining to your
     AgentsSpecializations
table in your query, instead of having to use the slow and
clumsy loop through the array?

Finally, what's with
    sql="Select * From " & SQLDatabase & "AgentsSpecializations ...

Do you REALLY use this code for multiple tables? Based on what?
Why not a single table with a discriminator field?? Much
better DB design and generally more efficient, assuming you
have your indexes specified correctly.

Old Pedant · August 26th, 2008, 04:28 PM

Surely this is a bug:

      ID=clng(rs1("ID"))
      Title=trim(rs1("Title"))
      if Ubound(specArray)>0 then
         for j = 0 to Ubound(specArray)
            if clng(specArray(j)) = ChildID then
           pCheckValue = "checked"
           exit for
        end if
     next

No?

Old Pedant · August 26th, 2008, 04:58 PM

Just noticed! Your "tree" is only 2 levels deep!

SHEESH! Sorry! We can EASILY rewrite this much simpler, w/o the
hassle of a JS solution.

Just need you to answer the questions asked in prior post.

Broodmdh · August 27th, 2008, 06:03 AM

Ya, the tree is only two levels deep. This page won't be very active, except when it is initially deployed (and I doubt that even then it will see too much action). You are right in assuming that specArray is a list of all of the previously checked boxes.

I was using ID for the "parent" level boxes, and ChildID for the "child" level boxes. SQL and db's are not my specialty, so I don't doubt that both could be cleaned up substantially. My goal is simply to get a list of all records in the Specializations table and then compare the list of records from AgentsSpecializations to it. The user should see all of the previously selected records and have to option to select others.

Old Pedant · August 27th, 2008, 08:18 PM

Okay, as I look at your code, it appears that if a specialization is a parent with children, you do *NOT* give a checkbox for it. (But if it has no children, you do give it a checkbox.)

Assuming that's what you want, try this code. CAUTION: Completely untested and off the top of my head. Don't be surprised if there's a typo or two. I wrote it in Notepad.

NOTE: This is for an Access DB. For MySQL change "IIF" to just "IF". For SQL Server, we'll have to rewrite the "IIF" to "CASE WHEN".

********* form page ********

Code:

<form action="processSpecializations.asp" method=post>
<%
Set db = Server.CreateObject("ADODB.Connection")
db.Open "...your connection string ..."

SQL = "SELECT 1 AS level, S.id AS groupID, S.id AS childID, S.title, IIF(A.SpecializationID IS NULL,'','CHECKED') AS chk " _
    & "FROM Specializations AS S LEFT JOIN AgentSpecializations AS A " _
    & "ON S.id = A.SpecializationID " _
    & "WHERE S.ParentID IS NULL " _
    & " UNION " _
    & "SELECT 2 AS level, S.parentID AS groupID, id AS childID, S.title,  IIF(A.SpecializationID IS NULL,'','CHECKED') AS chk " _
    & "FROM Specializations AS S LEFT JOIN AgentSpecializations AS A " _
    & "ON S.id = A.SpecializationID " _
    & "WHERE S.ParentID IS NOT NULL " _
    & "ORDER BY 2, 1, 3 "

Set rs = db.Execute( SQL ) ' by FAR the fastest way to get and use a recordset
priorGroup = rs("groupID")

Do Until rs.EOF
    level = rs("level")
    groupID = rs("groupID")
    id = rs("childID")
    title = rs("title")
    checked = rs("chk")

    rs.MoveNext ' we do this now so we can make the hasChildren check

    hasChildren = False
    If Not rs.EOF
        ' if next record is a child, parent has children
        If rs("level") <> 1 Then hasChildren = True
    End If

    ' so... 
    If Not hasChildren Then
        ' a level 1 specialization with no children...
        ' so it gets a simple div and checkbox for itself
%>
        <div style="margin-left:20px; margin-bottom:5px;">
             <input type="checkbox" name="AgentSpec" value="<%=id%>" <%=checked%> >&nbsp;<%=Title%><br>
        </div>
<%  
    Else
        ' so this is a level 1 parent with children...
        ' it's appearance is a lot different: 
        ' -- no checkbox for it, only children have them
        ' -- it has a hidden div that contains the children & a + image to show them
%>
        <div style="margin-left:20px; margin-bottom:5px;">
            <img src="images/plus.gif" id="node<%=ID%>" alt="" 
                 onclick="SwitchImage(this.id);ShowDiv(this.id);" 
                 style="padding-bottom:5px;">
            &nbsp;&nbsp;<%=Title%><br>
        </div>
        <div id="div<%=ID%>" style="margin-left:50px;display:none;">
<%
        ' and now get all the children for the hidden div:
        level = rs("level")
        Do While level = 2 ' for all children 
            groupID = rs("groupID")
            id = rs("childID")
            title = rs("title")
            checked = rs("chk")
            ' output one child checkbox:
%>
            <input type="checkbox" name="AgentSpec" Value="<%=id%>" <%=checked%> >>&nbsp;<%=Title%><br>
<%
            rs.MoveNext ' to next record
            If rs.EOF Then 
                level = 1 ' this will force an end to the hidden div of parent
            Else
                level = rs("level") ' so if not a child will terminate inner loop
            End If
        Loop
%>
        </div>
<%
    End If ' end of parent-with-children
Loop ' go get next parent, if any
rs.Close
db.Close
%>
<input type=hidden name="AgentID" value="<%=agentID%>" >
<input type=submit value="Process Changes">
</form>

*************************************************

***** FILE:  processSpecializations.asp *********

<%
Set db = Server.CreateObject("ADODB.Connection")
db.Open "...your connection string ..."

agentID = CLNG(Request("agentID")) ' or get it from session variable??

db.Execute "DELETE FROM AgentSpecializations WHERE agentID = " & agentID

Set reSafe = New RegExp
reSafe.Pattern = "[^\d\,]"
reSafe.Global = True

specList = reSafe.Replace( Request("AgentSpec"), "" ) ' zap all but digits and commas

SQL = "INSERT INTO AgentSpecializations( AgentID, SpecializationID ) " _
    & " SELECT " & agentID & ", id FROM Specializations " _
    & " WHERE id IN (" & specList & ")"

howMany = -1
db.Execute SQL, howMany

Response.Write "Agent " & agentID & " now has " & howMany & " specializations."
db.Close
%>