VB.NET中BindingSource.Filter多变量过滤异常问题求助

问题描述 #

我有一个绑定MsAccess文件数据源的DataGridView，通过多个TextBox实现快速过滤。当新增一个TextBox用于过滤额外列时，过滤功能出现异常，例如本该显示160条结果却仅显示5条。

正常工作的代码 #

Private Sub MPR_Filtrowanie(sender As Object, e As EventArgs) Handles FMPR_data.TextChanged, FMPR_kod.TextChanged, FMPR_opis.TextChanged,
    FMPR_regal.TextChanged, FMPR_uwagi.TextChanged, FMPR_lotto.TextChanged, FMPR_akcja.TextChanged, FMPR_lotto_prod.TextChanged

    MPruchyBindingSource.Filter = String.Format("Convert(data,'System.String') like '%{0}' and Convert(KodMP,'System.String') like '%{1}'
    and Convert(opis_gal,'System.String') like '%{2}%' and Convert(regal,'System.String') like '%{3}' and Convert(uwagi,'System.String') like '%{4}%'
    and Convert(lotto,'System.String') like '%{5}%' and Convert(akcja,'System.String') like '%{6}%'",
    _FMPR_data.Text.ToString, FMPR_kod.Text.ToString, FMPR_opis.Text.ToString, FMPR_regal.Text.ToString,
    _FMPR_uwagi.Text.ToString, FMPR_lotto.Text.ToString, FMPR_akcja.Text.ToString)

    MPRdgv.DataSource = MPruchyBindingSource

End Sub

新增列后异常的代码 #

Private Sub MPR_Filtrowanie(sender As Object, e As EventArgs) Handles FMPR_data.TextChanged, FMPR_kod.TextChanged, FMPR_opis.TextChanged,
    FMPR_regal.TextChanged, FMPR_uwagi.TextChanged, FMPR_lotto.TextChanged, FMPR_akcja.TextChanged, FMPR_lotto_prod.TextChanged

    MPruchyBindingSource.Filter = String.Format("Convert(data,'System.String') like '%{0}' and Convert(KodMP,'System.String') like '%{1}'
    and Convert(opis_gal,'System.String') like '%{2}%' and Convert(regal,'System.String') like '%{3}' and Convert(uwagi,'System.String') like '%{4}%'
    and Convert(lotto,'System.String') like '%{5}%' and Convert(akcja,'System.String') like '%{6}%' and Convert(lotto_prod,'System.String') like '%{7}%'",
    _FMPR_data.Text.ToString, FMPR_kod.Text.ToString, FMPR_opis.Text.ToString, FMPR_regal.Text.ToString,
    _FMPR_uwagi.Text.ToString, FMPR_lotto.Text.ToString, FMPR_akcja.Text.ToString, FMPR_lotto_prod.Text.ToString)

    MPRdgv.DataSource = MPruchyBindingSource

End Sub

问题原因 #

新增的过滤条件Convert(lotto_prod,'System.String') like '%{7}%'存在逻辑缺陷：

• Access数据库中，LIKE操作符无法匹配NULL值。如果lotto_prod列有大量空值（NULL），即使FMPR_lotto_prod文本框为空，过滤条件like '%%'也会排除所有NULL值的行，导致结果数量骤减。

解决代码 #

修改过滤逻辑，针对空文本框处理NULL值，同时只在文本框有内容时添加对应过滤条件：

Private Sub MPR_Filtrowanie(sender As Object, e As EventArgs) Handles FMPR_data.TextChanged, FMPR_kod.TextChanged, FMPR_opis.TextChanged,
    FMPR_regal.TextChanged, FMPR_uwagi.TextChanged, FMPR_lotto.TextChanged, FMPR_akcja.TextChanged, FMPR_lotto_prod.TextChanged

    Dim filterParts As New List(Of String)

    ' 逐个处理各列过滤条件，仅当文本框有内容时添加
    If Not String.IsNullOrEmpty(_FMPR_data.Text) Then
        filterParts.Add($"Convert(data,'System.String') like '%{_FMPR_data.Text}'")
    End If

    If Not String.IsNullOrEmpty(FMPR_kod.Text) Then
        filterParts.Add($"Convert(KodMP,'System.String') like '%{FMPR_kod.Text}'")
    End If

    If Not String.IsNullOrEmpty(FMPR_opis.Text) Then
        filterParts.Add($"Convert(opis_gal,'System.String') like '%{FMPR_opis.Text}%'")
    End If

    If Not String.IsNullOrEmpty(FMPR_regal.Text) Then
        filterParts.Add($"Convert(regal,'System.String') like '%{FMPR_regal.Text}'")
    End If

    If Not String.IsNullOrEmpty(_FMPR_uwagi.Text) Then
        filterParts.Add($"Convert(uwagi,'System.String') like '%{_FMPR_uwagi.Text}%'")
    End If

    If Not String.IsNullOrEmpty(FMPR_lotto.Text) Then
        filterParts.Add($"Convert(lotto,'System.String') like '%{FMPR_lotto.Text}%'")
    End If

    If Not String.IsNullOrEmpty(FMPR_akcja.Text) Then
        filterParts.Add($"Convert(akcja,'System.String') like '%{FMPR_akcja.Text}%'")
    End If

    ' 处理新增的lotto_prod列：空文本框时允许NULL值
    If Not String.IsNullOrEmpty(FMPR_lotto_prod.Text) Then
        filterParts.Add($"Convert(lotto_prod,'System.String') like '%{FMPR_lotto_prod.Text}%'")
    Else
        filterParts.Add("lotto_prod IS NULL OR Convert(lotto_prod,'System.String') like '%%'")
    End If

    ' 拼接过滤条件，无内容时清空过滤
    MPruchyBindingSource.Filter = If(filterParts.Count > 0, String.Join(" AND ", filterParts), "")
    MPRdgv.DataSource = MPruchyBindingSource

End Sub

说明 #

• 当FMPR_lotto_prod文本框为空时，过滤条件会同时匹配lotto_prod列为NULL和有内容的行，不会丢失大量数据。
• 仅在文本框有输入时才添加对应列的过滤条件，避免空文本框带来的无效过滤逻辑。
• 使用字符串插值（$""）替代String.Format，让代码更易读。

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