Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Database - recursive function calls - how would you do this?

Reply
Thread Tools

Database - recursive function calls - how would you do this?

 
 
Steven Burn
Guest
Posts: n/a
 
      10-21-2005
..:: The Specs:

MS Access 2000 (host charges extra for SQL/MySQL)
MS Windows Server 2003 (prod) / MS XP SP1 (dev)

..:: The setup:

The database has been setup with two tables;

tblDownloads
tblCatagories

Each "download" consists of a catagory field that corresponds to fldID in
tblCatagories. Each catagory, where the catagory is a parent, has a parent
field of value: 0 and each child, has a field with a value corresponding to
the fldID of it's parent. So for example;

**********************
fldID / fldName / fldParent
**********************

1 Multimedia 0
2 Audio 1
3 Video 1

Now, each child can have children of it's own (I've not put a drill level on
it as the app I'm writing is for a friend and he's specifically asked for
"no limit" on how low it can go), so for example (the following represents
how tblCatagories is setup);

**********************
fldID / fldName / fldParent
**********************

1 Multimedia 0
2 Audio 1
3 Video 1
4 MP3 Players 2
5 Skinnable 4
6 Add-ons 4
7 Misc 6

..:: The problem:

What I'm basically needing to do, is count the downloads in the current
catagory, so for example, in the case where the selected catagory was MP3
Players, it would run through "MP3 Players", "Skinnable", "Add-ons" and
"Misc" and count each download from tblDownloads, that corresponds to one of
those catagories. In the case where the catagory selected was null (i.e.
default downloads page), it would show all downloads for Multimedia and it's
children.

The code I've written will get the PC (parent catagory) and it's children,
but will not then progress to get the childrens children.

I search several places for recursive queries (or alternate ways to do what
I am needing) and unfortunately, I've thus far been unsuccessful in finding
one that works for my situation (they all either assume to be working with
SQL, or that there's a limit on how far down it can go, or uses INNER JOIN
(assumes the values are in two different tables, which is not the case
here)). Had this been VB, I could have simply used a GoTo [LABEL] where
child catagories were present, unfortunately ASP does not support this.

No error is returned to the server's logs or the event viewer, and no error
is returned to the client browser (client debug is turned on and the browser
is set to display the real error, rather than the horrid 500 error), the
page (and subequently, the server) stops responding. I've been trying to
figure this one out for just over 3 days now and am at my wits end (I know
recursive queries work as I've used them before, so why isn't it in this
case?).

..:: The code:

The functions involved are;

IsParentCatagory (returns boolean value)
HasChildren (returns boolean value)
GetChildren (returns long)
GetParent (returns long)
GetDownloadCount (returns long)

The function thats called to get the children is below. What I tried doing
was, looping through the children returned (after closing the existing rs of
course), and re-calling the function for each child (to then get the
childrens children and so on). Unfortunately, this does not apparently want
to work.

Sub CountEm(lCatID)
'// Init call for current catagory
GetChildren lCatID
'// Debugging (checked that there was actually something to process)
'// Response.Write "sKids: " & sKids: Response.End
arrChildren = Split(sKids, ",")
For x = LBound(arrChildren) To UBound(arrChildren)
'// Tried without the Call aswell, just incase
Call GetChildren(arrChildren(x))
Next
End Sub

Function GetChildren(lID)
Set oDB = Connect(DB_Downloads, 1)
sSQL = "Select fldID, fldParent From tblCatagories Where fldParent = " &
lID
Set rRst = oDB.Execute(sSQL)
If rRst.EOF Then rRst.Close: GetChildren = 0: Exit Function
Do Until rRst.EOF
'// The following If/End If is on one line,
'// just re-wrote it here incase of linewrap
If Len(sKids) = 0 Then
sKids = rRst("fldID")
Else
sKids = sKids & "," & rRst("fldID")
End If
rRst.MoveNext
Loop
rRst.Close: Set rRst = Nothing
'// Once returned, a Do/Loop is performed to run through
'// the children and count their downloads using GetDownloadCount
GetChildren = sKids
End Function

What I'm basically wanting to do is recurse to the lowest child catagories
(where HasChildren = False), regardless of how far down that actually is,
and return the download count for each child (upto and including the top
level parent). phpBB does this with ease for their downloads catagory, so
I'm almost positive this is relatively simple to do, but for the life of me,
I can't figure out where I am going wrong (and can't understand PHP or would
have took a look at their coding for hints). As an FYI, I also tried making
the above function a sub (since sKids is a public string anyway) and that
didn't help either.

Does anyone have any suggestions please?

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-21-2005
Steven Burn wrote:
> .:: The Specs:
>
> MS Access 2000 (host charges extra for SQL/MySQL)
> MS Windows Server 2003 (prod) / MS XP SP1 (dev)
>
> .:: The setup:
>
> The database has been setup with two tables;
>
> tblDownloads
> tblCatagories
>
> Each "download" consists of a catagory field that corresponds to
> fldID in tblCatagories. Each catagory, where the catagory is a
> parent, has a parent field of value: 0 and each child, has a field
> with a value corresponding to the fldID of it's parent. So for
> example;
>
> **********************
> fldID / fldName / fldParent
> **********************
>
> 1 Multimedia 0
> 2 Audio 1
> 3 Video 1
>
> Now, each child can have children of it's own (I've not put a drill
> level on it as the app I'm writing is for a friend and he's
> specifically asked for "no limit" on how low it can go), so for
> example (the following represents how tblCatagories is setup);


http://groups.google.com/group/micro...ee952f05404f03

Nested Sets is your answer.

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.


 
Reply With Quote
 
 
 
 
Steven Burn
Guest
Posts: n/a
 
      10-21-2005
Nice one Bob, thankyou ;o)

Seeing how simple the change to the query required was, I am now going to
proceed to kick myself ....

New query:

sSQL = "Select fldID, fldParent From tblCatagories Where fldParent = " &
lID & " OR fldParent IN (Select fldID from tblCatagories Where fldParent = "
& lID & ")"

Works like a charm )

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Steven Burn wrote:
> > .:: The Specs:
> >
> > MS Access 2000 (host charges extra for SQL/MySQL)
> > MS Windows Server 2003 (prod) / MS XP SP1 (dev)
> >
> > .:: The setup:
> >
> > The database has been setup with two tables;
> >
> > tblDownloads
> > tblCatagories
> >
> > Each "download" consists of a catagory field that corresponds to
> > fldID in tblCatagories. Each catagory, where the catagory is a
> > parent, has a parent field of value: 0 and each child, has a field
> > with a value corresponding to the fldID of it's parent. So for
> > example;
> >
> > **********************
> > fldID / fldName / fldParent
> > **********************
> >
> > 1 Multimedia 0
> > 2 Audio 1
> > 3 Video 1
> >
> > Now, each child can have children of it's own (I've not put a drill
> > level on it as the app I'm writing is for a friend and he's
> > specifically asked for "no limit" on how low it can go), so for
> > example (the following represents how tblCatagories is setup);

>
>

http://groups.google.com/group/micro.../browse_frm/th
read/d3f5a7f39ed08d4f/b8eee952f05404f0?lnk=st&q=%22Bob+Barrows%22+recurs ive+
query&rnum=1&hl=en#b8eee952f05404f03
>
> Nested Sets is your answer.
>
> 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.
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Re: tmobile block sip calls , is there a port change setting at pbxes.orgthat would enable the voip calls to come through via mobile data? David Woolley UK VOIP 0 07-13-2011 08:04 PM
Re: tmobile block sip calls , is there a port change setting atpbxes.org that would enable the voip calls to come through via mobile data? Brian A UK VOIP 0 07-12-2011 12:06 AM
Two recursive calls inside of a recursive function n00m C++ 12 03-13-2008 03:18 PM
This function has an onClick event that calls a function that calls This function Bob Javascript 5 10-24-2006 04:11 PM



Advertisments