datatable .net 2.0 select with double single quote returns no results

Question

I've datatable as follows

DataTable ex= new DataTable();
ex.Columns.Add("Object");
ex.Columns.Add("Found");

ex.Rows.Add("vase''s","True");

string keyword="vase''s";

DataRow [] drs = ex.Select("[Object] like '%" + keyword + "%'");

drs is always empty I've tried with equal I've the same results and I've tested in other frameworks I've the same also what's wrong in my select statement ?!

Update I realized that it's due to single quote is considered as one in the query statement but how can I do that search in a generic way

are there anyway as I don't want to call replace function if there's no single quotes it cost me a check — user690069, Aug 15 '15 at 23:45
It will cost more to perform that check than to replace it anyways — Rubens Farias, Aug 15 '15 at 23:48

score 0 · Answer 1 · answered Aug 15 '15 at 23:45

Modify your code to be like below

        DataTable ex = new DataTable();
        ex.Columns.Add("Object");
        ex.Columns.Add("Found");

        ex.Rows.Add("vase's", "True"); //don't escape here

        string keyword = "vase''s";
        //Use a equal to comparison rather than using LIKE operator
        DataRow[] drs = ex.Select("[Object] = '" + keyword + "'");

score 0 · Answer 2 · answered Aug 15 '15 at 23:52

The string value in your table literally has two single quotes. You do not escape single quotes in C# strings. The Select statement just needs two single quotes to tell the parser that the quote is not the end of the constant string value. So either keep a single quote in the value:

ex.Rows.Add("vase's", "True");

or search for a string with two single quotes:

string keyword = "vase''''s";

score 0 · Answer 3 · answered Aug 15 '15 at 23:54

When you added that DataRow, you're informed the vase''s value (with two single quotes) and you (probably) don't want it.

However you MUST escape it in the query, like below:

DataTable ex = new DataTable();
ex.Columns.Add("Object");
ex.Columns.Add("Found");
ex.Rows.Add("vase's", "True");

foreach (DataRow row in ex.Rows)
    Console.WriteLine(row["Object"]);   // "vase's"

string keyword = "vase's";
DataRow[] drs = ex.Select("[Object] like '%" + keyword.Replace("'", "''") + "%'");
Console.WriteLine("{0} rows found", drs.Length);

datatable .net 2.0 select with double single quote returns no results

3 Answers3