Apostrophe In RowFilter

Hank
on January 19, 2005

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

Michael
on February 15, 2005

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.

Hermann Klinke
on April 4, 2005

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.

SelArom
on April 23, 2005

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?

Milan Negovan
on April 25, 2005

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.

mstrclark
on June 7, 2005

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

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

-mstrclark

mstrclark
on June 7, 2005

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

SelArom
on June 7, 2005

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

yasmin
on June 27, 2005

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.

Josh
on June 27, 2005

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

yasmin
on June 28, 2005

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

Micronn
on July 28, 2005

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.

keerthi
on November 14, 2005

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

R Hancock
on March 10, 2006

Very helpful. Thank You.

damightyz
on March 21, 2006

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

Venu
on March 27, 2006

Thanks a lot!
It works perfectly

- Venu

Scott
on April 20, 2006

Life Saver, Thanks!

Karthik
on May 2, 2006

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

jack
on June 20, 2006

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

SelArom
on June 20, 2006

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

usha
on June 20, 2006

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."

Parasuraman
on August 22, 2006

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

Parasuraman

cuonglt12h
on October 4, 2006

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

Prash
on March 20, 2007

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?

Milan Negovan
on March 20, 2007

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

Travis
on May 18, 2007

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.

Jaime
on October 19, 2007

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 ...

gurjeet
on April 10, 2008

hi mates

that replace method worked like a vharm...ta

Alex
on May 9, 2008

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

Milan Negovan
on May 14, 2008

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

Alex
on May 14, 2008

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! :)

Lokesh
on July 11, 2008

Thanks for the article.

Victor Ivanov
on April 8, 2009

Thanks a lot!

talk000
on May 8, 2009

Hi everbody, I am new to vb.net, Please help . Thanks in advance

dataview rowfilter doesnt seem to work, what ever is the filter, only the first row of the database is shown and not the filtered one
My code is:

--------------------------------------------------------------
Dim dv As DataView = New DataView(DataSet11.Table("traininfo"))
dv.RowFilter = "train_no = " + trainNo '"
Dim trainNum As String = Convert.ToString(dv.Table.Rows.Item(0).Item("train_no"))
trainRouteArrayList.Add(trainNum)
return trainRouteArrayList
-------------------------------------------------------------
But when I check the value in trainRouteArrayList by : Convert.ToString(trainRouteArrayList(0)) I get the first row of my train database and not the one which is expected , ie the row for the given train_no

sspsot
on August 17, 2009

I have the following
Dim sqlFilter as string = "Document = '"+ idvalue + "'"

I tried sqlFilter. Replace(" ' ", " "" ") ....without spaces but still get the error