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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:39:58  来源:igfitidea点击:

Excel VBA - Create Treeview from recordset

excel-vbaexceltreeviewrecursionvba

提问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