How to correctly define a pattern of the Like-Operator (VisualBasic) within "DataColumn.Expression" and the use of lambda?

Question

this is my first question on SO. Please forgive me if I make mistakes in the way I am asking, and please let me no if so.

I am currently trying to programmatically sort items from a ComboBox.

The data I am filling the CB with are by now processed as follows: - query from database(sqllite) - fill a datatable with result - fill item range from combobox

As the data has both, digit and non-digit-containing strings, the ordinary sort functions (SQL, DataTable, ComboBox) don't do the job. (Image)

So Far: My approach by now is a mixture from answers of similar topics. I try to use helper columns in a DataTable to separate the given values in string/integers, and then i want to Sort according to the content of these two helper values.

Code:

(Please focus on first "Try" Block. I copied the rest of the code too, because i think it explains my intentions).

' This function adds items to the referred ComboBox "cb" based on the data from the given DataTable "tbl". 
' "tbl" in every case consists of one column named "value":
' -The number of rows varies.
' -The column type is undefined (varies too).
' -Data can be like: "Sicherungsscheibe", "156.4", "780", "M10" (Examples!) 
'  These are values which are describing attributes like "length", "material", "thread", "diameter", "type".
Sub AddItemsOfTableToComboBox(ByRef cb As ComboBox, tbl As DataTable)


    ' Use temporary DataTable and copy each value from within the source column to "tempTB", 
    ' beacause a type conversion on already source bound columns is not possible. 
    Dim tempDT As New DataTable("tempDT")
    Dim DataSet1 As New DataSet
    DataSet1.Tables.Add(tempDT)
    tempDT = tbl.Clone()
    tempDT.Columns("value").DataType = GetType(String)
    For Each row As DataRow In tbl.Rows
        tempDT.ImportRow(row)
    Next
    ' Fill first temporary column "Scol1". Check first char (only) for a letter. This approach should be sufficient for values like "M12". 
    Try
        ' This one works, and gives the desired feedback: It copies every string from the column "value" to "Scol1" which are beginning with "B".
        tempDT.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(value,1,1) like 'B',substring(value,1,len(value)),'else')"})
        ' Same as above, beacuse case insensitive.
        tempDT.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(value,1,1) like 'B',substring(value,1,len(value)),'else')"})

        ' This one works. Here I want to copy every non digit char. But however, the feedback is always 'else'.
        tempDT.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(value,1,1) like '\D',substring(value,1,len(value)),'else')"})

        ' Doenst work: '[A-Z]' is invalid.  
        tempDT.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(value,1,1) like '[A-Z]',substring(value,1,len(value)),'else')"})

        ' This one works. But feedback is always 'else'.
        tempDT.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(value,1,1) like '[[]A-Z[]]',substring(value,1,len(value)),'else')"})
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    ' Fill second temp column "Scol2"
    Try
        tempDT.Columns.Add(New DataColumn("Scol2", GetType(Integer)) With {.Expression = "Convert(iif (substring(value,2,len(value)) like '\d',substring(value,2,len(value)),'0'),  'System.Int32')"})
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    'Dim View As New DataView(tbl)
    'View.Sort = "Scol1,Scol2"
    'View.Table = DataSet1.Tables("tempDT")

    ' Here I would had convert "tempDT" into "tbl" again somehow.

    With cb
        .Items.Clear()
        Dim items = tbl.AsEnumerable().Select(Function(d) DirectCast(d(0).ToString(), Object)).ToArray()
        If items.Length > 0 Then
            .Parent.Enabled = True
            .Items.AddRange(items)
            .Sorted = True
        Else
            ' Disable corresponding Panel(parent) if for some reason there are no items to fill the ComboBox with.
            ' (f.e. if there's a recorded class but no corresponding subclass, or
            ' there's a recorded class/category without any parts referred to it.)
            .Parent.Enabled = False
        End If
    End With
End Sub

I expect the pattern for defining a group of characters to be working. By now, I am not very familiar/experienced with using lambda expressions, handling DataTables/Views, or using regex.

...Any idea where I make mistakes?

score 0 · Answer 1 · answered May 23 '19 at 12:15

0

It's like this

"ColumnName LIKE '*value*'"

answered May 23 '19 at 12:15

Mr. Tripodi

809
1
6
7

With this, I can check for determined values. If f.e. I use `substring(value,1,1) LIKE '*a*'`, it retrieves the vaules beginning with an "a". But what would like is to sort by a group of chars, to retrieve all chars that are non-digit, but this her doesn't work: `substring(value,1,1) LIKE '*\D*'` or this `substring(value,1,1) LIKE '*[[]A-Z[]]*'` – Etienne Martin May 27 '19 at 13:05

How to correctly define a pattern of the Like-Operator (VisualBasic) within "DataColumn.Expression" and the use of lambda?

1 Answers1