CODEWITHSUNDEEP
c#datatabledataviewrowfilter
Shakir Ahamed
Shakir Ahamed

Reputation: 1308

Using Like statement with DataView RowFIlter

I have a DataTable with some data and I want to filter with LIKE query so I have written a code like below and everything working fine, I'm generating the query and passing it to RowFilter object of DataView.

if (results.Count() == 2)
{
    if (results[0].ToString() == "" && results[1].ToString() != "")
    {
        searchString = String.Format("[" + searchBy + "] LIKE '%{0}'", results[1].ToString());
    }
    else if (results[0].ToString() != "" && results[1].ToString() == "")
    {
       searchString = String.Format("[" + searchBy + "] LIKE '{0}%'", results[0].ToString());
    }
    else if (results[0].ToString() != "" && results[1].ToString() != "")
    {
       searchString = String.Format("[" + searchBy + "] LIKE '{0}%{1}'", results[0].ToString(), results[1].ToString());
    }
}

if (searchString != "")
{
     DataView dv = new DataView(AccountList.Tables[0]);
     dv.RowFilter = searchString;
     var tab = dv.ToTable();
     DataSet data_set = new DataSet();
     data_set.Tables.Add(tab);
     ProcessDataSetAndLoadData(data_set);
}

In above code segment is working fine except last else if condition, In that condition I'm getting the searchString like this,

[AccountCode] LIKE 'c%l'

When I passing this to RowFilter I'm getting the eror like below,

Error in Like operator: the string pattern 'c%l' is invalid.

From all other conditions I'm getting the searchString like below and those conditions are working fine.

[AccountCode] LIKE '%c%'
[AccountCode] LIKE 'c%'
[AccountCode] LIKE '%c

My Question is why I'm getting the error only for the string like this ?. [AccountCode] LIKE 'c%l'

Please Advise me to complete my task.

Upvotes: 2

Views: 14176

Answers (2)

Shakir Ahamed
Shakir Ahamed

Reputation: 1308

As @KiranBeladiya advise me, I generated searchString like below and it's working fine , I'm getting correct output so updating an answer because of future viewers. 

searchString = String.Format("[" + searchBy + "] LIKE '"+ results[0].ToString() + "%' AND [" + searchBy + "] LIKE '" + "%" + results[1].ToString()+ "'");

this returning string like below,

[AccountCode] LIKE 'c%' AND [AccountCode] LIKE '%l'

So If pass this string to RowFilter object it is returning the same output of below string. 

[AccountCode] LIKE 'c%l'

Upvotes: 1

Kiran Beladiya
Kiran Beladiya

Reputation: 441

In DataView filter, wildcard characters are not allowed in middle of the string.

Please refer this MSDN article: https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx which says

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.

Upvotes: 2

Related Questions