How can I strip a single quote from a dataTable.Select( ) query in C#?

Question

So I have a list of dealers names and I am searching them in my datatable -- problem is, some chucklehead HAS to be named 'Young's' --- this causes an error.

drs = dtDealers.Select("DealerName = '" + dealerName + "'");

So I tried to replace the string (although it didnt work for me - maybe I dont know how to use replace...)

 DataRow[] drs;
                if (dealerName.Contains("'"))
                {
                    string dealerSearch = dealerName;
                    dealerSearch = dealerSearch.Replace("'", "\'");
                    drs = dtDealers.Select("DealerName = '" + dealerSearch + "'");
                }
                else
                {
                    drs = dtDealers.Select("DealerName = '" + dealerName + "'");
                }

Anyone have any good ideas?

Thanks! Todd

score 10 · Answer 1 · answered Jun 03 '11 at 00:16

10

i usually use double single quote to denote one i.e.

DealerName = 'Young''s'

Try that

answered Jun 03 '11 at 00:16

Dimitri

6,923
4
35
49

3

This is what worked for me. drs = dtDealers.Select("DealerName = '" + dealerName.Replace(@"'", "''") + "'"); Thanks. – Todd Vance Jun 03 '11 at 00:28

score 4 · Accepted Answer · answered Jun 03 '11 at 00:20

4

You can use the @ operator called the verbatim operator which means literal in latin. It will not do any interpretation of the characters within the string that would otherwise mean something.

answered Jun 03 '11 at 00:20

BRampersad

862
1
12
25

score 3 · Answer 3 · answered Jun 03 '11 at 00:19

3

Ultimately you might want to consider using a parameterized query. This will solve your problem and some others you may not have thought of yet. I found this link which details the reasons why and how.

answered Jun 03 '11 at 00:19

Richard Brightwell

3,012
2
20
22

score 2 · Answer 4 · answered Jun 03 '11 at 00:27

2

The double-single quote will fix the problem, but an extra note: Your "else" block is redundant.

You could accomplish the same thing by doing this:

 DataRow[] drs;
                if (dealerName.Contains("'"))
                {
                    string dealerSearch = dealerName;
                    dealerSearch = dealerSearch.Replace("'", "''");
                }
                drs = dtDealers.Select("DealerName = '" + dealerName + "'");

answered Jun 03 '11 at 00:27

Daniel Mann

57,011
13
100
120

I dont think it was .. I was searching with the dealerSearch if I had a single quote but searching with the dealerName if I did not... I did not want to changed the 'dealerName' variable because I use it later on ... – Todd Vance Jun 03 '11 at 00:31
You don't need to change dealerName, and the if block in this answer is redundant, too. You can *always* use dealerSearch for the criteria because if the name does not contain the single-quote character, the replace function will just return the unmodified string. That will make your code simpler. See the answer from CharithJ – phoog Jun 03 '11 at 03:39

score 1 · Answer 5 · answered Jun 03 '11 at 00:32

1

drs = dtDealers.Select("DealerName = '" + dealerName.Replace("'", "\\'") + "'");

answered Jun 03 '11 at 00:32

CharithJ

46,289
20
116
131

score 0 · Answer 6 · answered Jun 03 '11 at 00:14

0

You need to escape the \: dealerSearch.Replace("'", "\\'") (or use an @ literal)

Your \ is treated as a C# escape and doesn't appear in the actual string.

answered Jun 03 '11 at 00:14

SLaks

868,454
176
1,908
1,964

How can I strip a single quote from a dataTable.Select( ) query in C#?

6 Answers6

Linked