Filter DataGrid using Combobox

[Retelist]

In a simple form (Form1) I have one combobox (ComboBox1) and one datagrid (DataGridView1).

Combobox1 data source is a Access 2010(.accdb) table (tblCoustomers; CID is the ValueMember, CName is the DisplayMember)
DataGridView1 data source is from the same database (tblInvoice)

tblInvoice have those columns:
InvID, InvNumber, InvDate, CID

When I change the value in the ComboBox , i need that the DataGridView to show only the informations corresponding to combobox ValueMember.

 Private Sub ComboBox1_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        Me.BindingSource1.Filter = String.Format("CID LIKE '{0}%'", ComboBox1.ValueMember)
        DataGridView1.Visible = True
End Sub

I have also tried, without success:

Me.BindingSource1.Filter = String.Format("Field1 LIKE '{0}%' OR Field2 LIKE '{0}%'",Me.ComboBox1.ValueMember)
Me.BindingSource1.Filter = String.Format("Name LIKE '%{0}%'", Me.ComboBox1)
Me.BindingSource1.Filter = "CID = " & ComboBox1.ValueMember
Me.BindingSource1.Filter = "CID = '" & ComboBox1.ValueMember & "'"
Me.BindingSource1.Filter = "CID Like '%" & ComboBox1.ValueMember & "%'"

The combobox show the DisplayMembers correctly, the DataGrid show ALL the data, but I can't manage to filter those data.

I use VB 2010

[dunfiddlin]

So you didn't like the ComboBox's .SelectedItem property and decided to invent your own?

[Retelist]

There is not a _SelectedItem" propriety in combobox VB 2010

I 've changed "ComboBox1_SelectedIndexChanged_1" to "ComboBox1_SelectedValueChanged", but the DataGridView1 still show all the data. I need only the filtered data (where Combobox CID = Datagridview CID)

[jmcilhinney]

So you didn't like the ComboBox's .SelectedItem property and decided to invent your own?

Oops! That's not the right property.

There is a SelectedItem property but it's not what you want in this case. The ValueMember is the name of a column and that doesn't change. What you want is the value from that column that corresponds to the item selected by the user. You get that from the SelectedValue property.

[Retelist]

OK, but after I changed the propriety to SelectedValue, it still do not filter...

[jmcilhinney]

What data type is the CID column? If it's not text then you can't use LIKE and you don't wrap it in single quotes.

[Retelist]

CID is Number (Access 2010), CName is Text

I also tried:
Me.BindingSource1.Filter = String.Format("CID = '", ComboBox1.ValueMember)

without success...

[jmcilhinney]

Then there's your issue. The Filter property of a BindingSource basically represents a SQL WHERE clause so it must obey the same rules. In SQL you don't wrap numbers in single quotes and you cannot use the LIKE operator or wildcards. You can only numerical operators, e.g. = or <. This is the only example of yours that uses valid syntax:

Me.BindingSource1.Filter = "CID = " & ComboBox1.ValueMember

although that would have to be changed to use the SelectedValue property.

[Retelist]

I have attached the project and database (into a .zip archive)... Please, take a look..

VBTest.zip

[jmcilhinney]

No need. Already explained what to do.

[Retelist]

I did that, without success....

my code:

First attempt:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBTestDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.VBTestDataSet.Customer)
        'TODO: This line of code loads data into the 'VBTestDataSet.Invoices' table. You can move, or remove it, as needed.
        Me.InvoicesTableAdapter.Fill(Me.VBTestDataSet.Invoices)
        Me.DataGridView1.Visible = False
    End Sub

    Private Sub ComboBox1_ValueMember(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Me.BindingSource1.Filter = "CID =" & ComboBox1.ValueMember
        Me.DataGridView1.Visible = True
    End Sub
End Class

Nothing happened... no filtering


Second Attempt:

Public Class Form1

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBTestDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.VBTestDataSet.Customer)
        'TODO: This line of code loads data into the 'VBTestDataSet.Invoices' table. You can move, or remove it, as needed.
        Me.InvoicesTableAdapter.Fill(Me.VBTestDataSet.Invoices)
        Me.DataGridView1.Visible = False
    End Sub

    Private Sub ComboBox1_ValueMemberChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Me.InvoicesBindingSource.Filter = "CID =" & ComboBox1.ValueMember
        Me.DataGridView1.Visible = True
    End Sub
End Class

Error: "Syntax error: Missing operand after '=' operator."

I attached the vb project and database...

[Retelist]

third attempt:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBTestDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.VBTestDataSet.Customer)
        'TODO: This line of code loads data into the 'VBTestDataSet.Invoices' table. You can move, or remove it, as needed.
        Me.InvoicesTableAdapter.Fill(Me.VBTestDataSet.Invoices)
        Me.DataGridView1.Visible = False
    End Sub

    Private Sub ComboBox1_SelectedValue(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
        Me.InvoicesBindingSource.Filter = "CID =" & ComboBox1.ValueMember
        Me.DataGridView1.Visible = True
    End Sub
End Class

Error: An error occurred creating the form. See Exception.InnerException for details. The error is: Syntax error: Missing operand after '=' operator.

fourth attempt:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBTestDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.VBTestDataSet.Customer)
        'TODO: This line of code loads data into the 'VBTestDataSet.Invoices' table. You can move, or remove it, as needed.
        Me.InvoicesTableAdapter.Fill(Me.VBTestDataSet.Invoices)
        Me.DataGridView1.Visible = False
    End Sub

    Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
        Me.InvoicesBindingSource.Filter = "CID =" & ComboBox1.ValueMember
        Me.DataGridView1.Visible = True
    End Sub
End Class

Same error as above...

[Retelist]

Solveeeeeed!!!!

Working code:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'VBTestDataSet.Customer' table. You can move, or remove it, as needed.
        Me.CustomerTableAdapter.Fill(Me.VBTestDataSet.Customer)
        'TODO: This line of code loads data into the 'VBTestDataSet.Invoices' table. You can move, or remove it, as needed.
        Me.InvoicesTableAdapter.Fill(Me.VBTestDataSet.Invoices)

        Me.DataGridView1.Visible = False
    End Sub

    Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
        Me.InvoicesBindingSource.Filter = "CID ='" & ComboBox1.SelectedValue & "'"
        Me.DataGridView1.Visible = True
    End Sub
End Class

Thanks jmcilhinney !