What's wrong with this ASP recursive function?

Running out of SQL Connections?

You are dealing with so many layers there (Response.Write for the client, the ASP for the server, and the database) that its not surprising that there are problems.

Perhaps you can post some details about the error?

hard to tell without more description of how it breaks, but you are not using intLevel for anything.

try declaring the variables as local using a DIM statement within the function definition:

Function GetChilds(ParentID, intLevel)
Dim rsData, sSQL
Set ...

Edit: Ok, I try to be more explicit.

My understanding is that since rsData is not declared by DIM, it is not a local variable, but a global var. Therefore, if you loop through the WHILE statement, you reach the .Eof of the inner-most rsData recordset. You return from the recursive function call, and the next step is again a rsData.MoveNext, which fails.

Please correct me if rsData is indeed local.

How does it break?

My guess is that after a certain number of recursions you're probably getting a Stack Overflow (ironic) because you're not allocating too many RecordSets.

In each call you open a new connection to the database and you don't close it before opening a new one.

Not that this is actually a solution to the recursion issue, but it might be better for you to work out an SQL statement that returns all the information in a hierarchical format, rather than making recursive calls to your database.

Come to think of it though, it may be because you have too many concurrent db connections. You continually open, but aren't going to start closing until your pulling out of your recursive loop.

If you need recursion such as this I would personally put the recursion into a stored procedure and handle that processing on the database side in order to avoid opening multiple connections. If you are using mssql2005 look into something called Common Table Expressions (CTE), they make recursion easy. There are other ways to implement recursion with other RDBMS's.

Based on the sugestions I will atempt to move the query into a CTE (common table expression) when I find a good tutorial on how to do that. For now and as a quick and dirty fix, I have changed the code as follows:

Function GetChilds(ParentID, intLevel)
        'Open my Database Connection and Query the current Parent ID
        Set rsData= Server.CreateObject("ADODB.Recordset")
        sSQL = "SELECT AcctID, ParentID FROM Accounts WHERE ParentID='" & ParentID &"'"
        rsData.Open sSQL, conDB, adOpenKeyset, adLockOptimistic
        'If the Record Set is not empty continue
        If Not IsRSEmpty(rsData) Then
            Dim myAccts()
            ReDim myAccts(rsData.RecordCount)
            Dim i
            i = 0
            Do Until rsData.EOF
                Response.Write "Account ID: " & rsData("AcctID") & " ParentID: " & rsData("ParentID") & "<br />"
                'Add the Childs of the current Parent ID to an array.
                myAccts(i) = rsData("AcctID")
                i = i + 1
                rsData.MoveNext
            Loop
            'Close the SQL connection and get it ready for reopen. (I know not the best way but hey I am just learning this stuff)
            rsData.close: set rsData = nothing
            'For each Child found in the previous query, now lets get their childs.
            For i = 0 To UBound(myAccts)
                Call GetChilds(myAccts(i), intLevel + 1)
            Next
        End If
    End Function

    Call GetChilds(1,0)

I have working code with the same scenario.

I use a clientside cursor

...
rsData.CursorLocation = adUseClient
rsData.Open sSQL, conDB, adOpenKeyset, adLockOptimistic
rsData.ActiveConnectcion = Nothing
...

as pointed out in other responses, this is not very efficient, I use it only in an admin interface where the code is called infrequently and speed is not as critical.

I would not use such a recursive process in a regular web page. Either rework the code to get all data in one call from the database, or make the call once and save it to a local array and save the array in an application variable.