asked on

How to format the string value of the SPGridView property: FilteredDataSourcePropertyFormat when the value of the filter field has an apostrophe?

Hi,

I have a SPGriedView that works fine. It displays the data and sorts as expected, however when trying to filter a column that has special characters such a apostrophe it throws the Exception "Syntax error: Missing operand after 'Angelo' operator. System.Data.SyntaxErrorException"

For instance:
If I try to filter the column "Las_Name" where Last_Name like "Smith" that works fine, but
If I try to filter the column "Las_Name" where Last_Name like "D'Angelo" that trows the Exception "Syntax error: Missing operand after 'Angelo' operator. System.Data.SyntaxErrorException"

This is my code
...
...
SPGridView oGrid;
oGrid = new SPGridView();
oGrid.AutoGenerateColumns = false;
oGrid.AllowSorting = true;
oGrid.EnableSortingAndPagingCallbacks = true;
oGrid.EnableViewState = true;
oGrid.AllowFiltering = true;
oGrid.FilterDataFields = "First_Name, Last_Name";
oGrid.FilteredDataSourcePropertyName = "FilterExpression";
oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE '{0}'";
/* The value of the filter field is inserted at insertion point {0}. The name of the filter field is inserted at insertion point {1}. The default value of this string is "{0}". */
BoundField colFN = new BoundField();
colFN.DataField = "First_Name";
colFN.HeaderText = "First Name";
colFN.SortExpression = "First_Name";
oGrid.Columns.Add(colFN);

BoundField colLN = new BoundField();
colLN.DataField = "Last_Name";
colLN.HeaderText = "Last Name";
colLN.SortExpression = "Last_Name";
oGrid.Columns.Add(colLN);

I already tried this possible solutions I found in other Web sites, but none of them worked

Replace("'", "\'") this displays in the column D'Angelo,
but when filtering throws the exception "Syntax error: Missing operand after 'Angelo' operator. System.Data.SyntaxErrorException"

Replace("'", "\\'") this displays in the column D\'Amico but when filtering throws the exception "Syntax error: Missing operand after 'Angelo' operator. System.Data.SyntaxErrorException"

Replace("'", "''") this display in the column D''Amico but when filtering throws the exception "Syntax error: Missing operand after 'Angelo''operator. System.Data.SyntaxErrorException"

oGrid.FilteredDataSourcePropertyFormat = "{[1]} LIKE '{[0]}'" did not work either
when filtering throws the exception Input string was not in a correct format. System.FormatException.

oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE '{[0]}'" did not work either
when filtering throws the exception Input string was not in a correct format. System.FormatException

oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE '{[0]}'" did not work either
when filtering throws the exception Input string was not in a correct format. System.FormatException

How can I format the string value of "FilteredDataSourcePropertyFormat" so it works when the value of {0} has an apostrophe? for instance the value is D'Angelo or D'Amico

Exception.bmp

aibusinesssolutions

Have you tried HtmlEncode?

BoundField colLN = new BoundField();
colLN.HtmlEncode = True;

Also, how about
oGrid.FilteredDataSourcePropertyFormat = " {1} LIKE '[{0}]' " with the brackets on the outside

If neither of those work, you may need to write a custom formatter to replace ' with ''

aibusinesssolutions

Here is an example of writing your own custom formatter with SPGridView

http://www.hezser.de/blog/archive/2008/03/29/custom-formatting-in-a-spgridview.aspx

aibusinesssolutions

The answers that I gave will prevent the error that he is experiencing.

DanRollins

I concur with the delete request. It appears that the Asker has already tried the suggested options. I believe the problem may be related to having multiple field names in the filter field. There is some discussion of that issue here:
Filtering with SPGridView
http://www.sharepointblogs.com/bobsbonanza/archive/2007/05/14/filtering-with-spgridview.aspx

Rositta

ASKER

I am back.
I think the answer posted by aibusinesssolutions maybe could be a solution (I haven't tried yet) however, I think that complicated things a lot more.

I am almost sure that the problem have something to do with the string query
I tried to set a default value as fallow
this.oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE 'D''Amico'";
and that worked, but what I need is that the parameter {0} can be any of the selected by the user from the filter dropdown.

filterdropdown.JPG

aibusinesssolutions

That's what the custom formatter would do.

You would do something like:

this.oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE {0:myformat}";

myformat would then return the value formatted as you specify, in which you tell it to replace all apostrophes with ''

ASKER CERTIFIED SOLUTION

Rositta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial