Question Problem with DataTable.Select and LIKE operator

[VBobCat]

Hello People!

My application has several ComboBox whose DataSource properties are always DataTable with two columns, one contains the PrimaryKey, and its name ("PK") is set in ComboBox.ValueMember property, and the other contains text descriptions, and its name ("ITEM") is set in ComboBox.DisplayMember property. So far, so good.

Well, the lists of options are huge, so I created an auxiliary DialogBox which the user can open for each ComboBox. This DialogBox has a TextBox and a ListBox, and OK and Cancel buttons as well. The ListBox.DataSource is initially set to be the same as the ComboBox in which the user called for help.

My intention is to provide a list whose content narrows as the user inputs some text, in order to ease the search for one specific item. For that, I did this:

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        Dim Filtro As String = "ITEM like '%" + TextBox1.Text.Trim.ToUpper & "%'"
        Try
            ListBox1.DataSource = ListaBase.Select(Filtro).CopyToDataTable
        Catch ex As Exception
            ListBox1.DataSource = ListaBase
        End Try
        If ListBox1.Items.Count > 1 Then ListBox1.ClearSelected()
    End Sub

* ListaBase is the same datatable provided to be the DataSource for that paticular ComboBox.

This works, as long as I keep the filter as simple as that: '%(what the user typed)%', and the list will narrow to show only entries whose description contains the exact typed expression, whatever stands before and after.

It is already some help, but what I really wanted to have is this:
- if the user types more than one word, it will find descriptions even if there are other words between those typed (it is good enough if it matches only when the words appear in the same order, for there is a sense in it);
- if there are diacritics involved (they are common in Portuguese), the search will match near correspondences, for instance: if description has the word "execução" and user types "execucao", they match; and vice-versa, that is, if user types correctly a word that was put in database without the proper diacritics.

For the first accomplishment, I tried replacing each space in user's typing with an "%", so that typing, for instance, "expediente interno" would result this expression: "ITEM like '%expediente%interno%'"

For the second accomplishment, I wrote a replacement-function so that typing, for instance, "execução" would result this expression: "ITEM like '%[eéèëê]x[eéèëê]c[uúùüû][cç][aáàäâã][oóòöôõ]%'"

My problem is: both these options raise an error in this case, like this:

System.Data.EvaluateException was caught
  Message=Error at operator Like: the pattern of the string '%EXPEDIENTE%INTERNO%' is not valid.
  Source=System.Data
  StackTrace:
       em System.Data.LikeNode.AnalyzePattern(String pat)
       em System.Data.LikeNode.Eval(DataRow row, DataRowVersion version)
       em System.Data.Select.AcceptRecord(Int32 record)
       em System.Data.Select.GetLinearFilteredRows(Range range)
       em System.Data.Select.SelectRows()
       em System.Data.DataTable.Select(String filterExpression)
       em SASP.DialogSelItemLista.TextBox1_TextChanged(Object sender, EventArgs e)
  InnerException:

The error is handled, but then ListBox will show the entire original list (what I wanted to happen only if the expression was not found at all)

As I successfully used these syntaxes in my SQL expressions for my database queries, I thought I could use the same here. Obviously, I am wrong. Could someone help me with this? Thanks a lot!

 

[Herman]

Contrary to SQL, the Datatable.Select method does not accept wildcards in the middle of a search expression. So in case of #1, what you want to do is one search per word:

Dim SearchString As String = "word phrase" ' User enters 2 words.
Dim Words() As String = SearchString.Split(" ") ' Split them in two strings.

Dim SearchPattern As String = Nothing
For Each Word In Words ' Create an AND clause for each word.
    SearchPattern += "AND ITEM LIKE '%" & Word & "%' "
Next
SearchPattern = SearchPattern.SubString(4) ' Remove the first 4 characters.

ListBox1.DataSource = ListaBase.Select(SearchPattern).CopyToDataTable

 

[VBobCat]

Thanks, that solves one of my problems indeed.
Is there any other approach than Datatable.Select?
I still have to deal with that issue with diacritics and, as this method doesn't have the full possibilities of an SQL query, maybe I should choose something else...

 

[Herman]

You could use a combination of Linq and RegEx:

Dim Results = From Row As DataRow 
              In MyDataTable.Rows 
              Where RegEx.IsMatch(Row.Item("Item").ToString(), "[eéèëê]x[eéèëê]c[uúùüû][cç][aáàäâã][oóòöôõ]")
              Select Row
              
For Each Row As DataRow In Results
    ...
Next

It definitely adds a lot of flexibility and readability. But you still need to take care of splitting the search words and adding ANDs to the Where clause for each.

 

[VBobCat]

You could use a combination of Linq and RegEx:

Dim Results = From Row As DataRow 
              In MyDataTable.Rows 
              Where RegEx.IsMatch(Row.Item("Item").ToString(), "[eéèëê]x[eéèëê]c[uúùüû][cç][aáàäâã][oóòöôõ]")
              Select Row
              
For Each Row As DataRow In Results
    ...
Next

It definitely adds a lot of flexibility and readability. But you still need to take care of splitting the search words and adding ANDs to the Where clause for each.

That was the perfect approach for me. As I have previously said, word order does have an importance in my particular case, so I don't need to split expression into several AND conditions.

That is what I've done (the reason of splitting the code into so many functions is because some of them will be used also to generate SQL masks and other uses):

    Private Sub TextBoxExpressao_TextChanged(sender As Object, e As EventArgs) Handles TextBoxExpressao.TextChanged
        TextBoxExpressao.BackColor = SystemColors.Window
        Dim FRE As String = Tratar.ExprFonRegEx(TextBoxExpressao.Text.Trim.ToLower)
        Dim RRE = From Row As DataRow In ListaBase.Rows Where System.Text.RegularExpressions.Regex.IsMatch(Row.Item("ITEM").ToString.ToLower, FRE) Select Row
        If RRE.Count > 0 Then
            ListBoxIt.DataSource = RRE.CopyToDataTable
            If TextBoxExpressao.Text.Trim <> "" Then TextBoxExpressao.BackColor = Color.PaleGreen
        Else
            ListBoxIt.DataSource = ListaBase
            If TextBoxExpressao.Text.Trim <> "" Then TextBoxExpressao.BackColor = Color.MistyRose
        End If
        If ListBoxIt.Items.Count > 1 Then ListBoxIt.ClearSelected()
    End Sub

    Friend Acentos As String() = {"AÁÀÂÃÄ", "CÇ", "EÉÈÊË", "IÍÌÎÏ", "NÑ", "OÓÒÔÕÖ", "UÚÙÛÜ", "aáàâãä", "cç", "eéèêë", "iíìîï", "nñ", "oóòôõö", "uúùûü"}

    Function ExprFon(ByVal texto As String)
        Dim tc As String, tf As New System.Text.StringBuilder
        For Each c As Char In texto.ToLower
            tc = c
            For Each a As String In Acentos
                If a.Contains(c) Then tc = "[" & a & "]"
            Next
            tf.Append(tc)
        Next
        Return tf.ToString
    End Function

    Shared Function ExprFonRegEx(ByVal texto As String) As String
        Dim tr As String = DelCarDupl(ExprFon(texto.Trim), " "c).Replace(" ", ".*")
        Return tr
    End Function

    Shared Function DelCarDupl(ByVal texto As String, ByVal caractere As Char) As String
        Dim l As Integer
        Do
            l = texto.Length
            texto.Replace(caractere.ToString & caractere.ToString, caractere.ToString)
        Loop While l > texto.Length
        Return texto
    End Function