Results 1 to 21 of 21

Thread: [RESOLVED] DataColumn.Expression - accent Insensitive

  1. #1

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Resolved [RESOLVED] DataColumn.Expression - accent Insensitive

    Hi there!

    Anyone here know how to use DataTable.Select expression but with with a Accent Insensitive way?

    Imagine in a DataTable values like this
    "Déjà vue"
    "Deja vue"
    "Potato"
    "Tomato"


    What kind of expression should I give to DataTable.Select to get both of "Déjà vue" and "Deja vue" when searching for "e" or "a" letter?

    In the SQL way I should the COLLATE keyword but what with .NET Expression?

    Hope to be clear enough
    Using VS 2010 on Fw4.0

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: DataColumn.Expression - accent Insensitive

    Since as you mentioned this is being done from a DataTable and not from a database table here are two thoughts to try.

    Code:
    Dim MyTable As New DataTable With {.TableName = "MyTable"}
    MyTable.Columns.AddRange(New DataColumn() {New DataColumn("Column1", GetType(System.String))})
    MyTable.Rows.Add(New Object() {"Déjà vue"})
    MyTable.Rows.Add(New Object() {"Deja vue"})
    MyTable.Rows.Add(New Object() {"Tomato"})
    MyTable.Rows.Add(New Object() {"Potato"})
    
    Dim test = MyTable.Select("Column1 like 'Déjà vue' OR Column1 like 'Deja vue'")
    Console.WriteLine(test.Count)
    
    Dim Items As String() = {"Déjà vue", "Deja vue"}
    Dim query = (From T In MyTable.AsEnumerable _
                 Where Items.Contains(T.Field(Of String)("Column1"))).ToArray
    Console.WriteLine(query.Count)

  3. #3

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    I think the LINQ way could work but I'm completely lost in LINQ
    Using VS 2010 on Fw4.0

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by Zakary View Post
    I think the LINQ way could work but I'm completely lost in LINQ
    The following does not use LINQ, instead takes a different approach. First off a BindingSource component is used which allows you to control positioning of your data, for example, you want to find ‘Tomato’ in Column1 use BindingSource.Find which returns a value of -1 if the item is not located or a value greater than -1 meaning the item was located. If it was found you can set the Position property of the BindingSource. Other nice things are shown in the example code such as retrieving column value for the currently selected row.
    Any ways the array TextValues can be static elements or not. Suppose you want to filter on three values rather than two, simply place three values in the array and call BuildCondition as done in the example. Button2 toggles the filter on/off.

    • You may have not used a BindingSource before, if not give it a shot
    • If you decide to use LINQ method the BuildCondition will work for this too.


    Code:
    WithEvents bsMyData As New BindingSource
    Private TextValues As String() = {"Déjà vue", "Deja vue"}
    ''' <summary>
    ''' Simulation loading data into a DataTable followed
    ''' by the DataTable becoming the DataSource of a BindingSource
    ''' component which then is assigned to the DataSource of a
    ''' DataGridView.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button1_Click( _
       ByVal sender As System.Object, _
       ByVal e As System.EventArgs) _
       Handles Button1.Click
    
       Dim MyTable As New DataTable With {.TableName = "MyTable"}
       MyTable.Columns.AddRange(New DataColumn() {New DataColumn("Column1", GetType(System.String))})
       MyTable.Rows.Add(New Object() {"Déjà vue"})
       MyTable.Rows.Add(New Object() {"Deja vue"})
       MyTable.Rows.Add(New Object() {"Tomato"})
       MyTable.Rows.Add(New Object() {"Potato"})
    
       DataGridView1.DataSource = Nothing
       bsMyData.DataSource = MyTable
       DataGridView1.DataSource = bsMyData
    End Sub
    ''' <summary>
    ''' Toggle filtering on/off
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button2_Click( _
       ByVal sender As System.Object, _
       ByVal e As System.EventArgs) _
       Handles Button2.Click
    
       If String.IsNullOrEmpty(bsMyData.Filter) Then
          bsMyData.Filter = BuildCondition("Column1", TextValues)
       Else
          bsMyData.Filter = ""
       End If
    End Sub
    ''' <summary>
    ''' Show current row data
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub bsFiltered_PositionChanged( _
       ByVal sender As Object, _
       ByVal e As System.EventArgs) _
       Handles bsMyData.PositionChanged
    
       If bsMyData.Current IsNot Nothing Then
          Console.WriteLine("Current value: '{0}'", _
                            CType(bsMyData.Current, DataRowView).Item("Column1"))
       Else
          Console.WriteLine("No current row")
       End If
    End Sub
    ''' <summary>
    ''' Create our filter for the BindingSource
    ''' </summary>
    ''' <param name="ColumnName">Column to filter on</param>
    ''' <param name="Values">A string array of items to filter on</param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Private Function BuildCondition(ByVal ColumnName As String, ByVal Values As String()) As String
       Dim Condition As String = ""
       For Each item In Values
          Condition &= String.Format("{0} like '{1}' OR ", ColumnName, item)
       Next
    
       Condition = Condition.TrimEnd
       If Condition.EndsWith("OR") Then
          Dim Pos As Integer = Condition.LastIndexOf("OR") - 1
          Condition = Condition.Substring(0, Pos)
       End If
    
       Return Condition
    End Function

  5. #5

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    First Thanks for your help

    I'll explain a bit more about my current problem.

    I'm using DataBinbing (Witch is quite new for me) to display a Datatable to a DataGridView, and in my form I have a Textbox that let the user enter a value to be searched the DataTable display in the DataGridView, the value entered in the TextBox is searched within all predefined grid column, example; Name, Street, City, Country, and so, but not the ID field form the DataTable.

    Before using DataBinding, I was using a Query SQL to locate the row with the COLLATE keyword to tell the SQL Engine to Ignore the Case and ignore the Accent ( COLLATE Latin1_General_CI_AI)

    But now that I use DataBinding I'm using BindingSource.Filter method to filter the DataSource, witch work great, except for a Accent character with are not ignored.

    In my Google research to find a solution, I did not find anything except for a LINQ method, but as say earlier I'm totally lost with LINQ.
    Using VS 2010 on Fw4.0

  6. #6
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    You can always parse the incoming text box and replace those possible special characters with the SQL single character wildcard which is an underscore.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  7. #7

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by mbutler755 View Post
    You can always parse the incoming text box and replace those possible special characters with the SQL single character wildcard which is an underscore.

    You mean something like this?

    Code:
    BindingSource.Filter = "Street Like '*D_j_ vue*'"
    Using VS 2010 on Fw4.0

  8. #8
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by Zakary View Post
    First Thanks for your help

    I'll explain a bit more about my current problem.

    I'm using DataBinbing (Witch is quite new for me) to display a Datatable to a DataGridView, and in my form I have a Textbox that let the user enter a value to be searched the DataTable display in the DataGridView, the value entered in the TextBox is searched within all predefined grid column, example; Name, Street, City, Country, and so, but not the ID field form the DataTable.

    Before using DataBinding, I was using a Query SQL to locate the row with the COLLATE keyword to tell the SQL Engine to Ignore the Case and ignore the Accent ( COLLATE Latin1_General_CI_AI)

    But now that I use DataBinding I'm using BindingSource.Filter method to filter the DataSource, witch work great, except for a Accent character with are not ignored.

    In my Google research to find a solution, I did not find anything except for a LINQ method, but as say earlier I'm totally lost with LINQ.
    The example I gave you with the BindingSource.Filter on my test kept the accent characters, otherwise I would not have suggested it. I am even using an earlier version of VS, VS2008.

  9. #9
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: DataColumn.Expression - accent Insensitive

    See attached project.
    Attached Files Attached Files

  10. #10
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by Zakary View Post
    You mean something like this?

    Code:
    BindingSource.Filter = "Street Like '*D_j_ vue*'"
    Yes.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  11. #11

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by kevininstructor View Post
    The example I gave you with the BindingSource.Filter on my test kept the accent characters, otherwise I would not have suggested it. I am even using an earlier version of VS, VS2008.
    Many thank for your sample code

    But I may miss explain me. Because it is not exactly what I'm looking for.

    In my case I would like to search for a single value, like "a" and have as a result having both "Déjà vue" and "Deja vue".
    Using VS 2010 on Fw4.0

  12. #12

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by mbutler755 View Post
    Yes.

    After a quick thought this could not work correctly, because If a search for "à" this would result like
    Code:
    BindingSource.Filter = "Street Like '*_*'"
    witch is ... everything. Not only word that contain character such as "a", "à" , "â".
    Using VS 2010 on Fw4.0

  13. #13
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by Zakary View Post
    Many thank for your sample code

    But I may miss explain me. Because it is not exactly what I'm looking for.

    In my case I would like to search for a single value, like "a" and have as a result having both "Déjà vue" and "Deja vue".
    I saw the underscores without looking at the entire line. When I in a situation like yours and I bring in the data with my select query, I would use something like:

    Code:
    sqlQuery="SELECT COL1, COL2, COL3 FROM SOMETABLE WHERE SOMECOL LIKE 'D_j_ vue'"
    Of course, you have to parse out the vowels that may have accents and replace them with underscores. I do not know if it will work with the filter the way you have it above.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  14. #14

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by mbutler755 View Post
    I saw the underscores without looking at the entire line. When I in a situation like yours and I bring in the data with my select query, I would use something like:

    Code:
    sqlQuery="SELECT COL1, COL2, COL3 FROM SOMETABLE WHERE SOMECOL LIKE 'D_j_ vue'"
    Of course, you have to parse out the vowels that may have accents and replace them with underscores. I do not know if it will work with the filter the way you have it above.
    Well I've give it shot, but no it not working, don't thing "_" is considered a wildcard neither "?"
    Using VS 2010 on Fw4.0

  15. #15
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  16. #16

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by mbutler755 View Post

    I do agree with the SQL version, but for the ADO.NET one it is a bit different:

    http://msdn.microsoft.com/en-us/libr...xpression.aspx
    Using VS 2010 on Fw4.0

  17. #17
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    According to your link:

    Code:
    Wildcard Characters
    
    Both the * and &#37; can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:
    
    "ItemName LIKE '*product*'"
    
    "ItemName LIKE '*product'"
    
    "ItemName LIKE 'product*'"
    
    Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
    I'm out of options. Sorry for the goose chase. I'll let you know if I can think of anything else.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  18. #18

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by mbutler755 View Post
    According to your link:

    Code:
    Wildcard Characters
    
    Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:
    
    "ItemName LIKE '*product*'"
    
    "ItemName LIKE '*product'"
    
    "ItemName LIKE 'product*'"
    
    Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
    I'm out of options. Sorry for the goose chase. I'll let you know if I can think of anything else.
    Well thanks for trying!
    Cheers!
    Using VS 2010 on Fw4.0

  19. #19
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: DataColumn.Expression - accent Insensitive

    Quote Originally Posted by Zakary View Post
    Well thanks for trying!
    Cheers!
    The nail in the coffin was the last sentence:

    Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

    The only other idea I have is so super ugly. You can get every permeation of an a or an o and search for all of those. Talk about slowing things down.
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  20. #20

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    I agree, I thought about it but it is, as you say, ugly.

    I'll give other try with LINQ maybe tomorrow.
    Using VS 2010 on Fw4.0

  21. #21

    Thread Starter
    Frenzied Member Zakary's Avatar
    Join Date
    Mar 2005
    Location
    Canada, Quebec, Montreal
    Posts
    1,654

    Re: DataColumn.Expression - accent Insensitive

    For the record
    here is the final solution
    http://www.vbforums.com/showthread.p...99#post4120599
    Using VS 2010 on Fw4.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width