jcarr26
asked on
How to deal with apostrophes in DOTNET
I have been programming in classic asp for quite some time. Recently, I have been trying to learn asp.net and slowly convert our site over from classic asp. I've got some code for a page the utilizes two dropdownlists that are populated from tables in SQL using SqlDataSource controls. There is then another SqlDataSource controls that is used to accept the SelectedValue of one or both dropdownlist to filter the results in a Gridview. The code below works great, "unless", the results of either dropdownlist include text with an apostrophe(single quote) like in O'Brian. I know there has to be some way to do a Replace on the values coming from the first two SqlDataSources before the third one actually performs the Select command. I have tried a variety of things I have found around the many forums on the internet, but so far have been unable to come up with the correct solution. I'm hoping that someone can look at this code and figure out how I can fix what I have always easily fixed in classic asp. Thanks in advance.
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT DISTINCT [Operator_Name] FROM [vw_finalreports]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT DISTINCT [Unit_Name] FROM [vw_finalreports] WHERE ([Operator_Name] = @Operator_Name)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Operator_Name" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT [Year], [Operator_Name], [Unit_Name], [pdfNameRedacted] FROM [vw_finalreports]"
FilterExpression="Operator_Name like '{0}%' and Unit_Name like '{1}%'">
<FilterParameters>
<asp:ControlParameter Name="Operator" ControlID="DropDownList1" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Unit" ControlID="DropDownList2" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>
<asp:Label ID="Label2" runat="server" Text="Operator" Font-Bold="True"></asp:Label><br />
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource1" DataTextField="Operator_Name" AppendDataBoundItems="true"
DataValueField="Operator_Name" AutoPostBack="True">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList><br /><br />
<asp:Label ID="Label3" runat="server" Text="Unit" Font-Bold="True"></asp:Label><br />
<asp:DropDownList ID="DropDownList2" runat="server"
DataSourceID="SqlDataSource2" DataTextField="Unit_Name"
DataValueField="Unit_Name" AutoPostBack="True">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<br />
<br />
<br /><br />
<asp:Panel ID="Panel1" runat="server">
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" DataSourceID="SqlDataSource4"
EnableModelValidation="True" CellPadding="8" ForeColor="#333333"
GridLines="None" Width="100%" AutoGenerateColumns="False">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" />
<asp:BoundField DataField="Operator_Name" HeaderText="Operator"
SortExpression="Operator_Name" />
<asp:BoundField DataField="Unit_Name" HeaderText="Unit"
SortExpression="Unit_Name" />
<asp:TemplateField HeaderText="File Name" SortExpression="pdfNameRedacted" ControlStyle-Width="100%">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "http://www.apscservices.info/FinalReportsRedacted/" & Eval("pdfNameRedacted") %>' Width="85px"><%#Eval("pdfNameRedacted")%></asp:HyperLink><br />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle ForeColor="White" CssClass="resultheader" />
<PagerStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F0F0F0" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
</asp:GridView>
</asp:Panel>
</asp:Content>
ASKER
When I make that change I get the following error: Cannot find column [@Operator]. Both DDLs have an added ListItem with a value of "%" which brings up an unfiltered list in the GridView. But when I make any other selection from the DDL, I get that error.
Also, I "Probably" don't need to append the "%" to the @Operator or @Unit, since the values are actually and exact match to a matching column in the gridview.
Jack
Also, I "Probably" don't need to append the "%" to the @Operator or @Unit, since the values are actually and exact match to a matching column in the gridview.
Jack
ASKER
Also, I have only worked with VB, the limited C# I've tried has been strictly cut-n-paste stuff to see how something worked. I only mention this since I'm suspecting I'm going to be adding some additional CodeBehind. Thanks
Jack
Jack
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is a test page with the code displayed above with the suggested changes. When it first opens, you only see the two DDL. Selecting from the first one then gives you an error. http://www.apscservices.info/pipelinereports/FinalReports-test.aspx
Here is the link to the current page in production that works with the first code I listed without changes. http://www.apscservices.info/pipelinereports/FinalReports.aspx You will see it works fine unless you select "Painter's Mobile Home Park" or "Wilber's Mobile Home Park", which are the only two with apostrophes in the name so far. The second DDL will populate depending on the selected value of the first DDL. At this time there are no entries in the DB for Units that have an apostrophe, but doesn't mean there might not be in the future.
Jack
Here is the link to the current page in production that works with the first code I listed without changes. http://www.apscservices.info/pipelinereports/FinalReports.aspx You will see it works fine unless you select "Painter's Mobile Home Park" or "Wilber's Mobile Home Park", which are the only two with apostrophes in the name so far. The second DDL will populate depending on the selected value of the first DDL. At this time there are no entries in the DB for Units that have an apostrophe, but doesn't mean there might not be in the future.
Jack
ASKER
What truly surprises me in all of this, as much problem as an apostrophe(single quote) can be with DB, that a solutions hasn't been incorporated into the frameworks to account for them whether inserting data into the DB or doing a Select statement with a Where clause that might include one. And then I have spent several days and posted in many forums and there doesn't seem to be quick easy answer to something that has to be a problem regularly. I mean, in Ireland how do all the O'Brians and O'Malleys and such handle it. :))
Jack
Jack
>that a solutions hasn't been incorporated into the frameworks
Using Parameters is one of the solutions and it shields you from many other problems such as sql injection attacks.
Show me your current markup
Using Parameters is one of the solutions and it shields you from many other problems such as sql injection attacks.
Show me your current markup
ASKER
Here's the code with the suggested changes:
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT DISTINCT [Operator_Name] FROM [vw_finalreports]">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT DISTINCT [Unit_Name] FROM [vw_finalreports] WHERE ([Operator_Name] = @Operator_Name)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Operator_Name" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>"
SelectCommand="SELECT [Year], [Operator_Name], [Unit_Name], [pdfNameRedacted] FROM [vw_finalreports]"
FilterExpression="Operator_Name like @Operator and Unit_Name like @Unit">
<SelectParameters>
<asp:ControlParameter Name="Operator" ControlID="DropDownList1" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Unit" ControlID="DropDownList2" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="Label2" runat="server" Text="Operator" Font-Bold="True"></asp:Label><br />
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource1" DataTextField="Operator_Name" AppendDataBoundItems="true"
DataValueField="Operator_Name" AutoPostBack="True">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList><br /><br />
<asp:Label ID="Label3" runat="server" Text="Unit" Font-Bold="True"></asp:Label><br />
<asp:DropDownList ID="DropDownList2" runat="server"
DataSourceID="SqlDataSource2" DataTextField="Unit_Name"
DataValueField="Unit_Name" AutoPostBack="True">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<br />
<br />
<br /><br />
<asp:Panel ID="Panel1" runat="server">
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" DataSourceID="SqlDataSource4"
EnableModelValidation="True" CellPadding="8" ForeColor="#333333"
GridLines="None" Width="100%" AutoGenerateColumns="False">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" />
<asp:BoundField DataField="Operator_Name" HeaderText="Operator"
SortExpression="Operator_Name" />
<asp:BoundField DataField="Unit_Name" HeaderText="Unit"
SortExpression="Unit_Name" />
<asp:TemplateField HeaderText="File Name" SortExpression="pdfNameRedacted" ControlStyle-Width="100%">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "http://www.apscservices.info/FinalReportsRedacted/" & Eval("pdfNameRedacted") %>' Width="85px"><%#Eval("pdfNameRedacted")%></asp:HyperLink><br />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle ForeColor="White" CssClass="resultheader" />
<PagerStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F0F0F0" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
</asp:GridView>
</asp:Panel>
</asp:Content>
Jack
Its WITHOUT suggested changes. Have you seen http:#36944030 ?
ASKER
My bad! I only noticed part of the change, but didn't get it all in there. It seems to be working except for bring up "All", the default when you start on the page or select "all". The names with apostrophes are working now. Any suggestion for that? I promise to look closer at suggested code this time. I'm going to have to start studing up on the parameters and how to use them.
Jack
Jack
ASKER
I set a DefaultValue for both ControlParameters of % and that fixed that the problem. Thanks for all of your help and solving this.
Jack
Jack
ASKER
It was easy to follow if I would have read it entirely.
Glad that its working :-)
"Operator_Name like '{0}%' and Unit_Name like '{1}%'"
to
Operator_Name like @operator and Unit_Name like @unit"
When you use parameters, the use of single quote is not a problem but the problem now is how to append % at the end. You can intercept the values by handling the Selecting event of the SqlDataSource and modifying the values.