Excel VBA - 从记录集创建树视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1233541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Create Treeview from recordset
提问by Chris McCall
I have a SQL 05 Stored Procedure that brings back around 180 rows with the structure:
我有一个 SQL 05 存储过程，它带回了大约 180 行的结构：
ID | Name | ParentId.
身 | 姓名 | 父 ID。
What I would like to do is create a Treeview based on the records returned. Below is a sample I adapted from elsewhere on the forum (here)
我想做的是根据返回的记录创建一个 Treeview。以下是我从论坛其他地方改编的示例（此处）
I nearly have it doing what I want but not quite.
我几乎让它做我想做的事，但不完全。
This is the problem I have, The root is added ok. The next layer down is added ok. However, it does not know what the next ParentId should be. Should I just incremement a variable and try and find matches? I guess I should include that in the top method.
这就是我遇到的问题，root添加好了。添加下一层就ok了。但是，它不知道下一个 ParentId 应该是什么。我应该增加一个变量并尝试查找匹配项吗？我想我应该将其包含在 top 方法中。
Hopefully I am not far off and need a shove in the right direction,
希望我离得不远，需要朝正确的方向推动，
Many Thanks in advance Michael
非常感谢迈克尔
As a final thing, the BOF AND EOF checks are there in the loop as I seem to have had a lot of errors being thrown if I don't have it.
最后，循环中存在 BOF 和 EOF 检查，因为如果我没有它，我似乎会抛出很多错误。
Private Sub MakeTree(ByVal rs As ADODB.Recordset)
rs.MoveFirst
Do Until rs.EOF
If (IsNull(rs.Fields("ParentID"))) Then
Call TVFunds.Nodes.Add(, , "Key" + CStr(rs.Fields("Id")), rs.Fields("Name"))
Call MsgBox("Key" + CStr(rs.Fields("Id")) + " " + rs.Fields("Name"), vbInformation, "Added Root")
Else
DrawNode rs, rs.Fields("ParentID"), rs.Fields("ID")
End If
If rs.BOF <> True And rs.EOF <> True Then
rs.MoveNext
End If
Loop
End Sub
Private Sub DrawNode(ByRef r As ADODB.Recordset, ByRef pId As Integer, ByRef Id As Integer)
r.MoveFirst
Do Until r.EOF
If (r.Fields("ParentId") = pId And r.Fields("Id") = Id) Then
Call TVFunds.Nodes.Add("Key" + CStr(r.Fields("ParentId")), tvwChild, "Key" + CStr(r.Fields("Id")), r.Fields("Name"))
Call MsgBox("Key" + CStr(r.Fields("ParentId")) + " Key" + CStr(r.Fields("Id")) + " " + r.Fields("Name"), vbInformation, "Added")
Id = Id + 1
DrawNode r, pId, Id
End If
If r.BOF <> True And r.EOF <> True Then
r.MoveNext
End If
Loop
End Sub
回答by Chris McCall
First things first: Do Until... loops always execute at least once. So, if the recordset has no rows, it will still enter the loop. That's why you were getting those errors without the EOF/BOF checks.
首先要做的事情是：直到……循环总是至少执行一次。所以，如果记录集没有行，它仍然会进入循环。这就是为什么您在没有 EOF/BOF 检查的情况下收到这些错误的原因。
Change those Do Until... loops to Do While... loops like this:
将那些 Do until... 循环更改为 Do While... 循环，如下所示：
Do While Not rs.EOF
If (IsNull(rs.Fields("ParentID"))) Then
Call TVFunds.Nodes.Add(, , "Key" + CStr(rs.Fields("Id")), rs.Fields("Name"))
Call MsgBox("Key" + CStr(rs.Fields("Id")) + " " + rs.Fields("Name"), vbInformation, "Added Root")
Else
DrawNode rs, rs.Fields("ParentID"), rs.Fields("ID")
End If
rs.MoveNext
Loop
