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 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
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 28th, 2008, 01:33 PM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP to query a SQL recursive tree

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!!!
Reply With Quote
  #2 (permalink)  
Old April 28th, 2008, 07:55 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #3 (permalink)  
Old April 29th, 2008, 03:02 PM
Registered User
 
Join Date: Apr 2008
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

Reply With Quote
  #4 (permalink)  
Old April 29th, 2008, 03:09 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #5 (permalink)  
Old April 10th, 2012, 07:59 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy Help Me... WSUS Tree Groups

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
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
Recursive Query for Formatted Output Itech SQL Server 2005 11 June 12th, 2008 04:32 PM
Help with a recursive Query!!! dbayona SQL Server 2005 1 December 20th, 2007 07:40 AM
recursive query cathiec SQL Language 2 August 25th, 2006 05:58 AM
Fill Tree View Recursive And Display Right To Left favaedi BOOK: ASP.NET Website Programming Problem-Design-Solution 1 November 24th, 2004 02:36 PM
Sql Query in ASP mateenmohd SQL Server 2000 3 October 29th, 2003 12:16 AM



All times are GMT -4. The time now is 06:00 AM.


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