ASP to query a SQL recursive tree

chame · April 28th, 2008, 01:33 PM

Hello All,

Hopefully my question isn't too confusing and someone maybe able to help me. I'm completely new to ASP and I'm trying to display hierarchical data from a database that's already has a stored procedure set to categorize the data from the table hierarchically by assigning each item a level number.

The problem I am having is writing a proper function to actually display the data the way I want it. right now it's display all the data as parent nodes instead of descending in the correct order according to it's level number.

Here is the code I have so far:

Code:

<% 

                'get Recordset, objRS
                dim objRS
                set objRS = server.CreateObject("adodb.recordset")

                dim y
                if request("goCat") = "Go" and request("txtSearchCategory") <> "" then                    
                    y = FindLUCategoriesByName( request("txtSearchCategory"),(request("selDoc")), objRS)                    
                elseif request("showCat") <> "" then
                    y = GetLUCategories("", objRS)
                elseif request ("selDoc")    <> "" then
                    y = GetLUCategoriesByLayer(objrs, request ("selDoc"), 2)
                else
                    y = GetLUCategories("", objRS)                        
                end if    



                       [b] dim i
            dim layer
            set rs = server.CreateObject("adodb.recordset")
             layer = GetLUCategoriesByLayer(rs, documentTypeId, 2)
             category = rs("CATEGORY_NAME")
            set objRS = server.CreateObject("adodb.recordset")
             i = layer    
            if (i = 1) then              
             category = rs("CATEGORY_NAME")
            if (i >1) then
                  objRS.MoveNext 
                category = rs("CATEGORY_NAME")
              else               
            end if [\b]




    'First, display the TABLE header info:
    Response.Write "<Table border=1 cellspacing=0 cellpadding=5 id=box>"
            'Write the TR
            Response.Write "<TR>"                                            
                Response.Write "<TD>" 

        Do While Not objRS.EOF

            category = objrs("CATEGORY_NAME")
            layer = objrs("LAYER_NUMBER")
            documentTypeId = objrs("DOCUMENT_TYPE_ID")
            categoryId = objrs("CATEGORY_ID")
            parentCategoryId = objrs("PARENT_CATEGORY_ID")    


                   dim counter
                   counter = 0
                   loopCounter = loopCounter + 1

                   'Now loop through the recordset Fields
                   For Each objField in objRS.Fields
                     counter = counter + 1
                     'temp = objField.value

                       if objField.name = "CATEGORY_ID" then

                          elseif objField.name = "CATEGORY_NAME" then
                            if IsNull(objField.value) then
                                temp = " "

                            end if



                                Response.Write "[list]"
                                Response.Write  "<a href=category.asp?edit=categoryId" & ">" & Category & "</a>"
                                    Response.Write "[list]"
                                        Response.Write "<li>"  & layer &  "</li>"
                                    Response.Write "</ul>"
                                Response.Write "</ul>"

        'Advance the recordset
         objRS.MoveNext                
            end if 
            Next
            loop
            end if 
                    Response.Write "</TD>"
             'Close the TR tag
              Response.Write "</TR>"                
        'Close the table tag...
        Response.Write "</TABLE>"
    objRS.close
    set objRS = nothing
                %>

I'm especially having problems with the bold part what I wanted it to do is each time it calls the function it check the level number if it's "1" that's the parent node then 2.. 3 etc..

Thanks in Advance!!!

dparsons · April 28th, 2008, 07:55 PM

O.o Wow your code is all over the place. Rather then to try and make heads or tails of it all lets focus on this part:

Code:

dim i
dim layer

    'Based on the code here, you never open this recordset with a command
    set rs = server.CreateObject("adodb.recordset")
    'I assume this Sub returns an Int??
    layer = GetLUCategoriesByLayer(rs, documentTypeId, 2)
    category = rs("CATEGORY_NAME")
    'It is not entirely clear what you are doing here or why
    set objRS = server.CreateObject("adodb.recordset")
    i = layer    
    if (i = 1) then category = rs("CATEGORY_NAME")
    if (i > 1) then
        objRS.MoveNext 
        category = rs("CATEGORY_NAME")             
    end if

I am not sure where to start. You do not make it clear what queries you are executing or what data is returned. (I understand that layer is always 1)

Anyway, here is some pseudo code as to how I have done this in the past:

'Create a recordset with just the parent nodes
While Not rs.EOF
  'Process the text to the screen (or variable, or whatever)
  'Now send the children for this element to the screen
   ProcessChildren(rs("ParentID"))
   'Advance the recordset
   rs.MoveNext
Wend

Sub ProcessChildren(id)
'Open recordset that returns all children of parent ID
While Not rs.EOF
   'Output text to the screen, variable, etc

   'Now for the recursion, since a child can contain children itself
   ProcessChildren(rs("ParentID"))
   rs.MoveNext
Wend
End Sub

Does this make sense?

hth.
-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

chame · April 29th, 2008, 03:02 PM

Hey Doug,

Thanks for the response.... as I said I'm soo new to asp and not sure where to begin soo sorry my code probably doesn't make sense :)

But you explanation helps... I had a feeling it was much simpler than what I was trying to do.

Thanks again!

dparsons · April 29th, 2008, 03:09 PM

No problem. Let us know if you have any other issues!

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Zoanoid · April 10th, 2012, 07:59 PM

I gently ask for help

I tried to apply this code but without success...
I'm just trying to see the same tree groups on the WSUS in a web page...
Can you help me?

[BAD CODE:]

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=SQLNCLI10;Data Source=\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query;Da tabase=SUSDB;Trusted_Connection=yes"

Set rs1 = conn.Execute("SELECT Name, TargetGroupID, ParentGroupID, GroupPriority FROM tbTargetGroup WHERE TargetGroupTypeID = 0 AND GroupPriority = 0")
while rs1.eof = false

Set rs2 = conn.Execute("SELECT Name, TargetGroupID, ParentGroupID, GroupPriority FROM tbTargetGroup WHERE TargetGroupTypeID = 0 AND ParentGroupID = '" & rs1.fields(1) & "' ORDER BY Description DESC, Name")
while rs2.eof = false
ind = ""
for i = 1 to rs2.fields(3)
ind = ind & "-"
next
Response.Write ind & rs2.fields(0) & "<br />"

Set rs3 = conn.Execute("SELECT Name, TargetGroupID, ParentGroupID, GroupPriority FROM tbTargetGroup WHERE TargetGroupTypeID = 0 AND ParentGroupID = '" & rs2.fields(1) & "' ORDER BY Description DESC, Name")
while rs3.eof = false
ind = ""
for i = 1 to rs3.fields(3)
ind = ind & "-"
next
Response.Write ind & rs3.fields(0) & "<br />"

Set rs4 = conn.Execute("SELECT Name, TargetGroupID, ParentGroupID, GroupPriority FROM tbTargetGroup WHERE TargetGroupTypeID = 0 AND ParentGroupID = '" & rs3.fields(1) & "' ORDER BY Description DESC, Name")
while rs4.eof = false
ind = ""
for i = 1 to rs4.fields(3)
ind = ind & "-"
next
Response.Write ind & rs4.fields(0) & "<br />"

rs4.movenext
wend

rs3.movenext
wend

rs2.movenext
wend

rs1.movenext
wend

conn.close
%>

[OUTPUT:]

-Computer non assegnati
-Client
--Client Blacklist
--Client Test
---Client Group 01
---Client Group 02
-Server
--Server Blacklist
--Server Test
---Server Group 01
---Server Group 02

[DB CSV:]

[TargetGroupTypeID];[Name];[Description];[TargetGroupID];[OrderValue];[IsBuiltin];[ParentGroupID];[GroupPriority]
0;Computer non assegnati;;b73ca6ed-5727-47f3-84de-015e03f6a88a;0;TRUE;a0a08746-4dbe-4a37-9adf-9e7652c0b421;1
0;Client Test;NULL;b4b5109e-291b-4c5b-a0ce-01ea721cef08;0;FALSE;fa185ee0-2d42-45d7-9ec9-336198c9b8a6;2
0;Client Blacklist;NULL;478d4eab-0002-4acb-9ac4-04c1d349f8b9;0;FALSE;fa185ee0-2d42-45d7-9ec9-336198c9b8a6;2
0;Server Group 02;NULL;20a29e4e-116a-45ec-adef-18769575cbeb;0;FALSE;fdb59076-0c8e-48bb-90c4-bc149f52247d;3
0;Client;NULL;fa185ee0-2d42-45d7-9ec9-336198c9b8a6;0;FALSE;a0a08746-4dbe-4a37-9adf-9e7652c0b421;1
1;Server figlio;;d374f42a-9be2-4163-a0fa-3c86a401b7a7;0;TRUE;a0a08746-4dbe-4a37-9adf-9e7652c0b421;1
0;Client Group 02;NULL;12d5a0ee-32bd-4ed8-9b59-688b4f7c453d;0;FALSE;b4b5109e-291b-4c5b-a0ce-01ea721cef08;3
0;Server Blacklist;NULL;ddfe7e3f-d8d6-415a-b76e-68f964482b0a;0;FALSE;5e5128e5-fee1-46a0-9a68-b1e51d36530c;2
0;Client Group 01;NULL;b2ce45ed-300a-49e0-86e7-869f54b1c4c8;0;FALSE;b4b5109e-291b-4c5b-a0ce-01ea721cef08;3
0;Tutti i computer;;a0a08746-4dbe-4a37-9adf-9e7652c0b421;0;TRUE;NULL;0
0;Server;NULL;5e5128e5-fee1-46a0-9a68-b1e51d36530c;0;FALSE;a0a08746-4dbe-4a37-9adf-9e7652c0b421;1
0;Server Test;NULL;fdb59076-0c8e-48bb-90c4-bc149f52247d;0;FALSE;5e5128e5-fee1-46a0-9a68-b1e51d36530c;2
0;Server Group 01;NULL;60b2eead-8c04-4103-9076-f4e9c744e07a;0;FALSE;fdb59076-0c8e-48bb-90c4-bc149f52247d;3