[RESOLVED] DGV Combined Filter

[LuxCoder]

Hi,

I am filtering a Datagridview with combined help of 4 textboxes and 4 comboboxes each dedicated to a field in DB. All i know about this process is i can combine two or more filters but how do i check at a given time, whether which of these (cb & tb) have been used already to perform the filter already and then collect all the instances and perform the collective filter.

Any suggestions??

Lux.

[jmcilhinney]

What values have been used to filter before is irrelevant. Just build the entire filter string each time.

[LuxCoder]

Now suppose i have these three filters:

ClientProfileBindingSource.Filter = String.Format("Convert(MachineID, 'System.String') LIKE '{0}%'", TextBox2.Text)

and

ClientProfileBindingSource.Filter = "Category = '" & ComboBox2.Text & "'"

and

ClientProfileBindingSource.Filter = String.Format("ClientType = '{0}' OR ClientType = '{1}'", "Seeker", "Both")

You mean to say that under all of their event change, i should use a single combined filter?

ClientProfileBindingSource.Filter = String.Format("Convert(MachineID, 'System.String') LIKE '{0}%'", TextBox2.Text) AND "Category = '" & ComboBox2.Text & "'" AND String.Format("ClientType = '{0}' OR ClientType = '{1}'", "Seeker", "Both")

When i am using the above code i am getting error:

Conversion from string "Convert(MachineID, 'System.Strin" to type 'Long' is not valid.

How can it be resolved?

[danasegarane]

Check this one

ClientProfileBindingSource.Filter 
=String.Format("(Convert(MachineID, 'System.String') 
LIKE '{0}%') AND (Category ='{1}' ) AND 
 (ClientType = '{2}' OR ClientType = '{3}' ) ",

                                                  TextBox2.Text, ComboBox2.Text,"Seeker", "Both")

[LuxCoder]

i used this:

ClientProfileBindingSource.Filter = String.Format("(Convert(MachineID,'System.String') LIKE '{0}%') AND (Convert(FirstName,'System.String') LIKE '{1}%') AND (Category = '{2}') AND (Convert(LastName,'System.String') LIKE '{3}%') AND (Convert(Mobile,'System.String') LIKE '{4}%') AND (Type = '{5}') AND (Brand = '{6}') AND (Model = '{7}') AND (State = '{8}') AND (District = '{9}') AND (City = '{10}') AND (Convert(ClientID,'System.String') LIKE '{11}%') AND (Availability = '{12}')", TextBox2.Text, Cname.Text, ComboBox2.Text, TextBox3.Text, TextBox4.Text, ComboBox4.Text, ComboBox3.Text, ComboBox5.Text, ComboBox1.Text, ComboBox6.Text, ComboBox7.Text, ComboBox8.Text)

and i got the following error:

Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

[danasegarane]

Check the format arguments it is 12

TextBox2.Text, Cname.Text, ComboBox2.Text, TextBox3.Text, TextBox4.Text, ComboBox4.Text, ComboBox3.Text, ComboBox5.Text, ComboBox1.Text, ComboBox6.Text, ComboBox7.Text, ComboBox8.Text

and you are passing only 11

[LuxCoder]

oops.. got it right.

i have placed this code in all the event change method of the controls. But when i enter text in text box or select an item in combobox, i get no result in DGV.

I need to perform this filter mechanism by use of any possible combinations of the controls for which i hope 'AND' is the right operator. Am i wrong?

[danasegarane]

oops.. got it right.

i have placed this code in all the event change method of the controls. But when i enter text in text box or select an item in combobox, i get no result in DGV.

I need to perform this filter mechanism by use of any possible combinations of the controls for which i hope 'AND' is the right operator. Am i wrong?

For textBoxes call the the method TextBox.LeaveEvent of the textBox.

And for the Combo Box you have call in the the Combobox _SelectedIndex Changed method.

And for the And only you have decide whether to use AND or OR

[LuxCoder]

Tried what you suggested.
But how does it matter what even it is being used?

Is it not that, that the other blank textboxes and comboboxes are included in the filter when they have no values in them ???

[jmcilhinney]

You've got two choices:

1. If you don't want empty fields added to the filter then don't add them. That's what If statements are for:

Dim filters As New List(Of String)
 
If Me.TextBox1.Text.Trim().Length > 0 Then
    filters.Add(String.Format("Column1 LIKE '%{0}%'", Me.TextBox1.Text.Trim()))
End If
 
If Me.TextBox2.Text.Trim().Length > 0 Then
    filters.Add(String.Format("Column2 LIKE '%{0}%'", Me.TextBox2.Text.Trim()))
End If
 
If Me.TextBox3.Text.Trim().Length > 0 Then
    filters.Add(String.Format("Column3 LIKE '%{0}%'", Me.TextBox3.Text.Trim()))
End If
 
Dim filter As String = String.Join(" AND ", filters)

2. Include everything but add extra filters to account for empty fields:

Dim filter As String = String.Format("('{0}' = '' OR Column1 = '{0}') AND ('{1}' = '' OR Column2 = '{1}') AND ('{2}' = '' OR Column3 = '{2}')", _
                                     Me.TextBox1.Text.Trim(), _
                                     Me.TextBox2.Text.Trim(), _
                                     Me.TextBox3.Text.Trim())

Note that I use '=' rather than 'LIKE' in that second example because you don't need the extra filters for LIKE. If the field is empty then every record will match "LIKE '%%'" anyway.

[LuxCoder]

Thank you.

[LuxCoder]

I am using the following to join the filter string and perform a filter on the DGV. I tried a lot but it gives me error

Error 1 Value of type 'System.Collections.Generic.List(Of String)' cannot be converted to '1-dimensional array of String'.

Dim filters As New List(Of String)
                                If (reader("Category".ToString)).Length > 0 Then
                                    filters.Add(String.Format("(Convert(Category, 'System.String') LIKE '%{0}%')", (reader("Category"))))
                                End If
                                If (reader("Type".ToString)).Length > 0 Then
                                    filters.Add(String.Format("(Convert(Type, 'System.String') LIKE '%{0}%')", (reader("Type"))))
                                End If
                                Dim filter As String = String.Join(" And ", filters)
                                ClientProfileBindingSource.Filter = filter

what should i do?

[jmcilhinney]

I typed that code straight into the forum so didn't have the IDE to check it for me. That said, the fix is fairly simple. You've got a List(Of String) and you need a String(). How do you create an array from a List? You call ToArray on it.