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

SUBHAM DUTTA 1 Reputation point
2022-04-17T20:17:15.403+00:00
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
.NET Machine learning
.NET Machine learning
.NET: Microsoft Technologies based on the .NET software framework.Machine learning: A type of artificial intelligence focused on enabling computers to use observed data to evolve new behaviors that have not been explicitly programmed.
153 questions
0 comments No comments
{count} votes