Filter datatable with Like

[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

[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.

[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.

[tinstaafl]

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

[PM312]

see attached

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

[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