Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to deal with apostrophes in DOTNET

Posted on 2011-10-10
13
Medium Priority
?
1,032 Views
Last Modified: 2012-05-12
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>

Open in new window

0
Comment
Question by:jcarr26
  • 8
  • 5
13 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36942902
Try changing from

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

Author Comment

by:jcarr26
ID: 36943264
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
0
 

Author Comment

by:jcarr26
ID: 36943312
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 36944030
Try changing your sql data source from

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



to




<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:PipelineApp %>" 
        SelectCommand="SELECT [Year], [Operator_Name], [Unit_Name], [pdfNameRedacted] FROM [vw_finalreports] Where 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>

Open in new window

0
 

Author Comment

by:jcarr26
ID: 36944109
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
0
 

Author Comment

by:jcarr26
ID: 36944164
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36944356
>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
0
 

Author Comment

by:jcarr26
ID: 36944385
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>

Open in new window

Jack
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36944498
Its WITHOUT suggested changes. Have you seen http:#36944030      ?
0
 

Author Comment

by:jcarr26
ID: 36944694
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
0
 

Author Comment

by:jcarr26
ID: 36944724
I set a DefaultValue for both ControlParameters of % and that fixed that the problem.  Thanks for all of your help and solving this.

Jack
0
 

Author Closing Comment

by:jcarr26
ID: 36944735
It was easy to follow if I would have read it entirely.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36944811
Glad that its working :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question