 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

August 26th, 2008, 09:11 AM
|
Registered User
|
|
Join Date: Aug 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

August 26th, 2008, 12:50 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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??
|

August 26th, 2008, 01:06 PM
|
Registered User
|
|
Join Date: Aug 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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;"> <%=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%>> <%=ChildTitle%><br>
<%
rs2.movenext
cCheckValue=""
loop
%>
</div>
<% else %>
<div style="margin-left:20px; margin-bottom:5px;"><input type="checkbox" name="<%=ID%>" id="<%=ID%>" <%=pCheckValue%>> <%=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.
|

August 26th, 2008, 02:23 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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.
|

August 26th, 2008, 03:32 PM
|
Registered User
|
|
Join Date: Aug 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

August 26th, 2008, 04:26 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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.
|

August 26th, 2008, 04:28 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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?
|

August 26th, 2008, 04:58 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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.
|

August 27th, 2008, 06:03 AM
|
Registered User
|
|
Join Date: Aug 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

August 27th, 2008, 08:18 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
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%> > <%=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;">
<%=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%> >> <%=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
%>
|
|
 |