Recursive Database Access 2

hc98br · Sep 6, 2004

I have a list of records in a table, each with an ID and each with a parentId, this is the record id of another record in the table.

I want to draw a heretical tree of the data, so recursive function is needed.

My first though looked like it would work but I called the database within the recursive function, so I just get "Operation is not allowed when the object is open." on the second call - of course.

What is the best way to tackle this, I've found code that dumps the database into arrays first, then search through on each call of the function, but this seem clumsy - is it the best way though?

Thanks

Ben.

vbkris · Sep 6, 2004

to track from the last level to the first level (not viceversa), use a while loop

TheTree=""
parentid=rs("parentid") 'parent id of the record whose parent is to be traced
while parentid<>"0" 'assuming top level's parentid is 0
sql="select * from table where id="&parentid
rs1.open sql,con
TheTree=TheTree & ">>" &rs1("parent_id")
parentid=rs1("parent_id")
rs1.close
wend
response.write TheTree

Known is handfull, Unknown is worldfull

hc98br · Sep 6, 2004

Thanks for that, but I don't think it will not do the job.

I need to build a full list from the top down, eg:
[tt]
drinks
coke
orange
tea
english breakfast
assam
food
chips
fish
[/tt]

etc.

Tarwn · Sep 7, 2004

I think I gather how your data is organized. It sounds like everything is one table, you have a set of data that is parent data (maybe with the parent field null or set to 0) then all the other records refer back to other records as their parents.
Dynamic levels makes thing difficult, but you could just do something along the lines of:

Code:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "your connection string"

'--- Function OutputChildren
' Accepts the parent id as it's only argument
Function OutputChildren(parentID)
   'get all children for the selected ID
   Dim rs_children
   Set rs_children = objConn.Execute "SELECT TextField, IdField FROM YourTable WHERE ParentField = " & parentID " ORDER BY TextField"

   'output the children in a loop
   If Not rs_children.EOF Then rs_children.MoveFirst
   Do Until rs_children.EOF
      'multiple nested divs that will have margins defined in CSS to make dynamically indent more as nested deeper
      Response.Write "<div class=""node""><div class=""node_text"">" & rs_children("TextField") & "</div>"
      Response.Write "<div class=""node_children"">"
      'recursive call to find all children of this node
      OutputChildren rs_children("IdField")
      Response.Write "</div></div>"
      rs_children.MoveNext
   Loop
End Function
%>
<html>
<heaD>
<style>
.node{
   margin-left: 10px;
}
</style>
</head>
<body>
<%
'start by displaying highest level - pretending they have parentID's set to 0
OutputChildren 0
%>
</body>
</html>

The only thing that concerns me is that VBScript is painfully mesy about it's handling of variable scope, so it may very well not handle this recursion correctly. It should be making a new local recordset each time it is called without effecting any others that are in use higher up the recursive tree, but it may very well decide hurt itself and ignore scope, overwritng higher recordsets with lower calls. I don't think this will happen but if it does there are other possibilities that wouldn't be to terribly difficult to implement.

-T

JSpicolli · Sep 7, 2004

What is the Parent / Child Relationship?

hc98br · Sep 8, 2004

Tarwn - thanks a lot for that code, looks really good, except - I get an error

"Expected end of statement"

After execute on the line starting, Set rs_children = objConn.Execute "...".

I've not done much db access this way before (normally ADODB.Recordset) so am a little unsure!

Thanks.

P.S. JSpicolli, not sure what you mean, can you expand your question? - Thanks.

http://www.ben-robinson.com

Tarwn · Sep 8, 2004

Heh, thats alright it's my fault anyways, I missed an ampersand

Generally when you see Expected End Of Statement errors it means you either have to many arguments in a function call or you have an error in a string concatenation (which is the case here):

Code:

Set rs_children = objConn.Execute "SELECT TextField, IdField FROM YourTable WHERE ParentField = " & parentID [highlighti]&[/highlight] " ORDER BY TextField"

Sorry about that, I didn't even see it missing

-T

Tarwn · Sep 8, 2004

argh, typos:

Code:

Set rs_children = objConn.Execute "SELECT TextField, IdField FROM YourTable WHERE ParentField = " & parentID [highlight]&[/highlight] " ORDER BY TextField"

hc98br · Sep 8, 2004

Thank you very much for your time - got it working.

As it happened it wasn't the ampersand (I'd tried that already), it needed brakets () around the string - not sure why - maybe just my server being funny.

Like the DIVs, nice idea!

Cheers

Ben.

http://www.ben-robinson.com

Tarwn · Sep 8, 2004

I can't claim the credit for that unfortunatly, back when I wrote my first Treeview I was curious about how MS had implemented it for their default XML stylesheet (IE 5.0 days I think), so I went and dug around and found it, the margins were one of the things I ended up learning while reading through it. I actually learned a lot about CSS, javascript, and divs from that stylesheet. Probably formed the basis for the knowledge I later used when writing my own treeview objects/scripts, though i didn't feel it necessary to credit them as there was a 4 or 5 year gap between me reading theirs and writing my own (and regulars ought to know my lack of memory by now

)

Glad to have helped out,
-T

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Recursive Database Access 2

hc98br

ISP

vbkris

Programmer

hc98br

ISP

Tarwn

Programmer

JSpicolli

Programmer

hc98br

ISP

Tarwn

Programmer

Tarwn

Programmer

hc98br

ISP

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor