Dealing with a single quote - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Dealing with a single quote

Author

Topic

jcarr
Starting Member

2 Posts

Posted - 2011-10-10 : 09:39:21

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:Something %>" 
        SelectCommand="SELECT DISTINCT [xxx_Name] FROM [some_View]">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Something %>" 
        SelectCommand="SELECT DISTINCT [Some_Unit] FROM [vw_finalreports] WHERE ([Some_Operator] = @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:Something %>" 
        SelectCommand="SELECT [Year], [Some_Operator], [Some_Unit], [Some_pdfs] FROM [Some_View]" 
        FilterExpression="Some_Operator like '{0}%' and Some_Unit 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="Some_Operator" HeaderText="Operator" 
                    SortExpression="Operator_Name" />
                <asp:BoundField DataField="Some_Unit" 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/Some_Pdfs/" & Eval("Some_pdfs") %>' Width="85px"><%#Eval("Some_pdfs")%></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>

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-10-10 : 22:09:16

I would strongly advice you "NOT" to use the sql datasource control.

Its far much better, easier and flexible to use the Object datasource control, then you dont have to

1. Put your sql statement in your page
2. Place your connection string in your control.

If you know about the entity framework, which is new, I would advice you to go with that, else create a class and use the object datasource control to connect to it.

there are tonnes of examples here www.asp.net

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-11 : 08:15:48

Nobody can see what you have done here: "Edited by - jcarr on 10/11/2011 07:59:56" in your first post...
Everybody can see that there is no response to afrika's answer...

So what will happen now with this thread?

No, you're never too old to Yak'n'Roll if you're too young to die.

jcarr
Starting Member

2 Posts

Posted - 2011-10-11 : 08:37:42

Well, as I mentioned in my original post, I'm pretty new to the asp.net. And though the SqlDataSource is available in Visual Studio and used in hundreds of examples around the internet, including www.asp.net, afrika's only response was that it shouldn't be used and nothing offered to that would actually help me solve my imediate problem. So, I didn't really see where a response was warranted. But, thanks to a couple other forums, I did get the solution to my problem and I have my page working correctly now. This topic can be considered solved and closed.

Jack

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2011-10-11 : 08:52:39

AS i said to you, never use the sqldatasource, I honestly dont understand why microsoft introduced it in the first place.

1. create a class and put your select statement there

2. create a connection string in your web.config file and put your connection variable there

e.g.


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.ComponentModel;

/// <summary>
/// Summary description for phonebook
/// </summary>
/// 
[DataObject(true)]
public class phonebook
{

    [DataObjectMethod(DataObjectMethodType.Select,true)]
    public DataSet phonebook_data(string username)
	{
   
        string ConnectionString = WebConfigurationManager.ConnectionStrings["afrika"].ConnectionString.ToString();
     
       using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT DISTINCT [Some_Unit] FROM [vw_finalreports] WHERE ([Some_Operator] = '"+username+"')";
            command.Connection = connection;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet categoriesDataSet = new DataSet();
            adapter.Fill(categoriesDataSet);
            return categoriesDataSet;
         
        }


	}
}

and then in your gridview,

1. add a datasource and choose object datasource
2. configure your datasource to use your class.

works like magic, its neater, better and easier to update.

Better yet, if you understand the new entity framework or better yet MVC it makes life a lot easier.

Good luck

Ehi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources