这个ASP递归函数有什么问题？

When I call this function, everything works, as long as I don't try to recursively call the function again. In other words if I uncomment the line:

当我调用这个函数时，一切都有效，只要我不尝试再次递归调用该函数。换句话说，如果我取消注释该行：

GetChilds rsData("AcctID"), intLevel + 1

Then the function breaks.

然后功能中断。

<%
    Function GetChilds(ParentID, intLevel)
        Set rsData= Server.CreateObject("ADODB.Recordset")
        sSQL = "SELECT AcctID, ParentID FROM Accounts WHERE ParentID='" & ParentID &"'"
        rsData.Open sSQL, conDB, adOpenKeyset, adLockOptimistic
        If IsRSEmpty(rsData) Then
            Response.Write("Empty")
        Else
            Do Until rsData.EOF
                Response.Write rsData("AcctID") & "<br />"
                'GetChilds rsData("AcctID"), intLevel + 1 
                rsData.MoveNext
            Loop
        End If
        rsData.close: set rsData = nothing
    End Function

    Call GetChilds(1,0)
%>

*Edited after feedback

*在反馈后编辑

Thanks everyone,

感谢大家，

Other than the usual error:

除了通常的错误：

Error Type: (0x80020009) Exception occurred.

I wasn't sure what was causing the problems. I understand that is probably due to a couple of factors.

我不确定是什么导致了这些问题。我知道这可能是由于几个因素造成的。

Not closing the connection and attempting to re-open the same connection.
未关闭连接并尝试重新打开相同的连接。
To many concurrent connections to the database.
到许多并发连接到数据库。

The database content is as follows:

数据库内容如下：

AcctID | ParentID
1        Null
2        1
3        1
4        2
5        2
6        3
7        4

The idea is so that I can have a Master Account with Child Accounts, and those Child Accounts can have Child Accounts of their Own. Eventually there will be Another Master Account with a ParentID of Null that will have childs of its own. With that in mind, am I going about this the correct way?

我的想法是，我可以拥有一个拥有子账户的主账户，这些子账户可以拥有自己的子账户。最终会有另一个MasterID帐户，其ParentID为Null，将拥有自己的子项。考虑到这一点，我是否正确的方式？

Thanks for the quick responses.

感谢您的快速回复。

Thanks everyone,

感谢大家，

Other than the usual error:

除了通常的错误：

Error Type: (0x80020009) Exception occurred.

错误类型：（0x80020009）发生异常。

I wasn't sure what was causing the problems. I understand that is probably due to a couple of factors.

我不确定是什么导致了这些问题。我知道这可能是由于几个因素造成的。

Not closing the connection and attempting to re-open the same connection.
未关闭连接并尝试重新打开相同的连接。
To many concurrent connections to the database.
到许多并发连接到数据库。

The database content is as follows:

数据库内容如下：

AcctID | ParentID
1        Null
2        1
3        1
4        2
5        2
6        3
7        4

Thanks for the quick responses.

感谢您的快速回复。

10 个解决方案

#1

Look like it fails because your connection is still busy serving the RecordSet from the previous call.

看起来它失败了，因为您的连接仍在忙于从上一次调用中提供RecordSet。

One option is to use a fresh connection for each call. The danger there is that you'll quickly run out of connections if you recurse too many times.

一种选择是为每个呼叫使用新连接。存在的危险是，如果你进行多次递击，你将很快耗尽连接。

Another option is to read the contents of each RecordSet into a disconnected collection: (Dictionary, Array, etc) so you can close the connection right away. Then iterate over the disconnected collection.

另一种选择是将每个RecordSet的内容读入断开的集合:(字典，数组等），以便您可以立即关闭连接。然后迭代断开连接的集合。

If you're using SQL Server 2005 or later there's an even better option. You can use a CTE (common table expression) to write a recursive sql query. Then you can move everything to the database and you only need to execute one query.

如果您使用的是SQL Server 2005或更高版本，则可以选择更好的选项。您可以使用CTE（公用表表达式）来编写递归SQL查询。然后，您可以将所有内容移动到数据库，只需执行一个查询。

Some other notes:
ID fields are normally ints, so you shouldn't encase them in ' characters in the sql string.

其他一些注意事项：ID字段通常是整数，因此您不应将它们包含在sql字符串中的字符中。

Finally, this code is probably okay because I doubt the user is allowed to input an id number directly. However, the dynamic sql technique used is very dangerous and should generally be avoided. Use query parameters instead to prevent sql injection.

最后，这段代码可能还可以，因为我怀疑用户是否可以直接输入id号。但是，使用的动态sql技术非常危险，通常应该避免使用。使用查询参数来防止sql注入。

I'm not too worried about not using intLevel for anything. Looking at the code this is obviously an early version, and intLevel can be used later to determine something like indentation or the class name used when styling an element.

我不太担心不使用intLevel做任何事情。查看代码这显然是一个早期版本，并且稍后可以使用intLevel来确定缩进或在为元素设置样式时使用的类名。

#2

Running out of SQL Connections?

用完SQL连接？

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.

您正在处理那么多层（客户端的Response.Write，服务器的ASP和数据库），这些问题并不奇怪。

Perhaps you can post some details about the error?

也许您可以发布有关错误的一些细节？

#3

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

很难说没有它如何破坏的描述，但你没有使用intLevel做任何事情。

#4

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.

我的猜测是，经过一定数量的递归后，你可能会得到Stack Overflow（具有讽刺意味），因为你没有分配太多的RecordSet。

#5

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

在每次调用中，您都会打开一个与数据库的新连接，并且在打开新数据库之前不要将其关闭。

#6

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.

并不是说这实际上是递归问题的解决方案，但是你可能更好地编写一个以分层格式返回所有信息的SQL语句，而不是对数据库进行递归调用。

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.

考虑一下，可能是因为你有太多的并发数据库连接。你不断打开，但在你退出递归循环之前不会开始关闭。

#7

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

尝试使用函数定义中的DIM语句将变量声明为local：

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.

我的理解是，由于DIM不声明rsData，它不是局部变量，而是全局变量。因此，如果循环遍历WHILE语句，则会到达最内层rsData记录集的.Eof。您从递归函数调用返回，下一步再次是rsData.MoveNext，它失败。

Please correct me if rsData is indeed local.

如果rsData确实是本地的，请纠正我。

#8

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.

如果您需要这样的递归，我会亲自将递归放入存储过程并在数据库端处理该处理，以避免打开多个连接。如果您正在使用mssql2005查看名为Common Table Expressions（CTE）的内容，它们可以轻松地进行递归。还有其他方法可以实现与其他RDBMS的递归。

#9

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:

基于sugestions，当我找到一个关于如何做到这一点的好教程时，我将尝试将查询移动到CTE（公用表表达式）中。就目前而言，作为一个快速而肮脏的修复，我更改了代码如下：

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)

#10

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.

我不会在常规网页中使用这样的递归过程。重做代码以从数据库中获取一次调用中的所有数据，或者进行一次调用并将其保存到本地数组并将数组保存在应用程序变量中。

#1