如何改写DataGridView数字列搜索代码解决Like操作类型不匹配问题

问题描述 #

现有DataGridView搜索功能仅支持纯文本列，通过ExcelReader加载Excel数据后，使用Like操作筛选以TextBox3输入字符开头的条目。原代码如下：

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    TryCast(dgvData.DataSource, DataTable).DefaultView.RowFilter =
       String.Format("Diff like '" & TextBox3.Text & "%'")
End Sub

但搜索数字列（如ID_Number）时，抛出错误：System.Data.EvaluateException: 'Cannot perform 'Like' operation on System.Double and System.String.'，原因是数字列类型为Double，无法直接与字符串执行Like操作。

解决方案 #

核心思路是将数字列转换为字符串类型后再执行Like匹配，利用DataTable的CONVERT函数实现类型转换：

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    ' 转义输入中的单引号，避免RowFilter语法错误
    Dim searchText As String = TextBox3.Text.Replace("'", "''")
    ' 针对数字列ID_Number，先转换为字符串再执行前缀匹配
    TryCast(dgvData.DataSource, DataTable).DefaultView.RowFilter =
        String.Format("CONVERT(ID_Number, 'System.String') LIKE '{0}%'", searchText)
End Sub

通用适配优化 #

如果需要同时支持文本列和数字列的搜索，可以先判断目标列的数据类型，再选择对应筛选逻辑：

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim dt As DataTable = TryCast(dgvData.DataSource, DataTable)
    If dt Is Nothing Then Return
    
    Dim searchText As String = TextBox3.Text.Replace("'", "''")
    Dim targetColumn As String = "ID_Number" ' 替换为实际目标列名
    Dim filter As String
    
    ' 判断列类型是否为数字类型
    If dt.Columns(targetColumn).DataType Is GetType(Double) OrElse dt.Columns(targetColumn).DataType Is GetType(Integer) Then
        filter = String.Format("CONVERT({0}, 'System.String') LIKE '{1}%'", targetColumn, searchText)
    Else
        filter = String.Format("{0} LIKE '{1}%'", targetColumn, searchText)
    End If
    
    dt.DefaultView.RowFilter = filter
End Sub

内容的提问来源于stack exchange，提问作者PeterW