Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 26th, 2008, 09:11 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Displaying hierarchical data for selection

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.

Reply With Quote
  #2 (permalink)  
Old August 26th, 2008, 12:50 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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??
Reply With Quote
  #3 (permalink)  
Old August 26th, 2008, 01:06 PM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old August 26th, 2008, 02:23 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old August 26th, 2008, 03:32 PM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old August 26th, 2008, 04:26 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old August 26th, 2008, 04:28 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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?
Reply With Quote
  #8 (permalink)  
Old August 26th, 2008, 04:58 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old August 27th, 2008, 06:03 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #10 (permalink)  
Old August 27th, 2008, 08:18 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
%>
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
data selection and mapping with dropdownlist gagansharma7 ASP.NET 1.0 and 1.1 Basics 0 June 27th, 2006 11:41 AM
Data List Selection Norberto_Caraballo Beginning VB 6 1 June 24th, 2006 09:41 AM
Getting data based on field selection Trojan_uk Classic ASP Basics 1 December 9th, 2003 10:47 AM
How to make hierarchical data "lay flat" using SQL tinlong SQL Language 1 August 1st, 2003 01:38 AM
Displaying field selection meiyeen JSP Basics 0 July 13th, 2003 11:22 PM



All times are GMT -4. The time now is 04:47 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.