Hi,
I have a GridView connected to a sqldatasource control. Everything is working great, updates, paging and filtering with one exception. When the user enters in a name like O'Reilly (with a single quote), the page errors. The error returned is:
Syntax error: Missing operand after 'Reilly' operator.
Here is the definition of the sqldatasource:
<asp:SqlDataSource ID="SqlDataSourcePersons" runat="server"
ConnectionString="<%$ ConnectionStrings:database %>"
SelectCommand="SELECT [Id], [FirstName], [LastName],
, [PersonTypeId], [WorkerId], [_workerNTId], [Title], [City] FROM [Person]"
FilterExpression="(FirstName like '{0}%') AND (LastName like '{1}%') AND (WorkerId like '{2}%') AND (City like '{3}%')" ProviderName="System.Data.SqlClient">
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxFirstName" Name="FirstName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxLastName" Name="LastName" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxWorkerId" Name="WorkerId" DefaultValue="%" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="TextBoxCity" Name="City" DefaultValue="%" PropertyName="Text" Type="String" />
</FilterParameters>
</asp:SqlDataSource>
Any suggestions would be appreciated.
Thank you, Jim
Hey
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'.
Following link might helpful:
http://www.aspnetresources.com/blog/apostrophe_in_rowfilter.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskfilteringsortingdatausingdataview.asp
