Need help with recursively getting data from database

[Dragonhunter]

Hello all,

I'm trying to make a simple database that stores notes in categories.
Notes can have multiple categories and categories can have other
categories as subcategories. A web page will list a category and any
subcategories and notes under it. Seems simple but when I try to
implement it my head starts spinning...

Here is my database structure:

category
id
name
expand
note
id
text
note_categories
note_id
category_id
subcategories
child_id
parent_id

Here's what I'm trying to do on a webpage:

Find all of categories in category that have no parent in subcategories

For each such category [LOOP1],
list the name of the category
if the category is has expand = true,
list the name of subcategories [REPEAT LOOP1 FOR EACH, etc...]
list notes matching that category in note_categories
}
}

I got it working without the recursion part using nested loops, then I
read aspfaq.com's #2241 and came up with the table structure above. I
won't utter my crappy nested loop code here... how do I do it properly?

Any help would be appreciated.

dragonhunter

[Bob Barrows [MVP]]

Dragonhunter wrote:

> Hello all,
>
> I'm trying to make a simple database that stores notes in categories.
> Notes can have multiple categories and categories can have other
> categories as subcategories. A web page will list a category and any
> subcategories and notes under it. Seems simple but when I try to
> implement it my head starts spinning...
>

Can you provide a few rows of sample data for each table (in tabular form)
and the desired result? Descriptions can only go so far, plus it will help
us get to the correct solution if we are discussing the data that you will
be using.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Chris Hohmann]

"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
news:407CB995.9DF001C0@yahoo.com...

> Hello all,
>
> I'm trying to make a simple database that stores notes in categories.
> Notes can have multiple categories and categories can have other
> categories as subcategories. A web page will list a category and any
> subcategories and notes under it. Seems simple but when I try to
> implement it my head starts spinning...
>
> Here is my database structure:
>
> category
> id
> name
> expand
> note
> id
> text
> note_categories
> note_id
> category_id
> subcategories
> child_id
> parent_id
>
> Here's what I'm trying to do on a webpage:
>
> Find all of categories in category that have no parent in

subcategories

>
> For each such category [LOOP1],
> list the name of the category
> if the category is has expand = true,
> list the name of subcategories [REPEAT LOOP1 FOR EACH, etc...]
> list notes matching that category in note_categories
> }
> }
>
> I got it working without the recursion part using nested loops, then I
> read aspfaq.com's #2241 and came up with the table structure above. I
> won't utter my crappy nested loop code here... how do I do it

properly?

>
> Any help would be appreciated.
>
> dragonhunter

[url]http://groups.google.com/groups?threadm=ej7OlMSGEHA.3856%40TK2MSFTNGP12.phx .gbl[/url]

[Dragonhunter]

"Bob Barrows [MVP]" wrote:

> Can you provide a few rows of sample data for each table (in tabular form)
> and the desired result? Descriptions can only go so far, plus it will help
> us get to the correct solution if we are discussing the data that you will
> be using.
>
> Bob Barrows

Sure, here is my database and an example of the web page I want to create to
output the data. I structured the database to normalize after being explained
the concept on this group (thanks!). So, how do I do this recursive
displaying of data properly using ASP? I think I'm pretty stuck on the
SQL..., particularly when I try to do the "join" method instead of nested
loops. Any help would be appreciated!

Thanks,
Dragonhunter


category
id--name------------------expand
1 Favorite soundtracks true
2 Favorite music true
3 Pets false
4 Favorite quotes true
5 Ordered soundtracks true

note
id--text
1 Star Wars
2 Indiana Jones
3 Fluffy the cat
4 Goldfish 1
5 "To make an omelette you have to break some eggs"
6 "Nested loops are the root of all evil"
7 Batman
8 Predator
9 "it takes one to know one!"
10 Les miserables

note_categories
note_id--category_id
1 1
1 5 <--this is a note in two categories
2 1
3 3
4 3
5 4
6 4
7 1
8 1
9 4
10 1
7 5 <--this is a note in two categories

Subcategories
child_id--parent_id
1 2


Webpage=========================================== ===
+ is expandable to show things under it
- means un-expand it (and store +/- state in a database)


-Favorite music
-Favorite soundtracks
Star Wars
Indiana Jones
Batman
Predator
Les miserables
+Pets <-- none show because "expand" field is false
-Favorite quotes
"To make an omelette you have to break some eggs"
"Nested loops are the root of all evil"
"it takes one to know one!"
-Ordered soundtracks
Star Wars
Batman

[Bob Barrows]

See below:

Dragonhunter wrote:

> "Bob Barrows [MVP]" wrote:
>

>> Can you provide a few rows of sample data for each table (in tabular
>> form) and the desired result? Descriptions can only go so far, plus
>> it will help us get to the correct solution if we are discussing the
>> data that you will be using.
>>
>> Bob Barrows

>
> Sure, here is my database and an example of the web page I want to
> create to output the data. I structured the database to normalize
> after being explained the concept on this group (thanks!). So, how
> do I do this recursive displaying of data properly using ASP? I
> think I'm pretty stuck on the SQL..., particularly when I try to do
> the "join" method instead of nested loops. Any help would be
> appreciated!
>
> Thanks,
> Dragonhunter
>
>

I would probably have added a parent_id column to this table. The
subcategories table is really not needed. The expand field is really not
needed either, although it will make the subsequent coding easier. I'm not
sure this coding ease is worth the headaches that guaranteeing this field
will contain the correct value will cause.
Like this

> category
> id--name--------------------parent_id
> 1 Favorite soundtracks 2
> 2 Favorite music
> 3 Pets
> 4 Favorite quotes
> 5 Ordered soundtracks

However, I will assume you do not make this change

>
> note
> id--text
> 1 Star Wars
> 2 Indiana Jones
> 3 Fluffy the cat
> 4 Goldfish 1
> 5 "To make an omelette you have to break some eggs"
> 6 "Nested loops are the root of all evil"
> 7 Batman
> 8 Predator
> 9 "it takes one to know one!"
> 10 Les miserables
>
> note_categories
> note_id--category_id
> 1 1
> 1 5 <--this is a note in two categories
> 2 1
> 3 3
> 4 3
> 5 4
> 6 4
> 7 1
> 8 1
> 9 4
> 10 1
> 7 5 <--this is a note in two categories
>
> Subcategories
> child_id--parent_id
> 1 2
>
>
> Webpage=========================================== ===
> + is expandable to show things under it
> - means un-expand it (and store +/- state in a database)
>
>
> -Favorite music
> -Favorite soundtracks
> Star Wars
> Indiana Jones
> Batman
> Predator
> Les miserables
> +Pets <-- none show because "expand" field is false
> -Favorite quotes
> "To make an omelette you have to break some eggs"
> "Nested loops are the root of all evil"
> "it takes one to know one!"
> -Ordered soundtracks
> Star Wars
> Batman

OK. I will take care of showing you how to do the nesting. You will need to
take care of the client-side coding needed to expand and collapse the nodes.
You can ask about that in a client-side code newsgroup (newsgroups
containing "dhtml" in their title or one of the .scripting newsgroups). I
will put the + and - characters in spans whose onclick events can be used to
expand or collapse a node.

To start, let's order the categories by parent_id, then child id, so the
master categories will be first:
SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id), c.name, c.expand
FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id
ORDER BY sc.parent_id, c.id;
produces this resultset:

id parent_id name expand
2 0 Favorite music -1
3 0 Pets 0
4 0 Favorite quotes -1
5 0 Ordered soundtracks -1
1 2 Favorite soundtracks -1


Now let's get the notes:
SELECT n.id, c.id, n.text, null AS Expr1
FROM (category AS c
INNER JOIN note_categories AS nc ON c.id = nc.category_id)
INNER JOIN [note] AS n ON nc.note_id = n.id
ORDER BY c.id, n.id;
which gives us this result:
n.id c.id text
Expr1
1 1 Star Wars
2 1 Indiana Jones
7 1 Batman
8 1 Predator
10 1 Les miserables
3 3 Fluffy the cat
4 3 Goldfish 1
5 4 "To make an omelette you have to break some eggs"
6 4 "Nested loops are the root of all evil"
9 4 "it takes one to know one!"
1 5 Star Wars
7 5 Batman


Let's union these results together:

SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id) as parent_id,
c.name, c.expand, "cat" As Type
FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id
UNION ALL SELECT n.id, c.id, n.text, Null, "note"
FROM (category AS c
INNER JOIN note_categories AS nc ON c.id = nc.category_id)
INNER JOIN [note] AS n ON nc.note_id = n.id
ORDER BY parent_id, id;

giving these results:
qTree
id parent_id name
expand type
2 0 Favorite music
True cat
3 0 Pets
False cat
4 0 Favorite quotes
True cat
5 0 Ordered soundtracks
True cat
1 2 Favorite soundtracks
True cat
1 1 Star Wars
note
2 1 Indiana Jones
note
7 1 Batman
note
8 1 Predator
note
10 1 Les miserables
note
3 3 Fluffy the cat
note
4 3 Goldfish 1
note
5 4 "To make an omelette you have to break some eggs"
note
6 4 "Nested loops are the root of all evil"
note
9 4 "it takes one to know one!"
note
1 5 Star Wars
note
7 5 Batman
note

Copy the above sql into the SQL View window of an Access Query Builder and
save it as qTree after running it to verify the results are correct.

In your asp page, do this (as a bonus, I included the client-side code to
make the nodes expand and collapse):
<%
dim cn, rs, sSQL
set cn=createobject("adodb.connection")
cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("db7.mdb")


set rs=CreateObject("adodb.recordset")
rs.CursorLocation = 3 'adUseClient
rs.LockType = 1 'adLockReadOnly
cn.qTree rs
'disconnect the recordset:
set rs.ActiveConnection=nothing
cn.Close: set cn = nothing

if rs.EOF then
Response.Write "no records returned"
else
'stop
WriteChildDivs rs.Clone,rs(1).Value,"cat",-10
rs.Close:set rs = nothing
end if

Sub WriteChildDivs(pRS,pID,byval pType,pIndent)
dim bNextLvlVisible
pRS.Filter="parent_id=" & pID & " and type='" & pType & "'"
if pRS.eof then
ptype = "note"
pRS.Filter="parent_id=" & pID & " and type='" & pType & "'"
end if
do until pRS.EOF
Response.Write "<div style=""margin-left:" & _
pIndent + 10 & "px"">"

select case pRS(3).value
case true
Response.Write "<span onclick=""handleclick();""" & _
" style=""cursor:hand"">"
Response.Write "-</span>"
bNextLvlVisible=true
case false
Response.Write "<span onclick=""handleclick();"" & _
" style=""cursor:hand"">"
Response.Write "+</span>"
bNextLvlVisible=false
case else
bNextLvlVisible=true
end select
Response.Write pRS(2).Value
if pRS(4).value="cat" then
Response.Write "<div style=""display:"
if bNextLvlVisible then
Response.Write "block"">"
else
Response.Write "none"">"
end if
WriteChildDivs pRS.clone, pRS(0).Value,pRS(4).value, pIndent + 10
Response.Write "</div>"
end if
Response.Write "</div>"
pRS.MoveNext
loop
set pRS=nothing
End Sub
%>
<HTML>
<HEAD>
<SCRIPT LANGUAGE=javascript>
function handleclick()
{
var oSpan = event.srcElement
var oDiv=oSpan.nextSibling.nextSibling
switch (oSpan.innerText)
{
case "+":
oDiv.style.display="block"
oSpan.innerText = "-"
break
case "-":
oDiv.style.display="none"
oSpan.innerText = "+"
}
}
</SCRIPT>

</HEAD>
<BODY>
</BODY>
</HTML>

Which looks like this in the browser

-Favorite music
-Favorite soundtracks
Star Wars
Indiana Jones
Batman
Predator
Les miserables
+Pets
-Favorite quotes
"To make an omelette you have to break some eggs"
"Nested loops are the root of all evil"
"it takes one to know one!"
-Ordered soundtracks
Star Wars
Batman


HTH.
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Bob Barrows wrote:

> See below:

<snip>

> In your asp page, do this (as a bonus, I included the client-side
> code to make the nodes expand and collapse):
> <%
> dim cn, rs, sSQL
> set cn=createobject("adodb.connection")
> cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("db7.mdb")

Of course, you would substitute your database's path and name here ...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Augustus]

"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
news:407CB995.9DF001C0@yahoo.com...

> Hello all,
>
> I'm trying to make a simple database that stores notes in categories.
> Notes can have multiple categories and categories can have other
> categories as subcategories. A web page will list a category and any
> subcategories and notes under it. Seems simple but when I try to
> implement it my head starts spinning...

Hiya,

I know my post is 2 days after you had the problem, but in the event that
you didn't find a solution, I'll post one here
[note: I haven't tested this code, but it looks like it should work... the
SQL might need a little tweaking though. You might also find it helpful to
copy this to notepad or similar so you can take the word wrapping off]

First, for the database structure:

CATEGORY
ID
name
expand
relatesTO

NOTES
ID
relatesTO

NOTESTEXT
ID
TEXT

Explanation:
CATEGORY:
id: this is an autonumber identifier
name: this is the name of the category
expand: your true/false, if this category is expanded
relatesTO: for top level categories this is 0, for all other categories this
is the ID for that category

Example Data:
1,Top Level Category 1,true,0
2,Top Level Category 2,true,0
3,Sub Category A,false,1
4,Sub Category B,true,1
5,Sub Category i,false,4
6,Sub Category C,false,2
(you get the idea... the RELATESTO column tells it what category it belongs
under)

NOTES
id: this is a NON-unique identifier
relatesTO: what category.ID this note is displayed next to/under

NOTESTEXT
id: this ID links to the ID under notes (since the text can be repeated you
aren't including the text in the NOTES table)
text: this is the text for the ID

Example Data:
NOTES:
1,1
1,2
2,3
3,5

NOTESTEXT
1,This is a top level category
2,This is Sub Category A
3,This is Sub-Sub Category i


For the code: (pseudocode at the bottom)



Set oRs = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "-- your database connection string --"


sub GetMoreCategories(vGMC)
' create database object
' this has to be created here for recursion to work
dim objGMC
set objGMC = Server.CreateObject("ADODB.Recordset")

' open database object, we are going to get the data for any items that
relate to the current category
objGMC.Open "SELECT cc.id,cc.name,cc.expand,cc.relatesTO,nt.text FROM
category cc JOIN notes nn ON cc.id=nn.relatesTO JOIN notestext nt ON
nn.id=nt.id WHERE relatesTO=" & vGMC, oConn
' loop through the recordset
do while not objGMC.EOF
response.write "<br>" & objGMC("name") & " NOTE: " &
objGMC("text")
' check: do we need to
if objGMC("expand")=true then
' expanding, so do the recursion thing...
call GetMoreCategories(objGMC("id"))
end if
objGMC.movenext
loop
objGMC.close
end sub


' main body: find all categories that relate to 0
oRs.Open "SELECT cc.id,cc.name,cc.expand,cc.relatesTO,nt.text FROM category
cc JOIN notes nn ON cc.id=nn.relatesTO JOIN notestext nt ON nn.id=nt.id
WHERE relatesTO=0", oConn
' loop through the recordset
do while not oRs.EOF
response.write "<br>" & oRs("name") & " NOTE: " & oRs("text")
' check: do we need to
if oRs("expand")=true then
' expanding, so do the recursion thing...
call GetMoreCategories(oRs("id"))
end if
oRs.movenext
loop
oRs.close


Basically, in pseudocode, what we are doing:

- create global vairables for oRs and oConn
- LOOP
- opening the category table and getting all items that relateTO 0 (top
level categories) and any notes associated with that category
- write the name of the category out
- check: does "EXPAND" = true? If yes then call the subroutine
"GetMoreCategories" and pass the category ID
- REPEAT LOOP until there are no more categories that relateTO 0


SUBROUTINE: GetMoreCategories
- accept the passed CategoryID and store it in the variable vGMC
- create local variable for objGMC
- LOOP
- opening the category table and getting all items that relateTO the
category ID passed to vGMC and any notes associated with that category
- write the name of the category out
- check: does "EXPAND" = true? If yes then call the subroutine
"GetMoreCategories" and pass the category ID [RECURSION]
- REPEAT LOOP until there are no more categories that relateTO the category
ID passed to vGMC


Hope thats some help

Clint

[Dragonhunter]

Bob,

The code works perfectly! Thank you! Somewhat surprisingly, I was able to make
the change you suggested, destroying the subcategories table and instead giving
each category a parent (and getting it to work again!) I sent you an email
btw...

Dragonhunter


Bob Barrows wrote:

> See below:
>
> Dragonhunter wrote:

> > "Bob Barrows [MVP]" wrote:
> >

> >> Can you provide a few rows of sample data for each table (in tabular
> >> form) and the desired result? Descriptions can only go so far, plus
> >> it will help us get to the correct solution if we are discussing the
> >> data that you will be using.
> >>
> >> Bob Barrows

> >
> > Sure, here is my database and an example of the web page I want to
> > create to output the data. I structured the database to normalize
> > after being explained the concept on this group (thanks!). So, how
> > do I do this recursive displaying of data properly using ASP? I
> > think I'm pretty stuck on the SQL..., particularly when I try to do
> > the "join" method instead of nested loops. Any help would be
> > appreciated!
> >
> > Thanks,
> > Dragonhunter
> >
> >

>
> I would probably have added a parent_id column to this table. The
> subcategories table is really not needed. The expand field is really not
> needed either, although it will make the subsequent coding easier. I'm not
> sure this coding ease is worth the headaches that guaranteeing this field
> will contain the correct value will cause.
> Like this
>

> > category
> > id--name--------------------parent_id
> > 1 Favorite soundtracks 2
> > 2 Favorite music
> > 3 Pets
> > 4 Favorite quotes
> > 5 Ordered soundtracks

>
> However, I will assume you do not make this change
>

> >
> > note
> > id--text
> > 1 Star Wars
> > 2 Indiana Jones
> > 3 Fluffy the cat
> > 4 Goldfish 1
> > 5 "To make an omelette you have to break some eggs"
> > 6 "Nested loops are the root of all evil"
> > 7 Batman
> > 8 Predator
> > 9 "it takes one to know one!"
> > 10 Les miserables
> >
> > note_categories
> > note_id--category_id
> > 1 1
> > 1 5 <--this is a note in two categories
> > 2 1
> > 3 3
> > 4 3
> > 5 4
> > 6 4
> > 7 1
> > 8 1
> > 9 4
> > 10 1
> > 7 5 <--this is a note in two categories
> >
> > Subcategories
> > child_id--parent_id
> > 1 2
> >
> >
> > Webpage=========================================== ===
> > + is expandable to show things under it
> > - means un-expand it (and store +/- state in a database)
> >
> >
> > -Favorite music
> > -Favorite soundtracks
> > Star Wars
> > Indiana Jones
> > Batman
> > Predator
> > Les miserables
> > +Pets <-- none show because "expand" field is false
> > -Favorite quotes
> > "To make an omelette you have to break some eggs"
> > "Nested loops are the root of all evil"
> > "it takes one to know one!"
> > -Ordered soundtracks
> > Star Wars
> > Batman

>
> OK. I will take care of showing you how to do the nesting. You will need to
> take care of the client-side coding needed to expand and collapse the nodes.
> You can ask about that in a client-side code newsgroup (newsgroups
> containing "dhtml" in their title or one of the .scripting newsgroups). I
> will put the + and - characters in spans whose onclick events can be used to
> expand or collapse a node.
>
> To start, let's order the categories by parent_id, then child id, so the
> master categories will be first:
> SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id), c.name, c.expand
> FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id
> ORDER BY sc.parent_id, c.id;
> produces this resultset:
>
> id parent_id name expand
> 2 0 Favorite music -1
> 3 0 Pets 0
> 4 0 Favorite quotes -1
> 5 0 Ordered soundtracks -1
> 1 2 Favorite soundtracks -1
>
> Now let's get the notes:
> SELECT n.id, c.id, n.text, null AS Expr1
> FROM (category AS c
> INNER JOIN note_categories AS nc ON c.id = nc.category_id)
> INNER JOIN [note] AS n ON nc.note_id = n.id
> ORDER BY c.id, n.id;
> which gives us this result:
> n.id c.id text
> Expr1
> 1 1 Star Wars
> 2 1 Indiana Jones
> 7 1 Batman
> 8 1 Predator
> 10 1 Les miserables
> 3 3 Fluffy the cat
> 4 3 Goldfish 1
> 5 4 "To make an omelette you have to break some eggs"
> 6 4 "Nested loops are the root of all evil"
> 9 4 "it takes one to know one!"
> 1 5 Star Wars
> 7 5 Batman
>
> Let's union these results together:
>
> SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id) as parent_id,
> c.name, c.expand, "cat" As Type
> FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id
> UNION ALL SELECT n.id, c.id, n.text, Null, "note"
> FROM (category AS c
> INNER JOIN note_categories AS nc ON c.id = nc.category_id)
> INNER JOIN [note] AS n ON nc.note_id = n.id
> ORDER BY parent_id, id;
>
> giving these results:
> qTree
> id parent_id name
> expand type
> 2 0 Favorite music
> True cat
> 3 0 Pets
> False cat
> 4 0 Favorite quotes
> True cat
> 5 0 Ordered soundtracks
> True cat
> 1 2 Favorite soundtracks
> True cat
> 1 1 Star Wars
> note
> 2 1 Indiana Jones
> note
> 7 1 Batman
> note
> 8 1 Predator
> note
> 10 1 Les miserables
> note
> 3 3 Fluffy the cat
> note
> 4 3 Goldfish 1
> note
> 5 4 "To make an omelette you have to break some eggs"
> note
> 6 4 "Nested loops are the root of all evil"
> note
> 9 4 "it takes one to know one!"
> note
> 1 5 Star Wars
> note
> 7 5 Batman
> note
>
> Copy the above sql into the SQL View window of an Access Query Builder and
> save it as qTree after running it to verify the results are correct.
>
> In your asp page, do this (as a bonus, I included the client-side code to
> make the nodes expand and collapse):
> <%
> dim cn, rs, sSQL
> set cn=createobject("adodb.connection")
> cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("db7.mdb")
>
> set rs=CreateObject("adodb.recordset")
> rs.CursorLocation = 3 'adUseClient
> rs.LockType = 1 'adLockReadOnly
> cn.qTree rs
> 'disconnect the recordset:
> set rs.ActiveConnection=nothing
> cn.Close: set cn = nothing
>
> if rs.EOF then
> Response.Write "no records returned"
> else
> 'stop
> WriteChildDivs rs.Clone,rs(1).Value,"cat",-10
> rs.Close:set rs = nothing
> end if
>
> Sub WriteChildDivs(pRS,pID,byval pType,pIndent)
> dim bNextLvlVisible
> pRS.Filter="parent_id=" & pID & " and type='" & pType & "'"
> if pRS.eof then
> ptype = "note"
> pRS.Filter="parent_id=" & pID & " and type='" & pType & "'"
> end if
> do until pRS.EOF
> Response.Write "<div style=""margin-left:" & _
> pIndent + 10 & "px"">"
>
> select case pRS(3).value
> case true
> Response.Write "<span onclick=""handleclick();""" & _
> " style=""cursor:hand"">"
> Response.Write "-</span>"
> bNextLvlVisible=true
> case false
> Response.Write "<span onclick=""handleclick();"" & _
> " style=""cursor:hand"">"
> Response.Write "+</span>"
> bNextLvlVisible=false
> case else
> bNextLvlVisible=true
> end select
> Response.Write pRS(2).Value
> if pRS(4).value="cat" then
> Response.Write "<div style=""display:"
> if bNextLvlVisible then
> Response.Write "block"">"
> else
> Response.Write "none"">"
> end if
> WriteChildDivs pRS.clone, pRS(0).Value,pRS(4).value, pIndent + 10
> Response.Write "</div>"
> end if
> Response.Write "</div>"
> pRS.MoveNext
> loop
> set pRS=nothing
> End Sub
> %>
> <HTML>
> <HEAD>
> <SCRIPT LANGUAGE=javascript>
> function handleclick()
> {
> var oSpan = event.srcElement
> var oDiv=oSpan.nextSibling.nextSibling
> switch (oSpan.innerText)
> {
> case "+":
> oDiv.style.display="block"
> oSpan.innerText = "-"
> break
> case "-":
> oDiv.style.display="none"
> oSpan.innerText = "+"
> }
> }
> </SCRIPT>
>
> </HEAD>
> <BODY>
> </BODY>
> </HTML>
>
> Which looks like this in the browser
>
> -Favorite music
> -Favorite soundtracks
> Star Wars
> Indiana Jones
> Batman
> Predator
> Les miserables
> +Pets
> -Favorite quotes
> "To make an omelette you have to break some eggs"
> "Nested loops are the root of all evil"
> "it takes one to know one!"
> -Ordered soundtracks
> Star Wars
> Batman
>
> HTH.
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"