ASP Looping, Recursion to display hierarchy (How to?)

M

Matt

I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

I have two tables. One table has "Locations" (tb_locations), and another
table has "Machines" (tb_machines). Being this as the case, I have a SQL JOIN
query to allow me to pull all the information from both tables.

My SQL Query is as follows:
Code:
"SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
c.f_machinename"

As you can see, tb_machines.f_machineparent references
tb_locations.f_locationID. I have looked through literally thousands of pages
of Google results and have found hundreds of examples of recursion, but none
dealing with my scenario of two related database tables.

Here is my current code, which does get the first "Location" and the first
"Machine" related to that location, but it simply loops through that machine
hundreds of times until I get "Out of Memory" errors:
Code:
		Call ListCategory(0)
Sub ListCategory(parentID)
' -- Create Recordset --
Dim rs_clients
Dim rs_clients_cmd
Dim rs_clients_numRows

Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_clients_cmd.Prepared = true

Set rs_clients = rs_clients_cmd.Execute
rs_clients_numRows = 0

' -- Loop Through Pages --
If Not rs_clients.EOF Then
Do While Not rs_clients.EOF
response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
response.write Space(12) & "<ul>" & vbCrLf
response.write Space(14) & "<li class=""parent"">" &
rs_clients("f_locationname") & "</a></li>" & vbCrLf

Call ListSubCategory(rs_clients("f_locationID"), 1)

response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
response.write Space(12) & "</ul>" & vbCrLf
response.write Space(10) & "</div>" & vbCrLf

intCounter = intCounter + 1
If intCounter Mod 3 = 0 Then
response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
End If
rs_clients.MoveNext
Loop
End If

' -- Clear Divs --
If intCounter Mod 3 <> 0 Then
response.write Space(10) & "<div id=""clear""></div>"
End If

' -- Close Connection --
rs_clients.Close
Set rs_clients = Nothing
End Sub


Sub ListSubCategory(f_locationID, Counter)
Dim rs_machines
Dim rs_machines_cmd
Dim rs_machines_numRows

Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_machines_cmd.Prepared = true

Set rs_machines = rs_machines_cmd.Execute
rs_machines_numRows = 0

' -- Loop Through Pages --
If Not rs_machines.EOF Then
Do While Not rs_machines.EOF
response.write Space(14) & "<li style=""margin-left: " & (Counter *
10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf


Counter = Counter + 1
Call ListSubCategory(rs_machines("f_locationID"), Counter)
Counter = Counter - 1
rs_machines.MoveNext
Loop
End If

' -- Close Connection --
rs_machines.Close
Set rs_machines = Nothing
End Sub
Result:
Code:
- Paris
- machine01
-machine01
-machine01
-machine01
-machine01
....etc

So at some point I'm missing a step on getting out of that first machine's
loop and moving onto the second machine. Does anybody have any experience
with this?

Thanks in advance,
Matt
 
D

Dave Anderson

Matt said:
Sub ListSubCategory(f_locationID, Counter)
...
Call ListSubCategory(rs_machines("f_locationID"), Counter)

You are passing a value for f_locationID, but never using it.
 
E

Evertjan.

=?Utf-8?B?TWF0dA==?= wrote on 04 dec 2008 in
microsoft.public.inetserver.asp.general:
If intCounter Mod 3 = 0 Then
response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
End If
rs_clients.MoveNext
Loop

Having multiple id's with the same value is illegal.
 
A

Anthony Jones

Matt said:
I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been
able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

I have two tables. One table has "Locations" (tb_locations), and another
table has "Machines" (tb_machines). Being this as the case, I have a SQL
JOIN
query to allow me to pull all the information from both tables.

My SQL Query is as follows:
Code:
"SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
c.f_machinename"

I don't see a recursive relationship here. It doesn't help that * in the
select really fails to inform and is not good practice, list all the fields
needed. My guess is that if there is a recursive relationship its because
Locations point to sub locations. Is this the case? If the location table
does not refer to itself or the machine table does not have more than one
relationship to the location table then you don't have a recursive system.

Your post has the problem of have a good many things wrong with it. Most
unrelated to your question. We need to take in steps. First we need to
establish is there really a recursive relationship here or not?
 
M

Mike Brind

Matt said:
I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been
able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

I have two tables. One table has "Locations" (tb_locations), and another
table has "Machines" (tb_machines). Being this as the case, I have a SQL
JOIN
query to allow me to pull all the information from both tables.

My SQL Query is as follows:
Code:
"SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
c.f_machinename"

As you can see, tb_machines.f_machineparent references
tb_locations.f_locationID. I have looked through literally thousands of
pages
of Google results and have found hundreds of examples of recursion, but
none
dealing with my scenario of two related database tables.

Here is my current code, which does get the first "Location" and the first
"Machine" related to that location, but it simply loops through that
machine
hundreds of times until I get "Out of Memory" errors:
Code:
 Call ListCategory(0)
Sub ListCategory(parentID)
' -- Create Recordset --
Dim rs_clients
Dim rs_clients_cmd
Dim rs_clients_numRows

Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_clients_cmd.Prepared = true

Set rs_clients = rs_clients_cmd.Execute
rs_clients_numRows = 0

' -- Loop Through Pages --
If Not rs_clients.EOF Then
Do While Not rs_clients.EOF
response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
response.write Space(12) & "<ul>" & vbCrLf
response.write Space(14) & "<li class=""parent"">" &
rs_clients("f_locationname") & "</a></li>" & vbCrLf

Call ListSubCategory(rs_clients("f_locationID"), 1)

response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
response.write Space(12) & "</ul>" & vbCrLf
response.write Space(10) & "</div>" & vbCrLf

intCounter = intCounter + 1
If intCounter Mod 3 = 0 Then
response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
End If
rs_clients.MoveNext
Loop
End If

' -- Clear Divs --
If intCounter Mod 3 <> 0 Then
response.write Space(10) & "<div id=""clear""></div>"
End If

' -- Close Connection --
rs_clients.Close
Set rs_clients = Nothing
End Sub


Sub ListSubCategory(f_locationID, Counter)
Dim rs_machines
Dim rs_machines_cmd
Dim rs_machines_numRows

Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_machines_cmd.Prepared = true

Set rs_machines = rs_machines_cmd.Execute
rs_machines_numRows = 0

' -- Loop Through Pages --
If Not rs_machines.EOF Then
Do While Not rs_machines.EOF
response.write Space(14) & "<li style=""margin-left: " & (Counter *
10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf


Counter = Counter + 1
Call ListSubCategory(rs_machines("f_locationID"), Counter)
Counter = Counter - 1
rs_machines.MoveNext
Loop
End If

' -- Close Connection --
rs_machines.Close
Set rs_machines = Nothing
End Sub
Result:
Code:
- Paris
- machine01
-machine01
-machine01
-machine01
-machine01
...etc

So at some point I'm missing a step on getting out of that first machine's
loop and moving onto the second machine. Does anybody have any experience
with this?

Thanks in advance,
Matt

Like the others, I don't see a recursive relationship here either. Getting
the contents of folders generally requires recursion, because there is no
way of knowing how many levels of nested subfolders there are, so you
construct a method or function to look at a folder, then list its contents.
That function then calls itself on all folders found within the folder
contents, lists the contents, and then calls itself on all folders found
within the subfolder contents, lists the contents, and then calls itself
on....

You hopefully get the point.

What you appear to be doing in your case is to display a Master-Child or
One-To-Many relationship. The inefficient way to do this is to select all
the locations, and the for each location, fire a select command to obtain
the related machines. That is the way you see code samples show this most
often. But there are better ways, not least this one:

http://databases.aspfaq.com/database/how-do-i-present-one-to-many-relationships-in-my-asp-page.html
 
M

Matt

Mike Brind said:
Matt said:
I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been
able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

I have two tables. One table has "Locations" (tb_locations), and another
table has "Machines" (tb_machines). Being this as the case, I have a SQL
JOIN
query to allow me to pull all the information from both tables.

My SQL Query is as follows:
Code:
"SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
c.f_machinename"

As you can see, tb_machines.f_machineparent references
tb_locations.f_locationID. I have looked through literally thousands of
pages
of Google results and have found hundreds of examples of recursion, but
none
dealing with my scenario of two related database tables.

Here is my current code, which does get the first "Location" and the first
"Machine" related to that location, but it simply loops through that
machine
hundreds of times until I get "Out of Memory" errors:
Code:
 Call ListCategory(0)
Sub ListCategory(parentID)
' -- Create Recordset --
Dim rs_clients
Dim rs_clients_cmd
Dim rs_clients_numRows

Set rs_clients_cmd = Server.CreateObject ("ADODB.Command")
rs_clients_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_clients_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_clients_cmd.Prepared = true

Set rs_clients = rs_clients_cmd.Execute
rs_clients_numRows = 0

' -- Loop Through Pages --
If Not rs_clients.EOF Then
Do While Not rs_clients.EOF
response.write Space(10) & "<div id=""sitemap-category"">" & vbCrLf
response.write Space(12) & "<ul>" & vbCrLf
response.write Space(14) & "<li class=""parent"">" &
rs_clients("f_locationname") & "</a></li>" & vbCrLf

Call ListSubCategory(rs_clients("f_locationID"), 1)

response.write Space(14) & "<div id=""clear""></div>" & vbCrLf
response.write Space(12) & "</ul>" & vbCrLf
response.write Space(10) & "</div>" & vbCrLf

intCounter = intCounter + 1
If intCounter Mod 3 = 0 Then
response.write Space(10) & "<div id=""clear""></div>" & vbCrLf
End If
rs_clients.MoveNext
Loop
End If

' -- Clear Divs --
If intCounter Mod 3 <> 0 Then
response.write Space(10) & "<div id=""clear""></div>"
End If

' -- Close Connection --
rs_clients.Close
Set rs_clients = Nothing
End Sub


Sub ListSubCategory(f_locationID, Counter)
Dim rs_machines
Dim rs_machines_cmd
Dim rs_machines_numRows

Set rs_machines_cmd = Server.CreateObject ("ADODB.Command")
rs_machines_cmd.ActiveConnection = MM_conn_beaconreader_STRING
rs_machines_cmd.CommandText = "SELECT * FROM tb_locations p LEFT JOIN
tb_machines c ON p.f_locationID = c.f_machineparent ORDER BY
p.f_locationname, c.f_machinename"
rs_machines_cmd.Prepared = true

Set rs_machines = rs_machines_cmd.Execute
rs_machines_numRows = 0

' -- Loop Through Pages --
If Not rs_machines.EOF Then
Do While Not rs_machines.EOF
response.write Space(14) & "<li style=""margin-left: " & (Counter *
10) & "px;"">" & rs_machines("f_machinename") & "</li>" & vbCrLf


Counter = Counter + 1
Call ListSubCategory(rs_machines("f_locationID"), Counter)
Counter = Counter - 1
rs_machines.MoveNext
Loop
End If

' -- Close Connection --
rs_machines.Close
Set rs_machines = Nothing
End Sub
Result:
Code:
- Paris
- machine01
-machine01
-machine01
-machine01
-machine01
...etc

So at some point I'm missing a step on getting out of that first machine's
loop and moving onto the second machine. Does anybody have any experience
with this?

Thanks in advance,
Matt

Like the others, I don't see a recursive relationship here either. Getting
the contents of folders generally requires recursion, because there is no
way of knowing how many levels of nested subfolders there are, so you
construct a method or function to look at a folder, then list its contents.
That function then calls itself on all folders found within the folder
contents, lists the contents, and then calls itself on all folders found
within the subfolder contents, lists the contents, and then calls itself
on....

You hopefully get the point.

What you appear to be doing in your case is to display a Master-Child or
One-To-Many relationship. The inefficient way to do this is to select all
the locations, and the for each location, fire a select command to obtain
the related machines. That is the way you see code samples show this most
often. But there are better ways, not least this one:

http://databases.aspfaq.com/database/how-do-i-present-one-to-many-relationships-in-my-asp-page.html

Mike, thanks for the reply. That was one of the first articles that I
referenced, and it works....to a point. First, maybe I'm not doing recursion,
I guess that's why I posted here because I'm really unsure about how to
"term" what I am doing, which makes Internet searches for help very difficult.

Second, back to the meat of our discussion. The link you showed is what I
used previously to test what I have, but that limits me to a single
parent-child relationship, where I have a parent, with children, who have
children, who have children, continuing infinitely (though I hope not). Since
apparently this is not recursion, that would explain why I cannot find any
examples online.

Here is a basic structure of what I intend to accomplish:

San Francisco
|
|---> Car Dealership 1
|
|--->Machine01
|--->Machine02
|
|---> Car Dealership 2
|
|--->West Wing
|--->Machine03
|--->Machine04
|--->East Wing
|--->Machine05
|--->Machine06
|--->Machine07

------------------------------------
So locations can be children of other locations. If location equals zero
(0), then it is a top-level location, otherwise it's f_locationparent (sorry,
I was definitely not clear enough in my first post) can reference the
f_locationID of another location:

Here is some sample data (used in the little example hierarchy above):
CREATE TABLE [dbo].[tb_locations](
[f_locationID] [int] IDENTITY(1,1) NOT NULL,
[f_locationcompany] [int] NOT NULL,
[f_locationparent] [int] NOT NULL,
[f_locationname] [nvarchar](50) NULL,
[f_locationlogofile] [nvarchar](50) NULL

CREATE TABLE [dbo].[tb_machines](
[f_machineID] [int] IDENTITY(1,1) NOT NULL,
[f_machineGUID] [nvarchar](50) NOT NULL,
[f_machineparent] [int] NOT NULL,
[f_machineserial] [nvarchar](50) NULL,
[f_machinename] [nvarchar](50) NULL,
[f_OSversion] [nchar](10) NULL,
[f_lastreported] [datetime] NULL,
[f_lastIPaddresses] [nvarchar](50) NULL
=====================================
Some data within the tables (used as the hierarchy display above) would be
as follows:

INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
VALUES (1,0,San Francisco)
INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
VALUES (2,1,Car Dealership 1)
INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
VALUES (3,1,Car Dealership 2)
INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
VALUES (4,3,West Wing)
INSERT INTO tb_locations(f_locationID, f_locationparent,f_locationname)
VALUES (5,3,West Wing)

INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (2,Machine01)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (2,Machine02)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (4,Machine03)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (4,Machine04)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (5,Machine05)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (5,Machine06)
INSERT INTO tb_machines(f_machineparent,f_machinename) VALUES (3,Machine07)

Thanks for the reply Mike, I appreciate it.
Matt
 
M

Matt

Anthony Jones said:
Matt said:
I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been
able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

I have two tables. One table has "Locations" (tb_locations), and another
table has "Machines" (tb_machines). Being this as the case, I have a SQL
JOIN
query to allow me to pull all the information from both tables.

My SQL Query is as follows:
Code:
"SELECT * FROM tb_locations p LEFT JOIN tb_machines c ON
p.f_locationID = c.f_machineparent ORDER BY p.f_locationname,
c.f_machinename"

I don't see a recursive relationship here. It doesn't help that * in the
select really fails to inform and is not good practice, list all the fields
needed. My guess is that if there is a recursive relationship its because
Locations point to sub locations. Is this the case? If the location table
does not refer to itself or the machine table does not have more than one
relationship to the location table then you don't have a recursive system.

Your post has the problem of have a good many things wrong with it. Most
unrelated to your question. We need to take in steps. First we need to
establish is there really a recursive relationship here or not?

Anthony, thanks for the reply. I posted more information in a reply to Mike
Brind, so hopefully that better explains the scenario. Also, I always use the
(*) for my select statements when posting them to support forums / newsgroups
if it doesn't add to the discussion, otherwise it makes the query look
overwhelming when simply trying to glance at what it does. Unfortunately, in
doing that, I always receive the inevitable reply like yours telling me that
using a wildcard is a bad idea.
 
M

MightyChaffinch

Matt said:
I am having some serious brain-melting session just trying to wrap my head
around what I'm trying to accomplish, but unfortunately I've never been able
to properly put it into words. I found some articles on using recursion to
display a hierarchy on the file system. Unfortunately, I think I GET the
basic concept of what recursion means now (I think, but I'm likely wrong),
but I cannot figure out how to migrate the practice into how my database
works.

... detail snipped ...

This is basically a "bill-of-materials" type of problem = try Googling
"BOM". I've done a quite similar thing before, this is the solution I
worked out if it helps...

Standard SQL doesn't understand your hierarchy of locations. You need to
do a recursion which fires a select for each level of the hierarchy in
turn until there are no locations found. Then you have reached the
bottom of the deepest hierarchy.

First create a work table to hold your BOM expansion:
CREATE TABLE Locn_Tree (
Level_Nr INT NOT NULL,
Locn VARCHAR (25) NOT NULL,
Parent_Locn VARCHAR (25),
other columns...);

Next put the node that you want to expand into the work table:
INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
SELECT 0, L.Locn, L.Parent_Locn, ...
FROM Locn AS L
WHERE P.Locn = 'locn';

And now expand the first level in the hierarchy:
INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
SELECT 1, L.Locn, L.Parent_Locn, ...
FROM Locn_Tree AS T
INNER JOIN Locn AS LP
ON T.Locn = L.Parent_Locn
WHERE T.Level_Nr = 0;

And again for each level in turn until no more rows are INSERTed:
INSERT INTO Locn_Tree (Level_Nr, Locn, Parent_Locn, ...)
SELECT (level), L.Locn, L.Parent_Locn, ...
FROM Locn_Tree AS T
INNER JOIN Locn AS L
ON T.Locn = L.Parent_Locn
WHERE T.Level_Nr = (level - 1);

Now you have the part of the hierarchy you want expanded in the work
table, and you can SELECT it in the order you want to present it.

There's an example here:
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

And example code here for Access:
http://www.mvps.org/access/queries/qry0023.htm

Hope this helps,
MC
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top