[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2882
  • Last Modified:

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
0
Rositta
Asked:
Rositta
  • 4
  • 2
1 Solution
 
aibusinesssolutionsCommented:
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 ''
0
 
aibusinesssolutionsCommented:
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
0
 
aibusinesssolutionsCommented:
The answers that I gave will prevent the error that he is experiencing.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
DanRollinsCommented:
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
0
 
RosittaAuthor Commented:
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
0
 
aibusinesssolutionsCommented:
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 ''
0
 
RosittaAuthor Commented:
Finally, the filter is working.

Following the very first sugestion of aibusinesssolutions. I tried again with HtmlEncode, but instated of HtmlEncode = true I used HtmlEncode=false. In addition to that I had to replace all apostrophes by "'" in the data
The only weird thing is how the data is diplayed in the SPGridview and the dropdown filter
For instance: In the SPGridview  the value "D'Angelo", is displayed as "D'Angelo" which is a good thing, but in the dropdown filter "D'Angelo" is displayed as D'Angelo
I tried to format the data with the property
colLN.HtmlEncodeFormatString = true or false and that did not help.
I think I have to open a new thread fro that problem :&
//The data is added to the dataTable as follow
dt = new DataTable();
dt.Columns.Add("Last_Name");
string _lastName = myFuctionGetLastName();
dt.Rows.Add(_lastName.Replace("'", "'"));
.....
//Then... 
this.oGrid = new SPGridView();
this.oGrid.AutoGenerateColumns = false;
this.oGrid.AllowSorting = true;
this.oGrid.EnableSortingAndPagingCallbacks = true;
this.oGrid.EnableViewState = true;
this.oGrid.AllowFiltering = true;
this.oGrid.FilterDataFields = "Last_Name";
this.oGrid.FilteredDataSourcePropertyName = "FilterExpression";
this.oGrid.FilteredDataSourcePropertyFormat = "{1} LIKE '{0}'";
this.oGrid.Sorting += new GridViewSortEventHandler(oGrid_Sorting);
BoundField colLN = new BoundField();
colLN.HtmlEncode = false;
colLN.DataField = "Last_Name";
colLN.HeaderText = "Last Name";
colLN.SortExpression = "Last_Name";
this.oGrid.Columns.Add(colLN);

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now