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)
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
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.
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 can always parse the incoming text box and replace those possible special characters with the SQL single character wildcard which is an underscore.
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.
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".
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.
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 "?"
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.
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.