绑定源在DataGridView中使用两个或多个单词的列名上的binding源无法使用

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

按代理名称过滤不起作用，请任何人都需要帮助。通过ID或Contactno过滤效果很好。我有另一种形式的类似代码，它的工作正常。

编辑：即使我更改SQL =“选择ID，名称，地址，Pincode，Contactno，AltContactno，SelfipType，Selfipref，Selfipref，Selfipa，Selfipp，Selfipp，Isipopd，Ipopdtype，iPopdvalue，iPopdvalue，Isactive，Isactive，isAttive，Isaverive，artherby，creatsby，create in tblagentMaster crount tblagentMaster of tblagentMaster” 和过滤文本为bagentdata.filter =“名称为'％”＆amp; search.text＆amp; “％'”然后还按名称过滤不起作用。 在下面添加了另一个示例代码，该代码在该示例中正常工作：

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

根据 BindingSource.Filter 的文档 (这里）：

如果基础数据源是 DataSet、DataTable 或 DataView，
您可以使用记录的语法指定布尔表达式
DataColumn.Expression 属性。

然后根据 DataColumn.Expression 的文档 (

创建表达式时，使用ColumnName属性来引用
列。

我建议执行以下操作：

1. 在填充 DataTable 的数据时设置断点 单
2. 步执行（F10 快捷键）到下一行
3. 检查 DataTable 的 Columns 属性

我怀疑发生的情况是 Fill 方法正在调整列名称以使其符合以正确的命名约定，例如，“Agent Name” 变为“AgentName”。获得 DataColumn 的实际名称后，请在过滤器中使用它。