VB.NET中基于TextBox匹配值过滤ListView行的问题排查

云上老周

2026-8-15

账单软件功能异常排查与改进建议

需求与问题

我正在开发账单软件，核心需求：

当在DataGridView1第三列（索引为2）按下任意按键时，ListView2从DataTable dt9加载数据
在TextBox11输入内容时，通过TextChanged事件根据输入值过滤DataTable并更新ListView2

当前代码存在有时生效有时无效的问题，以下是原代码：

Private Sub DataGridView1_KeyPress(sender As Object, e As KeyPressEventArgs) Handles DataGridView1.KeyPress
    Dim c2 As DataGridViewCell = DataGridView1.CurrentCell

    If c2.ColumnIndex = 3 Then
        If (Char.IsControl(e.KeyChar)) Then Return
        If Trim(DataGridView1.Rows(c2.RowIndex).Cells(2).Value).Length = 0 AndAlso DataGridView1.Rows(c2.RowIndex).Cells(6).ReadOnly = True Then
            MsgBox("Please Enter the Item Name.")
        End If
    End If


    If c2.ColumnIndex = 2 Then
        If (Char.IsControl(e.KeyChar)) Then Return
        TextBox12.Text = Integer.Parse(c2.RowIndex)

        conn9.Open()
        Dim strSQL9 As String
        Dim cmd9 As SqlCommand
        Dim da9 As SqlDataAdapter = New SqlDataAdapter
        Dim df As New DataTable
        dt9.Clear()
        strSQL9 = "Select Col_Barcode as Barcode,Col_Pro_Name as Item,Col_Pack_Size as [Pack Size],Col_Brand as Brand,Col_Curr_Stock as Stock,Col_Base_Rate as DPL,Col_A_Rate as [Rate A],Col_B_Rate as [Rate B],Col_C_Rate as [Rate C],Col_D_Rate as [Rate D] From Tab_Item_M where Col_Pro_Name IS NOT NULL Order by Col_Pro_Name ASC"
        cmd9 = New SqlCommand(strSQL9, conn9)
        cmd9.CommandType = CommandType.Text
        da9.SelectCommand = cmd9
        da9.Fill(dt9)
        conn9.Close()
        Panel7.Visible = True
        Panel7.Size = New Size(1007, 375)
        TextBox11.Text = e.KeyChar
        TextBox11.Focus()
        TextBox11.Select(TextBox11.Text.Length, 0)
    End If
    e.Handled = True
End Sub



Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Timer1.Interval = 250
    Timer1.Stop()
    Dim df As DataTable = dt9.Clone
    df.Clear()
    Dim Rows As DataRow() = dt9.[Select]("Item like '%" & TextBox11.Text & "%'")

    For Each row As DataRow In Rows
        df.ImportRow(row)
    Next

    With ListView2
        .View = View.Details
        .GridLines = True
        .Columns.Clear()
        .Items.Clear()
    End With

    For Each col As DataColumn In df.Columns
        ListView2.Columns.Add(col.ToString)
    Next

    For Each row As DataRow In df.Rows
        Dim lst1 As ListViewItem = ListView2.Items.Add(If(row(0) IsNot Nothing, row(0).ToString, String.Empty))

        For i As Integer = 1 To df.Columns.Count - 1
            lst1.SubItems.Add(If(row(i) IsNot Nothing, row(i).ToString, String.Empty))
        Next
    Next
    ListView2.Columns.Item(0).Width = 70
    ListView2.Columns.Item(1).Width = 300
    ListView2.Columns.Item(2).Width = 90
    ListView2.Columns.Item(3).Width = 100
    ListView2.Columns.Item(5).Width = 70
End Sub

Private Sub TextBox11_TextChanged(sender As Object, e As EventArgs) Handles TextBox11.TextChanged
    Timer1.Stop()
    Timer1.Start()
End Sub

问题根源分析

数据库连接稳定性问题：直接操作conn9未做异常处理，若查询时出现错误，连接可能无法正常关闭，导致后续查询失败。
Timer触发逻辑不稳定：快速输入时，Timer的启停会出现叠加，导致过滤逻辑延迟或重复执行。
DataTable过滤语法风险：直接拼接输入文本到Select语句，若输入含%、_等特殊字符，会导致过滤条件失效。
ListView重绘效率低：每次更新都清空列和项再重新创建，频繁操作易引发界面卡顿或无响应。
空值处理不严谨：未判断DataGridView单元格、DataTable行/列的空值，可能触发空引用异常。

改进方案

1. 优化数据库操作（自动释放资源）

将数据库查询抽为独立方法，用Using自动释放连接、命令、适配器，避免资源泄漏：

Private dt9 As New DataTable() ' 确保全局初始化

Private Sub LoadItemData()
    Try
        dt9.Clear()
        ' 替换为你的实际连接字符串
        Using conn9 As New SqlConnection("你的数据库连接字符串")
            conn9.Open()
            Dim strSQL9 As String = "Select Col_Barcode as Barcode,Col_Pro_Name as Item,Col_Pack_Size as [Pack Size],Col_Brand as Brand,Col_Curr_Stock as Stock,Col_Base_Rate as DPL,Col_A_Rate as [Rate A],Col_B_Rate as [Rate B],Col_C_Rate as [Rate C],Col_D_Rate as [Rate D] From Tab_Item_M where Col_Pro_Name IS NOT NULL Order by Col_Pro_Name ASC"
            Using cmd9 As New SqlCommand(strSQL9, conn9)
                Using da9 As New SqlDataAdapter(cmd9)
                    da9.Fill(dt9)
                End Using
            End Using
        End Using
    Catch ex As Exception
        MsgBox($"数据加载失败：{ex.Message}")
    End Try
End Sub

2. 修正DataGridView触发逻辑

增加空值判断，避免异常，同时优化数据加载逻辑：

Private Sub DataGridView1_KeyPress(sender As Object, e As KeyPressEventArgs) Handles DataGridView1.KeyPress
    Dim c2 As DataGridViewCell = DataGridView1.CurrentCell
    If c2 Is Nothing Then Return ' 避免空单元格异常

    If c2.ColumnIndex = 3 Then
        If Char.IsControl(e.KeyChar) Then Return
        Dim itemCellValue = DataGridView1.Rows(c2.RowIndex).Cells(2).Value
        ' 严谨判断空值与长度
        If (itemCellValue Is Nothing OrElse Trim(itemCellValue.ToString()).Length = 0) AndAlso DataGridView1.Rows(c2.RowIndex).Cells(6).ReadOnly Then
            MsgBox("Please Enter the Item Name.")
        End If
    End If

    If c2.ColumnIndex = 2 Then
        If Char.IsControl(e.KeyChar) Then Return
        TextBox12.Text = c2.RowIndex.ToString() ' 直接转字符串，避免Parse异常

        ' 仅当dt9为空时重新加载，减少数据库请求
        If dt9.Rows.Count = 0 Then
            LoadItemData()
        End If

        Panel7.Visible = True
        Panel7.Size = New Size(1007, 375)
        TextBox11.Text = e.KeyChar
        TextBox11.Focus()
        TextBox11.Select(TextBox11.Text.Length, 0)
    End If
    e.Handled = True
End Sub

3. 重构TextBox过滤逻辑（移除Timer）

直接在TextChanged事件中执行过滤，用DataView替代DataTable.Select，同时处理特殊字符：

Private Sub TextBox11_TextChanged(sender As Object, e As EventArgs) Handles TextBox11.TextChanged
    FilterAndUpdateListView()
End Sub

Private Sub FilterAndUpdateListView()
    If dt9 Is Nothing OrElse dt9.Rows.Count = 0 Then Return

    ' 处理Like语法的特殊字符，避免过滤失效
    Dim filterText = TextBox11.Text.Replace("%", "[%]").Replace("_", "[_]")
    Dim filterExpr = $"Item LIKE '%{filterText}%'"

    ' 使用DataView过滤，效率更高
    Dim dv As New DataView(dt9)
    dv.RowFilter = filterExpr

    UpdateListView(dv.ToTable())
End Sub

Private Sub UpdateListView(dataTable As DataTable)
    With ListView2
        .View = View.Details
        .GridLines = True
        .BeginUpdate() ' 批量更新，避免界面闪烁
        .Columns.Clear()
        .Items.Clear()

        ' 添加列
        For Each col As DataColumn In dataTable.Columns
            .Columns.Add(col.ColumnName)
        Next

        ' 添加行，严谨处理DBNull值
        For Each row As DataRow In dataTable.Rows
            Dim lstItem As New ListViewItem(If(row("Barcode") Is DBNull.Value, "", row("Barcode").ToString()))
            lstItem.SubItems.Add(If(row("Item") Is DBNull.Value, "", row("Item").ToString()))
            lstItem.SubItems.Add(If(row("Pack Size") Is DBNull.Value, "", row("Pack Size").ToString()))
            lstItem.SubItems.Add(If(row("Brand") Is DBNull.Value, "", row("Brand").ToString()))
            lstItem.SubItems.Add(If(row("Stock") Is DBNull.Value, "", row("Stock").ToString()))
            lstItem.SubItems.Add(If(row("DPL") Is DBNull.Value, "", row("DPL").ToString()))
            lstItem.SubItems.Add(If(row("Rate A") Is DBNull.Value, "", row("Rate A").ToString()))
            lstItem.SubItems.Add(If(row("Rate B") Is DBNull.Value, "", row("Rate B").ToString()))
            lstItem.SubItems.Add(If(row("Rate C") Is DBNull.Value, "", row("Rate C").ToString()))
            lstItem.SubItems.Add(If(row("Rate D") Is DBNull.Value, "", row("Rate D").ToString()))
            .Items.Add(lstItem)
        Next

        ' 设置列宽
        .Columns(0).Width = 70
        .Columns(1).Width = 300
        .Columns(2).Width = 90
        .Columns(3).Width = 100
        .Columns(5).Width = 70

        .EndUpdate() ' 结束批量更新，刷新界面
    End With
End Sub

4. 预加载数据（可选）

在窗体加载时预先加载数据，避免第一次按键时等待：

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    LoadItemData()
End Sub

内容的提问来源于stack exchange，提问作者RUSHI HARDWARE

VB.NET中基于TextBox匹配值过滤ListView行的问题排查

需求与问题 #

问题根源分析 #

改进方案 #

1. 优化数据库操作（自动释放资源） #

2. 修正DataGridView触发逻辑 #

3. 重构TextBox过滤逻辑（移除Timer） #

4. 预加载数据（可选） #