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:
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:
Result:
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
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
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