Apostrophe In RowFilter

When you filter a DataView by setting its RowFilter property to a string watch out for apostrophes or you’ll get an exception. What you need to do is double each apostrophe, not escape it as MSDN suggests.

MSDN says:

If a filter expression contains reserved characters, such as a single quotation mark, those characters must be specified using escape characters. For example, the following expression shows how to use an escape character to include an apostrophe in the expression: CompanyName = 'Margie\'s Travel'.

Not true. The code still throws an exception. This works:

dv.RowFilter = "CompanyName = 'Margie''s Travel'"

I spent about 20 minutes trying to figure out an error related to this. Tonight I opened up Microsoft ADO.NET (Core Reference) and found this sample line on page 359, which backs up my point:

vue.RowFilter = "[Spaces in Column Name] = '0''Malley'"

Nice catch, had the same issue about 6 months back. Drove me crazy.

Ok i understand using the rowfilter with one value to check for in the expression but what if you wanted to filter on two pieces of data maybe customer name and customer class, because the customer list is over 4,000 records so the rowfilter search needs to break things down father than one column.

Hey Michael, I've written an ExpressionBuilder class that takes care for everything. Maybe I'll publish it on The Code Project if a have time. I have basically a public AddCondition method (that's overloaded for every type) and an Expression property. The AddCondition escapes every column name and data type. The Expression property then returns the build expression. Before this is accessed, I store every condition in an ArrayList and then use it to build the expression. You can use it then to filter on as many pieces as I want.

I'm having this problem but in VB.NET. I get a

System.Data.SyntaxErrorException: Syntax error: Missing operand after 's' operator.

whenever a user inputs data that has an apostrophy, like Fry's. How can I fix this if I don't know what the user is going to put in beforehand?

I simply wrote a method which makes a string safe for these kinds of row filters. Nothing fancy: if it sees an apostrophe, it doubles it. Whenever I set a row filter, I always pass it through this method first.

Need to replace all the apostrophes with double apostrophes. The code below will work.

Vb.Net
MyString= Regex.Replace(MyString, "[.']", "''")

-mstrclark

It occurred to me, after my last post, that you could remove the apostrophes in a couple of other ways:

From a text box:
MyTextBox.Text.Replace("'", "''")

From a listbox
MyListBox.Text.Replace("'", "''")

From a string variable:
MyString MyString.Replace("'", "''")

Every occurrence of the apostrophe will be replace
with the second parameter of the "Replace()" function.

-mstrclark

Thanks mstrclark. I had actually tried that before and indeed it did work! Thanks for the help.

I tried
MyTextBox.Text.Replace("'", "''")

but I still keep getting the same error message about using an apostrophe. I'm not sure what I could be doing wrong.

I think you have to set the textbox equal to MyTextBox.Text.Replace("'", "''") because I think .Replace() returns a copy of the string. Have you tried
MyTextBox.Text = MyTextBox.Text.Replace("'", "''")

That might work. hope that was helpful

-SelArom

Thanks a lot.
IT's working perfectly!
I appreciate it.

If you use the String.Replace method and an expression with LIKE, you should also take care of the '*' character. It can't be in the middle of the string.

Actually i tried using replace method it still didn't work
actually i want to check only one item in datagrid which has apostrophe
can somebody help

Very helpful. Thank You.

I was wondering if someone could be a huge help to me... I am getting the following error when trying to apply a dataview rowfilter:

"System.Data.SyntaxErrorException: Syntax error: Missing operand after 'S' operator."

The rowfilter is a dynamic string similar to this: Rep IN ('GG', 'CT'), where "Rep" is the column name.

NOTE: This works perfectly on my browser but is not filtering properly on other browsers (generating the error message above).

Any help would be greatly appreciated!

Thanks,
Jason

Thanks a lot!
It works perfectly

- Venu

Life Saver, Thanks!

Thanks a lot. Was of real help. Saved me a lot of time.

I am using asp.net and i have tried Replace("'",""") but it does not work for apostrophe.Please help.

jack you need to put 4 quotes in the second parameter. the outer pair is the enclosure, the inner pair represents the empty string "" with which you're replacing the apostrophe.

try .Replace(" ' ", " "" ") without the spaces, of course!

hope that helps
-SelArom

Am trying to do as below:
int intLinePricgNumId = int.Parse(arrIntLinePricingIDs[0].ToString());
drLinePricingRow["LinPricgNumId"] = intLinePricgNumId;

But it throwing exception as "Syntax error: Missing operand after '=' operator."

Hi,
How to filter a column with sspaces indataview. The code looks like,
dvGRP.RowFilter = "Call Attempts Adjusted Capacity 'NaN'"
Thanks regards

Parasuraman

System.Data.SyntaxErrorException: Syntax error: Missing operand after '12' operator.

Hi,

When I am trying to excecute following line I am getting an exception.
dv.RowFilter ="UNIQUE_ID = 3";

Error is
Object reference not set to an instance of an Object.

Can you please help?

Prash, I believe your DataView (dv) is null in the first place.

Thank You!!

I replaced my reference of MyTextBox.Text.ToString to MyTextBox.Text.Replace("'", "''").ToString and it handles the "O'Brien" clan just fine now.

IF you return an extra field from your database like this an use it in the value field of the dropdown it should work.


SELECT

EventDescription, REPLACE(EventDescription, '''', '''''') as EventDescriptionValueField

FROM ...

hi mates

that replace method worked like a vharm...ta

Hi,

I need to apply a filter to a set of columns that contain numbers.

If I specify only one column in the filter, then I can apply it successfuly. For example: "Delta Pressure = 4"

However when I try to filter for more than one column, such as:

"Imbalance = 4 OR Delta Pressure = 4"

I get: "Syntax error: Missing operand after 'Status' operator."

I have tried applying parenthesis without success. Does anyone have some insight on this?

Thanks

Something tells me it's that space between Delta and Pressure. I'm not sure what the proper syntax is. [Delta Pressure], perhaps?

Thanks for your reply Milan. I think you are right, once I add brackets around the name of the column it works!

so this is the syntax:

view.RowFilter = "[" + ColumnName + "]" + Operator + SearchNumber

and that works for "=", ">", "=", "<="

Thanks! :)

Thanks for the article.