DataTable .Select and .RowFilter Escaping with Apostrophe’s

One of the great things about the DataTable or DataView is its ease of ability to Sort and Filter data. Often I utilize the .Select method of DataTable. This involves writing a short where clause string to provide to the method. The contents of this select filter in my scenario actually contained an apostrophe in the string. Thus I had something like this:

table.Select(“column1 = ‘Apostrophe’s’”);

You will note here, the single apostrophe used inside the value. Unfortunately, this is not automatically handled by the select logic, rather we need to properly escape our values. Interestingly enough, I found this article:

http://aspnetresources.com/blog/apostrophe_in_rowfilter

This article talks about Microsoft’s suggested usage for escaping this instance with a back slash:

table.Select(“column1 = ‘Apostrophe’s’”);

But, that fails horribly. You actually escape the character in this usage, with a second apostrophe, much like you would in a SQL statement:

table.Select(“column1 = ‘Apostrophe’’s’”);

This means that you simply have to use a global replace on the contents of your value if its dynamic (i.e. stringVal.Replace(“”, “’’”); ).

2 Comments

  1. Kelly Haul says:

    Enjoyed every bit of your blog.Thanks Again. Fantastic.

  2. Michelle Losilla says:

    Very useful. Thanks.