CODEWITHSUNDEEP
c#.netdatatableoledb
Alex
Alex

Reputation: 699

Query a datatable where text contains

Building on this question: how to run query on dataset?

I'm trying to query a datatable from my dataset where the text contains a string, similar to the String.Contains method or the sql LIKE operator.

Here's what I've tried so far:

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        DataTable tbl = globals.UserDataSet.Tables[0];
        DataRow[] tempDataRows = tbl.Select("USER_ID Like " + textBox1.Text + " OR THE_NAME Like " + textBox1.Text);
    }

This gives a System.Data.SyntaxError error.

Is it possible to query a datatable for text containing a substring? Is there a better way of doing this?

Upvotes: 9

Views: 32275

Answers (3)

Habib
Habib

Reputation: 223227

Couple of things:

  • First you need to enclose your values in single quote. (that is there in the linked question)
  • Second, if you are trying to compare for Contains then your values should have % (Just like SQL)

so your statement should be:

DataRow[] tempDataRows = tbl.Select("USER_ID Like '%" + textBox1.Text + "%' OR THE_NAME Like '%" + textBox1.Text +"%');

You can also use LINQ to DataSet/DataTable to filter your results like:

var query = tbl.AsEnumerable()
    .Where(r => r.Field<string>("USER_ID").Contains(textBox1.Text) &&
                r.Field<string>("THE_NAME").Contains(textBox1.Text));

Upvotes: 12

Doug Leary
Doug Leary

Reputation: 318

You need single quotes and % around string values. It's throwing a syntax error because the constructed query has the raw strings from the inputs, as in "WHERE THE_NAME Like John". Try this:

tbl.Select("USER_ID Like '%" + textBox1.Text + "%' OR THE_NAME Like '%" + textBox1.Text + "%'");

Upvotes: 1

D Stanley
D Stanley

Reputation: 152521

You need to add single quotes around your strings:

DataRow[] tempDataRows = tbl.Select("USER_ID Like '" + textBox1.Text + "' OR THE_NAME Like '" + textBox1.Text + "'");

Upvotes: 5

Related Questions