BindingSource.Filter Problem Part 2

Sorry for reposting this, but my new question was burried within the thread of my previous post and I wanted people to get a fresh look at my problem.

I have a form with a DataGridView with multiple columns. I have another form that pops up to give the user options of filtering the data in the DataGridView form. I have several ComboBoxes bound to tables that contain the data available in a certain column in which the user can select items from the ComboBox to filter the column.

Thanks to tabdalla, I was able to get the code to make a single column filter based on the item selected in one of the ComboBoxes. Here is the code tabdalla helped me with:

Private Sub ComboBox_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ComboBox_Player.Validating, ComboBox_Set.Validating

Dim name As String = CType(sender, ComboBox).Name

Select Case name

Case "ComboBox_Player"

List_Screen.Player_InfoDataGridView.DataSource.Filter = String.Format("Player ='{0}'", Me.ComboBox_Player.Text)

Case "ComboBox_Set"

List_Screen.Player_InfoDataGridView.DataSource.Filter = String.Format("Make2 ='{0}'", Me.ComboBox_Set.Text)

End Select

End Sub

Here is my dilema. The code works if the user selects either the ComboBox_Player or the ComboBox_Set, but will not work if both ComboBoxes are selected. In that case, it just filters the data based on the ComboBox_Set. Also, if the user goes back to the filter form after he filters the data for the first time and does not select any of the ComboBoxes (in other words, wants to revert back to the full unfiltered data), the DataGridView still shows the previous filtered data.

Is there a way through modifying the above code (or using different code like If Then statements) to allow the user to select any combination of ComboBoxes (ie just ComboBox_Player, just ComboBox_Set, both ComboBoxes, or none) to filter the data and to remove the filters if none of the ComboBoxes are selected

Finally, when the user opens the filter form the ComboBoxes show the first item in the list rather than a blank space for the user to either type in an item or click the arrow to reveal the items. I have tried setting the DropDownStyle to DropDown and DropDownList and neither of them correct the problem. Do the ComboBoxes work with the first item selected automatically Yes. But I'm a bit anal and I would like the form to have a certain look to it. Thanks.

Answer this question

BindingSource.Filter Problem Part 2

Aleksei

Nothing will be filtered until you click the button which is setting the filter source property - which is when it will constructString routine will be run to generate the filter string and sets the bindingsource.filter property.

If you want it filtered when a change a combobox then you'll need to put this in an event such as the ComboBox SelectedIndexChanged event. That way when the selectedindex changes on the combobox it will do the constructstring and set the bindingsource.filter property.

But as per my original post

At the moment I have a button which you click to see the contents and would personally have a refresh button - so the user could select whatever combination they want and then when they are ready click refresh which would set the filter string. This way you avoid having things refreshing and filter every time they touch any combobox. I'd only want to retrieve data once I've set everything.

Which is why I would recommend a filter button.

As its the same code for whatever combobox is set you can create a

Private Sub ComboBox_Set_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox_Set.SelectedIndexChanged, ComboBox_Player.SelectedIndexChanged

List_Screen.Player_InfoDataGridView.DataSource.Filter = constructString()

End Sub

Krugger
Spotty:

Well, I moved the code around a little and made sure that the List_Screen.Player_InfoDataGridView.DataSource.Filter=constructString() line was included in the section for the Private Sub Filter_Form_Click and guess what It worked like a charm!! Thank you for all your help! I'm sure I will be back to this forum with more questions as I develop my program. Thanks again.

John
Justin J Smith
OK lets start with a few things here.

What is list_screen    Is it a listbox/combobox - for this code to work it needs to be a combobox.   You also need to set the tag property on the combobox to the field that this is going to filter the database on.

I have modified the code so it is slightly clearly - this has two comboboxes - called combobox1 and combobox2 and a Button1.

When you enter texts into the comboboxes - it will create a filter string of all the comboboxes which have a text value.    Using the tag to determine the field and the text to determine the contents.   It displayes this in a messagebox but at this point this is where you would set you bindingsource.filter property just as you did before.

Also note - you cannot put code after a return statement as it will not get executed as the return will exeit from the appropriate method.

For this to work it is essential that these are comboboxes and that the tag's are set on them.

Class form1
    Private Sub RepopulateFilterString(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        MsgBox(constructString())
        '//This would be where you set your bindingfilter with the constructString()
    End Sub

    Function constructString() As String
        Dim strFilterString As String = ""
        For Each ctrl As Control In Me.Controls
            If TypeOf (ctrl) Is ComboBox Then
                If CType(ctrl, ComboBox).Text.Length > 0 Then
                    If strFilterString.Length >= 0 Then strFilterString = strFilterString & " AND "
                    strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
                End If
            End If
        Next
        Return strFilterString
    End Function

    Private Sub form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '//Establish the tags on the comboboxes
        Me.ComboBox1.Tag = "Field1" '//Which field in the database this combobox relates to
        Me.ComboBox1.Tag = "Field2" '//Which field in the database this combobox relates to

    End Sub

End Class
wichall
I believe that you want to be able to select and combobox or combination of combobox to build up a filter string to use. So the code you are showing is simply going to set the filter string to the currently selected combobox - one box only -which is not what you want.

The following code is a simple example of what I believe you are looking for - or along the lines of. It will search through the controls on the form looking for the combobox control and if the text property is set to something then it will use the tag property which I have used here to hold the field name and the text property of the control to add to a filter string.

So you get a filter string that comprises of all the currently selected comboboxes.

At the moment I have a button which you click to see the contents and would personally have a refresh button - so the user could select whatever combination they want and then when they are ready click refresh which would set the filter string. This way you avoid having things refreshing and filter every time they touch any combobox. I'd only want to retrieve data once I've set everything.

But instead of displaying the contents in a messagebox you would set the filter property with this value.

This is a basic idea and if you have other comboboxes on the form you would have to identify which are FilterCombo which you could do by doing a comparison on the name or something like that.

Class form1
Private Sub RepopulateFilterString(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox(constructString)
End Sub

Function constructString() As String
Dim strFilterString As String = ""

For Each ctrl As Control In Me.Controls
If TypeOf (ctrl) Is ComboBox Then
If CType(ctrl, ComboBox).Text.Length > 0 Then
strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
End If
End If
Next

Return strFilterString
End Function
End Class

All you'll have to do is decide where you want to call the constructstring method and once you have called it and got a combined filter string of all the comboboxes then you can set the bindingsource filter property.
GregXTZ
At the moment the following code will work - this will address the Me.controls me is an collection of the controls on a form that should work for any form.

The repopulate is commented out as I have a question is List_Screen the name of the form class that you have established or is it the instance of the form. This makes a great big difference. If its the instance how is this being passed to the form as I dont see any property which would allow you to reference this from this form.

To start with I'd make a simple form with this all on one form rather than over multiple forms to ensure the concept is correct. Once this is correct then you can move it to multiple forms.

Public Class form1

Private Sub Filter_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DataDataSet.Make' table. You can move, or remove it, as needed.
Me.MakeTableAdapter.Fill(Me.DataDataSet.Make)
Me.MainTableAdapter.Fill(Me.DataDataSet.Main)
Me.ComboBox_Player.SelectedIndex = -1
Me.ComboBox_Set.SelectedIndex = -1
Me.ComboBox_Player.Tag = "Player"
Me.ComboBox_Set.Tag = "Make2"
End Sub

Private Sub RepopulateFilterString(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Filter_Form_GO.Click
' ' List_Screen.Player_InfoDataGridView.DataSource.Filter = constructString()

End Sub

Function constructString() As String
Dim strFilterString As String = ""
For Each ctrl As Control In Me.Controls
If TypeOf (ctrl) Is ComboBox Then
If CType(ctrl, ComboBox).Text.Length > 0 Then
If strFilterString.Length >= 0 Then strFilterString = strFilterString & " AND "
strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
End If
End If
Next
Return strFilterString
End Function

End Class
JVH
Spotty:

Here is the code that I have entered:

Class form1
Private Sub RepopulateFilterString(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox(constructString)
End Sub
Function constructString() As String
Dim strFilterString As String = ""
For Each ctrl As Control In Me.Controls
If TypeOf (ctrl) Is ComboBox Then
If CType(ctrl, ComboBox).Text.Length > 0 Then
strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
End If
End If
Next
Return strFilterString
List_Screen.Player_InfoDataGridView.DataSource.Filter = strFilterString
End Function
End Class
End Class

I added the 'List_Screen.Player_InfoDataGridView.DataSource.Filter = strFilterString' line because that was the code I was using to filter the DataGridView in the old code I was using.

I get a couple of errors when I test the code. First, the Button1.Click in the second line gives me an error of "Handles clause requires a WithEvents variable defined in the containing type or one of its base types." Secondly, the Me.Controls in the sixth line gives me an error of " 'Controls' is not a member of 'SportscardOrganizer.Filter_Form.form1".

I do have a button on the form to run the filter and it is called Filter_Form_GO. I tried putting that in line 2 but I got the same error. What am I missing Are there other aspects of the code example you gave that I need to change to correspond to my particular program Forgive me, I am new to this. Thanks.
etcheverrjc
I'm really pleased that it worked for you eventually.

My apologies if my last response was rather terse - it was gone midnight when I was answering. Its often really easy to know where to put things (if its right in front of you) but difficult to describe to others what you want when you cant see the code.
k333
Slight Typo - it should have been as bolded

Function constructString() As String
Dim strFilterString As String = ""
For Each ctrl As Control In Me.Controls
If TypeOf (ctrl) Is ComboBox Then
If CType(ctrl, ComboBox).Text.Length > 0 Then
If strFilterString.Length > 0 Then strFilterString = strFilterString & " AND "
strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
End If
End If
Next
Return strFilterString
End Function

If it is an instance of an object - how is this communicated to the form. I dont see any means of referencing this object from this form. Are you sure that this is an instance and not a class name.

If you open up that form in designer and click on it - what is the name of the form.

Do you have and line in you application that say

Dim List_Screen as <samething>

where something can be anything at all
hamed babai
Spotty:

List_Screen is the form that contains the DataGridView. Filter_Form is the form that contains the comboboxes (named ComboBox_Player and ComboBox_Set) that I hope to filter the data in the List_Screen form. I have a button on the Filter_Form called Filter_Form_GO. Also, I have set the SelectedIndex for the two comboboxes to -1 so the comboboxes do not automatically show the first item in the list. I now have the following code:

Public Class Filter_Form
Private Sub Filter_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DataDataSet.Make' table. You can move, or remove it, as needed.
Me.MakeTableAdapter.Fill(Me.DataDataSet.Make)
Me.MainTableAdapter.Fill(Me.DataDataSet.Main)
Me.ComboBox_Player.SelectedIndex = -1
Me.ComboBox_Set.SelectedIndex = -1
Me.ComboBox_Player.Tag = "Player"
Me.ComboBox_Set.Tag = "Make2"
End Sub

Class form1

Private Sub RepopulateFilterString(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Filter_Form_GO.Click
List_Screen.Player_InfoDataGridView.DataSource.Filter = constructString()
End Sub

Function constructString() As String
Dim strFilterString As String = ""
For Each ctrl As Control In Me.Controls
If TypeOf (ctrl) Is ComboBox Then
If CType(ctrl, ComboBox).Text.Length > 0 Then
If strFilterString.Length >= 0 Then strFilterString = strFilterString & " AND "
strFilterString = strFilterString & ctrl.Tag & "='" & CType(ctrl, ComboBox).Text & "' "
End If
End If
Next
Return strFilterString
End Function
End Class
End Class

I'm still getting the error concerning the 'Filter_Form_GO.Click' and the 'Me.Controls', What would I need to modify to get those two items to work I really appreciate your help. Thanks.
rkn123

Reichard,

I've never tried to construct a filter based upon input from multiple controls. I've read spotty's post and the process he suggests seems to make the most sense. Spotty has also helped me several times in the past and his advice always seems to be "spot on" (pun intended). Sorry I couldn't be of more help.

Tony
Mirek Sztajno
To be honest, I don't really know if it is an instance and not a class name. When I open the form that has the DataGridView, the name is List_Screen. I do not have a line that says Dim List_Screen. In the code for that form it says 'Public Class List_Name' at the top. Does that help

Well, I made the change to the line and when I ran it, I did not get any errors. That's the good news. The bad news is that when I select either of the comboboxes (or both of them), nothing gets filtered. If it helps, here is the code that did work when just one of the comboboxes was selected:

List_Screen.Player_InfoDataGridView.DataSource.Filter = String.Format("Player ='{0}'", Me.ComboBox_Player.Text)

I can't thank you enough for sticking through this with me.
kristie
OK - You begining to confuse the heck out of me now.

I can see what this method is doing, this button when clicked will display a form called List_screen and depending upon whether the checkboxes are check they will display a column or not and then close the current form. But at no point are you setting the bindingsource.filter property here.

The code I gave you constructed a string - which can be used as the bindingsource.filter property based upon combobox values.

Where you set this string is up to you - whether it be on a button, in the combo selectedindexchnage event or wherever you want it. But ultimately it needs to get called when you want to filter the datagridview.

If you dont call the line to construct the filterstring or dont set the bindingsource.filter property with this string returned from this function then you aren't going to get the datagridview filtered.

You need to ensure that the line is executed. Stick a breakpoint on the line and ensure that whatever actions you doing in you application this is getting called - if its not called then its not going to filter. You need to seriously determine that whatever event you are using to do the filter is fiting and this line is getting called.

List_Screen.Player_InfoDataGridView.DataSource.Filter = constructString()

is the important line.
ChrisSd
List_Screen I believe is the instance of the form. It is the name of the form that holds the DataGridView. All I know is that in my previous attempts to filter the DataGridView using this instance worked (to some extent - with the exception of this problem of using more than one combobox to filter the data). When I run the program using the revised code you gave me (but without the repopulate commented out) I get the following error:

Syntax error: Missing operand before 'And' operator

for the line:

List_Screen.Player_InfoDataGridView.DataSource.Filter = constructString()
Andr&#233; Tadeu
I do have a button. It's called Filter_Form_GO. However, I think I know why clicking the button does not trigger the filter. I have other code that I have not given you because that works. On the Filter Form I have a series of check boxes that if deselected, will hide the corresponding column from the DataGridView on the List Screen Form. I won't list all of the code, but I'll give you the beginning and end. I think the button I have on the form is only handling the check boxes and not the comboboxes. Let me know if you think that is the case. If so, how do I incorporate the code for the comboboxes so that by clicking on the button it handles both the check boxes and comboboxes

Private Sub Filter_Form_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Filter_Form_GO.Click
List_Screen.Show()
If Me.Player_Name.Checked Then
List_Screen.Player_InfoDataGridView.Columns(0).Visible = True
Else
List_Screen.Player_InfoDataGridView.Columns(0).Visible = False
End If
If Me.Year.Checked Then
List_Screen.Player_InfoDataGridView.Columns(1).Visible = True
Else
List_Screen.Player_InfoDataGridView.Columns(1).Visible = False
End If
Me.Close()
End Sub
BindingSource.Filter Problem Part 2

Answer this question

BindingSource.Filter Problem Part 2

Hot Topic

VS Express Editions

BindingSource.Filter Problem Part 2

BindingSource.Filter Problem Part 2

Aleksei

Krugger

Justin J Smith

wichall

GregXTZ

JVH

etcheverrjc

k333

hamed babai

rkn123

Mirek Sztajno

kristie

ChrisSd

Andr&#233; Tadeu