VB.NET基于SQL Server数据填充TreeView时遇索引越界异常

DevOps 小满

2026-8-3

VB.NET TreeView填充时“指定的参数超出了有效值范围”异常排查与解决

开发VB.NET项目时，通过Populate_Tree()方法从SQL Server数据库取数据填充TreeView控件，运行时抛出**“指定的参数超出了有效值范围。参数名: Index”**异常，原代码如下：

Private Sub Populate_Tree()
    Try
        Dim dt As New DataTable
        Dim str_Sql As String
        If cls_GeneralVariables.Group_Name = "Data Entry Operators" Then
            str_Sql = " EXEC sp_GeneratorFueling_PopulateTree_Get  " & cls_GeneralVariables.LoginEmployeeId
        Else
            str_Sql = " EXEC sp_GeneratorFueling_PopulateTree_Get 0 "
        End If
        dt = cls_GeneralFunction.Get_DataTable(str_Sql, cls_GeneralVariables.Sql_Connection)
        bln_Populate = False
        If tv_SurveyDate.Nodes.Count > 0 Then
            tv_SurveyDate.Nodes.Clear()
        End If
        If dt.Rows.Count > 0 Then
            Dim iLoop As Integer
            Dim pNode, pNode_Value, pNode1 As Integer
            Dim pYear As String = Nothing

            For iLoop = 0 To dt.Rows.Count - 1

                With dt.Rows(iLoop)
                    If pYear <> .Item("Year") Then
                        '--- Add New Year Node
                        pYear = .Item("Year")
                        tv_SurveyDate.Nodes.Add(.Item("Year"))
                        If iLoop > 0 Then
                            pNode = pNode + 1
                        End If
                        ''--- Add Child Nodes
                        If pNode_Value <> .Item("MONTH") Then
                            pNode_Value = .Item("MONTH")
                            tv_SurveyDate.Nodes(pNode).Nodes.Add(Format(.Item("MonthTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(tv_SurveyDate.Nodes(pNode).Nodes.Count - 1).Tag = .Item("MONTH")
                            'tv_SurveyDate.Nodes(pNode).Nodes(tv_SurveyDate.Nodes(pNode).Nodes.Count - 1).ToolTipText = .Item("MONTH")
                            '--- Flush pNode1 

                            pNode1 = 0

                            ''--- Add 2nd Child Nodes
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Add(Format(.Item("FuelingTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).Tag = .Item("FuelingID")
                            ''tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).ToolTipText = .Item("MONTH")
                        Else
                            '    ''--- Add 2nd Child Nodes

                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Add(Format(.Item("FuelingTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).Tag = .Item("FuelingID")
                            '    'tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).ToolTipText = .Item("MONTH")
                        End If
                        tv_SurveyDate.SelectedNode = tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1)
                        If .Item("Color") = "White" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.White
                        ElseIf .Item("Color") = "Yellow" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Yellow
                        ElseIf .Item("Color") = "Orange" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Orange
                        ElseIf .Item("Color") = "LimeGreen" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LimeGreen
                        ElseIf .Item("Color") = "LightGreen" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LightGreen
                        ElseIf .Item("Color") = "LightPink" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LightPink
                        ElseIf .Item("Color") = "Aqua" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Aqua
                        End If
                    Else
                        ''--- Add Child Nodes
                        If pNode_Value <> .Item("MONTH") Then
                            pNode_Value = .Item("MONTH")
                            tv_SurveyDate.Nodes(pNode).Nodes.Add(Format(.Item("MonthTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(tv_SurveyDate.Nodes(pNode).Nodes.Count - 1).Tag = .Item("MONTH")
                            'tv_SurveyDate.Nodes(pNode).Nodes(tv_SurveyDate.Nodes(pNode).Nodes.Count - 1).ToolTipText = .Item("MONTH")
                            '--- Add 2nd Child Nodes

                            If iLoop > 0 Then
                                pNode1 = pNode1 + 1
                            End If

                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Add(Format(.Item("FuelingTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).Tag = .Item("FuelingID")
                            ' tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).ToolTipText = .Item("MONTH")

                        Else
                            '    '--- Add 2nd Child Nodes
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Add(Format(.Item("FuelingTitle")))
                            tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).Tag = .Item("FuelingID")
                            '    'tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1).ToolTipText = .Item("MONTH")
                        End If
                        tv_SurveyDate.SelectedNode = tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes(tv_SurveyDate.Nodes(pNode).Nodes(pNode1).Nodes.Count - 1)
                        If .Item("Color") = "White" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.White
                        ElseIf .Item("Color") = "Yellow" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Yellow
                        ElseIf .Item("Color") = "Orange" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Orange
                        ElseIf .Item("Color") = "LimeGreen" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LimeGreen
                        ElseIf .Item("Color") = "LightGreen" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LightGreen
                        ElseIf .Item("Color") = "LightPink" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.LightPink
                        ElseIf .Item("Color") = "Aqua" Then
                            tv_SurveyDate.SelectedNode.BackColor = Color.Aqua
                        End If
                    End If
                End With
            Next
            bln_Populate = True
            'tv_SurveyDate.Nodes(0).Expand()
            tv_SurveyDate.CollapseAll()
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

异常原因分析

索引依赖逻辑缺陷：代码通过pNode、pNode1等整数变量跟踪节点索引，但初始值设置不合理（如pNode_Value初始为0，若数据中存在Month=0的记录，会直接进入未创建Month节点的分支，导致访问不存在的节点索引）。
数据顺序依赖：代码逻辑完全依赖数据库返回数据的顺序（按Year、Month排序），若数据顺序混乱，会导致索引与实际节点数量不匹配。
直接索引访问风险：多次通过Nodes(索引)的方式访问节点，一旦索引值与实际节点集合的长度不匹配，就会触发索引越界异常。

解决方法

1. 优化节点引用方式（核心修复）

不再通过索引访问节点，而是直接保存刚创建的节点对象引用，彻底避免索引越界问题：

创建currentYearNode、currentMonthNode变量保存当前层级的节点对象
所有子节点添加操作直接基于父节点对象进行

2. 修正变量初始化逻辑

将pNode_Value初始值设为-1（与实际Month值区分），避免初始值与数据中的Month值冲突。

3. 确保数据有序

修改存储过程，添加ORDER BY Year, MONTH语句，保证返回数据按年份、月份升序排列，避免逻辑混乱。

4. 空值检查

添加数据空值判断，避免添加空节点导致的异常。

修改后的代码

Private Sub Populate_Tree()
    Try
        Dim dt As New DataTable
        Dim str_Sql As String
        If cls_GeneralVariables.Group_Name = "Data Entry Operators" Then
            str_Sql = " EXEC sp_GeneratorFueling_PopulateTree_Get  " & cls_GeneralVariables.LoginEmployeeId
        Else
            str_Sql = " EXEC sp_GeneratorFueling_PopulateTree_Get 0 "
        End If
        dt = cls_GeneralFunction.Get_DataTable(str_Sql, cls_GeneralVariables.Sql_Connection)
        bln_Populate = False
        tv_SurveyDate.Nodes.Clear() '直接清空，无需判断Count

        If dt.Rows.Count > 0 Then
            Dim currentYearNode As TreeNode = Nothing
            Dim currentMonthNode As TreeNode = Nothing
            Dim lastYear As String = Nothing
            Dim lastMonth As Integer = -1 '初始化为不存在的月份值

            For Each row As DataRow In dt.Rows
                Dim currentYear As String = row("Year").ToString()
                Dim currentMonth As Integer = CInt(row("MONTH"))
                Dim monthTitle As String = Format(row("MonthTitle"))
                Dim fuelingTitle As String = Format(row("FuelingTitle"))
                Dim fuelingId As Object = row("FuelingID")
                Dim nodeColor As String = row("Color").ToString()

                '处理年份节点
                If currentYear <> lastYear Then
                    lastYear = currentYear
                    '创建并添加年份节点
                    currentYearNode = New TreeNode(currentYear)
                    tv_SurveyDate.Nodes.Add(currentYearNode)
                    '重置月份跟踪
                    lastMonth = -1
                    currentMonthNode = Nothing
                End If

                '处理月份节点
                If currentMonth <> lastMonth Then
                    lastMonth = currentMonth
                    '创建并添加月份节点
                    currentMonthNode = New TreeNode(monthTitle)
                    currentMonthNode.Tag = currentMonth
                    currentYearNode.Nodes.Add(currentMonthNode)
                End If

                '处理加油记录节点
                If currentMonthNode IsNot Nothing Then
                    Dim fuelingNode As New TreeNode(fuelingTitle)
                    fuelingNode.Tag = fuelingId
                    '设置节点背景色
                    Select Case nodeColor.ToLower()
                        Case "white"
                            fuelingNode.BackColor = Color.White
                        Case "yellow"
                            fuelingNode.BackColor = Color.Yellow
                        Case "orange"
                            fuelingNode.BackColor = Color.Orange
                        Case "limegreen"
                            fuelingNode.BackColor = Color.LimeGreen
                        Case "lightgreen"
                            fuelingNode.BackColor = Color.LightGreen
                        Case "lightpink"
                            fuelingNode.BackColor = Color.LightPink
                        Case "aqua"
                            fuelingNode.BackColor = Color.Aqua
                    End Select
                    currentMonthNode.Nodes.Add(fuelingNode)
                    '设置选中节点为最新添加的节点
                    tv_SurveyDate.SelectedNode = fuelingNode
                End If
            Next

            bln_Populate = True
            tv_SurveyDate.CollapseAll()
        End If
    Catch ex As Exception
        MsgBox($"填充TreeView时出错：{ex.Message}")
    End Try
End Sub

额外说明

修改后的代码使用For Each遍历DataRow，比原For循环更简洁易读
通过Select Case替代原多个ElseIf，优化颜色判断逻辑
添加变量类型转换与空值检查，提升代码健壮性

内容的提问来源于stack exchange，提问作者Muhammad Nomaan

VB.NET基于SQL Server数据填充TreeView时遇索引越界异常

异常原因分析 #

解决方法 #

1. 优化节点引用方式（核心修复） #

2. 修正变量初始化逻辑 #

3. 确保数据有序 #

4. 空值检查 #

修改后的代码 #

额外说明 #