Member Avatar for PM312

I am traying to filter Datagrid view based on user type in TbxGlCode (TextBox)

Initialy Datatable is populated from SQL table to TempTable (DataTable)

getting error at runtime at below line

Dim Rows As DataRow() = TempTable.[Select]("Code like '%" & VerCode & "%'")

Exception Unhandled
System.Data.EvaluateException: 'Cannot perform 'Like' operation on System.Int32 and System.String

How to filter datatable with Like option

My Code

Dim TempTable As DataTable

    Private Sub FrmGLCodeList_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' Dim TempTable As DataTable
        'Create New Table Instance
        TempTable = New DataTable

        TempTable.Columns.Add("Code", GetType(Integer))
        TempTable.Columns.Add("Name", GetType(String))

        'Adding Rows to TempTable from SQL table

        Call RSData("Select GlCode as Code , GlName as Name from TblGlCodeMaster")

        Dim I As Integer = 0

        If DBCnCommonDataTable.Rows.Count > 0 Then
            For I = 0 To DBCnCommonDataTable.Rows.Count - 1
                TempTable.Rows.Add(Trim(DBCnCommonDataTable.Rows(I)("Code")), Trim(DBCnCommonDataTable.Rows(I)("Name")))
            Next
        End If

        DgvGlCodeView.DataSource = TempTable

        DBCnClose()
        DgvGlCodeView.Columns(1).Width = 300
    End Sub
    'Filtered data table
    Private Sub FilteredDT()

        Dim VerCode As Integer = Trim(TbxGlCode.Text)
        Dim FilteredDT As DataTable = TempTable.Clone
        Dim Rows As DataRow() = TempTable.[Select]("Code like '%" & VerCode & "%'")
        For Each row As DataRow In Rows
            FilteredDT.ImportRow(row)
        Next

        DgvGlCodeView.DataSource = FilteredDT

    End Sub

    Private Sub TbxGlCode_TextChanged(sender As Object, e As EventArgs) Handles TbxGlCode.TextChanged

        Call FilteredDT()
    End Sub
Edited by Dani because: Moved to Software Dev forum
  • 3 Contributors
  • 5 Replies
  • 17K Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by tinstaafl

Dani AI

Generated

You are getting the exception because the DataTable column Code is Int32, and the DataColumn expression engine will not apply LIKE to numeric types. You can still do a contains match by converting the numeric column to a string in the filter expression.

Two practical approaches:

  • Use a DataView and Convert(...) in the RowFilter so you can keep Code as an integer:
Dim term As String = TbxGlCode.Text.Trim()
Dim dv As New DataView(TempTable)
dv.RowFilter = $"Convert(Code, 'System.String') LIKE '%{term.Replace("'", "''")}%'"
DgvGlCodeView.DataSource = dv

Note: escape single quotes in user input by doubling them. Convert(expr, 'System.String') is supported by the DataColumn expression syntax used by RowFilter and Select (see DataColumn.Expression and DataView.RowFilter).

  • For better performance on repeated filtering, add a computed string column once, then filter on that string column:
If Not TempTable.Columns.Contains("CodeText") Then
    TempTable.Columns.Add("CodeText", GetType(String), "Convert(Code, 'System.String')")
End If

Dim term As String = TbxGlCode.Text.Trim()
Dim dv As New DataView(TempTable)
dv.RowFilter = $"CodeText LIKE '%{term.Replace("'", "''")}%'"
DgvGlCodeView.DataSource = dv

Tip: avoid parsing TbxGlCode.Text to Integer for this scenario; keep it as a string so partial matches and non-numeric input do not throw. For very large datasets, consider pushing the filter into SQL rather than client-side conversion.

Recommended Answers

All 5 Replies

Member Avatar for rproffitt

From memory you can only perform "Like" on string types. I bet if you google the error you'll find examples to convert to string for the like.

Member Avatar for tinstaafl

If you're using the latest version of VB.net, which you should be, you can use the interpolation operator:

Dim Rows As DataRow() = TempTable.Select($"Code like '%{VerCode}%'")

The advantages of this, are cleanliness, the code is more concise and simpler, and any variable used inside the curly brackets is automatically converted to a string with the ToString() method.

On a side note, you don't really need the square brackets([]) in this case. Even though Select may be a keyword, it's isn't being used as a type. The compiler can easily tell the difference.

commented: Tried your suggestion , But still getting same error +3
Member Avatar for tinstaafl

Show an example of the data in the column and the value you're trying to match up.

Member Avatar for PM312

see attached

i have solved issue with find string method. but would like to know like method.

Capture2.PNG 10.53 KB
Member Avatar for tinstaafl

apparently with the column datatype set to numbers, using LIKE isn't possible. You can however use boolean operators with the numbers:

Dim Rows As DataRow() = TempTable.Select("Code > 99999 and Code < 200000")

This will return all rows with code starting with a 1

commented: Thanks +3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.