VB.NET BindingSource.Filter for multiple variables strange behaviour-CodePudding

I have got a DataGridView with datasource from MsAccess file which i quickly filter thru the many TextBox If add one more TextBox filtering for a one more column, then filtering is not working properly, for example is showing only 5 results where it should be showing 160 reusults

Working properly: IMG

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

Not working properly (just one more column): IMG

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

CodePudding user response：

I'm fairly confident that the issue is that your filter will match only string values and not nulls so your filter is excluding rows where that column is null. In that case, you can change this:

    Convert(lotto_prod,'System.String') like '%{7}%'

to this:

    CONVERT(ISNULL(lotto_prod, ''),'System.String') LIKE '%{7}%'

and I think it should work. You may need to do similarly for other columns that could contain nulls.