CODEWITHSUNDEEP
.netvb.net
D T
D T

Reputation: 3746

How select value like column in DataTable DotNet?

My code: 

 Dim dt As New DataTable
     dt.Columns.Add("No")
     dt.Columns.Add("Col2")
      Dim dr = dt.NewRow
     dr("No") = 1
     dr("Col2") = "abc"
     dt.Rows.Add(dr)
 Dim strText="abcdes"
 For Each drow As DataRow In dt.Select(String.Format("'{0}' like '%' & Col2 & '%'", strText)) 
 //value of Col2 exist in strText
 Next

It show error:

{"The expression contains unsupported operator '&'."} System.Data.EvaluateException

How select value like column in DataTable DotNet?

Upvotes: 1

Views: 3167

Answers (3)

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31743

While building the correct Filter value for a LIKE condition, you have to take care of the special characters.

There is an example here

public static string EscapeLikeValue(string valueWithoutWildcards)
{
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < valueWithoutWildcards.Length; i++)
  {
    char c = valueWithoutWildcards[i];
    if (c == '*' || c == '%' || c == '[' || c == ']')
      sb.Append("[").Append(c).Append("]");
    else if (c == '\'')
      sb.Append("''");
    else
      sb.Append(c);
  }
  return sb.ToString();
}

And as others mentioned, your syntax looks odd, try

dt.Select(String.Format("col2 LIKE '%{0}%'", EscapeLikeValue(strText))

Upvotes: 1

Reza Aghaei
Reza Aghaei

Reputation: 125217

You should use + instead of & and correct the format for LIKE:

String.Format(" '{0}' LIKE '%' + Col2 + '%' ", strText)

For each row, this checks if the value of Col2 is contained in strText it returns the row.

To check if Col2 contains strText, you should use String.Format("Col2 LIKE '%{0}%'", strText).

Example:

Dim foundRows() As DataRow
foundRows= dt.Select(String.Format(" '{0}' LIKE '%' + Col2 + '%' ", strText))
For Each dr As DataRow In foundRows
    'Do something
Next

To learn more about expression syntax:

Note:

If instead of getting a list of DataRow, you need to Filter a DataTable, for example to show only filtered rows in a DataGridView, use:

Me.DataGridView1.DataSource = dt
dt.DefaultView.RowFilter = String.Format("Col2 LIKE '%{0}%'", strText)

Upvotes: 2

Mohammad Adnan
Mohammad Adnan

Reputation: 119

Have you tried doing dt.AsEnumerable() and using LINQ functions?

In your case you can do something similar

var dataRows = dt.AsEnumerable(); var matchingRows = dataRows.Where(e=>e.Row<string>("Col2").Contains(strText)).Select();

This will return an IEnumerable which has all the rows that contain the value of strText

It's much easier to read and a much more preferred way.

Upvotes: -1

Related Questions