Binding Source doesn't work on column names with two or more words in DataGridView

Question

Dim bagentdata As New BindingSource
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable
Dim SQL As String

Private Sub frmAgent_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Try
        'conn.Open()
        ConnectDatabase()
        SQL = ""
        SQL = "SELECT ID, Name `Agent Name`, Address, Pincode, ContactNo, AltContactNo, SelfIPType, SelfIPRef, SelfIPA, SelfIPP, " & _
                            "IsIPOPD, IPOPDType, IPOPDValue, IsActive, CreatedBy, CreatedOn FROM tblagentmaster"

        myCommand.Connection = Conn
        myCommand.CommandText = SQL

        myAdapter.SelectCommand = myCommand
        myData.Clear()
        myAdapter.Fill(myData)

        Me.AgentDataView.DataSource = myData
        Me.AgentDataView.DefaultCellStyle.Font = New Font("Arial", 10, FontStyle.Bold)
        AgentDataView.ReadOnly = True
        CloseDatabase()
    Catch myerror As MySqlException
        MessageBox.Show("Error: " & myerror.Message)
    End Try
    Clear.PerformClick()
End Sub

Private Sub Search_TextChanged(sender As Object, e As EventArgs) Handles Search.TextChanged
        bagentdata.DataSource = myData
        AgentDataView.DataSource = bagentdata
        bagentdata.Filter = "[Agent Name] Like '%" & Search.Text & "%' " & _
                            " OR ContactNo = " & Val(Search.Text) & " OR ID = " & Val(Search.Text)
        If Search.Text = "" Then
            Clear.PerformClick()
        End If
End Sub

Filter by Agent Name is not working, please anyone need help on this. Filter by ID or Contactno is working fine. I have a similar kind of code in another form, there it's working fine.

Edit: Even if I change the SQL = "SELECT ID, Name, Address, Pincode, ContactNo, AltContactNo, SelfIPType, SelfIPRef, SelfIPA, SelfIPP, IsIPOPD, IPOPDType, IPOPDValue, IsActive, CreatedBy, CreatedOn FROM tblagentmaster" and Filter Text as bagentdata.Filter = "Name Like '%" & Search.Text & "%' " then also Filter By Name not working. Below adding another Sample code where the Filter is working perfectly:

Sub ClearAll(rindex As Integer)
        Try
            ConnectDatabase()
            SQL = ""
            SQL = "SELECT BillNo as BillNO, date(CaseCreatedOn) as 'Booked On', PatientName as `Patient Name` , UHID, PatientAge as Age, PatientSex as Gender FROM tblbooking " & _
                "where BillNo in (SELECT DISTINCT BillNo FROM tblbookingdetails WHERE ItemStatus in (7,8)) ORDER BY 'Booked On', BillNO"

            myCommand.Connection = Conn
            myCommand.CommandText = SQL

            myAdapter.SelectCommand = myCommand
            myData.Clear()
            myAdapter.Fill(myData)

            allbilldtv.DataSource = myData
            CloseDatabase()
        Catch myerror As MySqlException
            MessageBox.Show("Error: " & myerror.Message)
        End Try
        Try
            allbilldtv.CurrentCell = allbilldtv.Rows(rindex).Cells(0)
            allbilldtv_CellClick(Nothing, Nothing)
        Catch err As ArgumentOutOfRangeException
            MessageBox.Show("Report Generate Queue All Cleared...!!!", "©2022-25 Healeon™ - All Rights Reserved®", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
Private Sub CommonSearch_TextChanged(sender As Object, e As EventArgs) Handles CommonSearch.TextChanged
        Try
            allbilldata.DataSource = myData
            allbilldtv.DataSource = allbilldata
            allbilldata.Filter = "[Patient Name] Like '%" & CommonSearch.Text.ToUpper & "%' OR BillNO = " & Val(CommonSearch.Text)

            If CommonSearch.Text = "" Then
                ClearAll(0)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Answer 1

According to the documentation for BindingSource.Filter (here):

If the underlying data source is a DataSet, DataTable, or DataView, you can specify Boolean expressions using the syntax documented for the DataColumn.Expression property.

Then according to the documentation for DataColumn.Expression (here):

When you create an expression, use the ColumnName property to refer to columns.

I would suggest doing this following:

1. Setup a breakpoint when filling the DataTable's data
2. Step over (F10 shortcut key) to the next line
3. Inspect the Columns property of the DataTable

What I suspect is happening is that the Fill method is massaging the column name to conform to proper naming convention, e.g. "Agent Name" becomes "AgentName". Once you have the actual name of the DataColumn, use it in your filter.