Link to home
Start Free TrialLog in
Avatar of jcarr26
jcarr26Flag for United States of America

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>

Open in new window

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of jcarr26

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
Avatar of jcarr26

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
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jcarr26

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
Avatar of jcarr26

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
>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
Avatar of jcarr26

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>

Open in new window

Jack
Its WITHOUT suggested changes. Have you seen http:#36944030      ?
Avatar of jcarr26

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
Avatar of jcarr26

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
Avatar of jcarr26

ASKER

It was easy to follow if I would have read it entirely.
Glad that its working :-)