• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

preserving apostrophe's from data inserted into database, to displaying on web via ASP.NET

Hello,

I have a custom windows service that monitors a specific pop3 account, and when it receives a new message, adds it to a sql server 2005 db. if the message contains an apostrohpe, the insert statement breaks because of the terminated string constant. i tried to solve this by first checking the data for apostrophes, and replacing them with ’. this works fine except when i try to display it on the page, the & is converted to & resulting in ’ which obviously doesnt display correctly. how can i preserve the stored ampersand and have it not convert to &?

here is some of the code i am using:

<asp:GridView ID="GridView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"
            ForeColor="#333333" GridLines="None" Width="100%" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" DataKeyNames="Ticket #" AllowPaging="True" AllowSorting="True" AutoGenerateSelectButton="True" AutoGenerateColumns="False">
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <EditRowStyle BackColor="#999999" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <PagerSettings Mode="NumericFirstLast" />
            <Columns>
                <asp:BoundField DataField="Ticket #" HeaderText="Ticket #" InsertVisible="False"
                    ReadOnly="True" SortExpression="Ticket #" />
                <asp:BoundField DataField="Reported On" HeaderText="Reported On" SortExpression="Reported On" />
                <asp:BoundField DataField="Priority" HeaderText="Priority" SortExpression="Priority" />
                <asp:BoundField DataField="Reporter Name" HeaderText="Reporter Name" SortExpression="Reporter Name" />
                <asp:BoundField DataField="Summary" HeaderText="Summary" SortExpression="Summary" />
                <asp:BoundField DataField="Ticket Status" HeaderText="Ticket Status" SortExpression="Ticket Status" />
                <asp:BoundField DataField="Resolution Status" HeaderText="Resolution Status" SortExpression="Resolution Status" />
                <asp:BoundField DataField="Assigned To" HeaderText="Assigned To" SortExpression="Assigned To" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SupportTickets %>"
            SelectCommand="SELECT [TicketID] as [Ticket ,[Products].[ProductName] as [Product Name,[ProductComponents].[ComponentName] as [Sub Component],[StatusCodes].[CodeLabel] as [Ticket Status,[ResolutionCodes].[CodeLabel] as [Resolution Status,[Users].[Name] as [Assigned To,[Summary,[FullText,[ReporterEMail] as [Reporter E-Mail,[ReporterName] as [Reporter Name,[Versions].[VersionValue] as [Product Version,[Timestamp] as [Reported On,[Priorities].[PriorityLabel] as [Priority] FROM [SupportData].[dbo].[Tickets],[SupportData].[dbo].[Products],[SupportData].[dbo].[ProductComponents],[SupportData].[dbo].[StatusCodes],[SupportData].[dbo].[ResolutionCodes],[SupportData].[dbo].[Users],[SupportData].[dbo].[Versions],[SupportData].[dbo].[Priorities] WHERE [Products].[ProductID] = [Tickets].[ProductID] AND [ProductComponents].[ComponentID] = [Tickets].[ComponentID] AND [StatusCodes].[CodeID] = [Tickets].[Status] AND [ResolutionCodes].[CodeID] = [Tickets].[Resolution] AND [Users].[UserID] = [Tickets].[AssignedTo] AND [Versions].[VersionID] = [Tickets].[Version] AND [Priorities].[PriorityID] = [Tickets].[Priority]">
        </asp:SqlDataSource>
0
RepriseMIS
Asked:
RepriseMIS
  • 4
  • 3
  • 2
1 Solution
 
valkyrie_ncCommented:
Have you tried escaping it with a \ ?  It sounds like your replacement procedure works fine; you just need to force the system to display it correctly.  Maybe you could use &#39; instead of &rsquo; ?


hth

valkyrie_nc
0
 
RepriseMISAuthor Commented:
&#39; would produce the same result because the ampersand would still be converted to &amp;. i think it happens on the server-side before the page is rendered. so the question remains, how do i prevent ASP.NET from converting &rsquo; to &amp;rsquo;?
0
 
valkyrie_ncCommented:
How about escaping the & with a \ :  \&rsquo; ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RepriseMISAuthor Commented:
Nope, now i get \&amp;rsquo;
0
 
TimCotteeCommented:
Hi RepriseMIS,

The standard way to do this is to double the ' character.

So in your insert / update statement, ensure that you use a replace statement to do this.

Tim Cottee
0
 
RepriseMISAuthor Commented:
what would that look like? i'm not very advanced with SQL yet.
0
 
TimCotteeCommented:
RepriseMIS,

It would be in your service that monitors the pop3 account that you would need to make the change. Can you show the bit of code that inserts the message into your database from that application as that is where it needs to be changed. The display of the apostrophe is not actually a problem as such.

Tim
0
 
RepriseMISAuthor Commented:
private int AddTicket(string summary, string fulltext, string date, string reportername, string reporteremail)
        {
            dbm = new DatabaseManager(DB_Hostname + "\\" + DB_Instance, DB_Database, DB_Username, DB_Password, eventLog1);
            dbm.OpenDatabase();

            StringBuilder cmd = new StringBuilder();
            cmd.Append("INSERT INTO [SupportData].[dbo].[Tickets] ");
            cmd.Append("([ProductID],[ComponentID],[Status],[Resolution],[AssignedTo],[Summary],[FullText],[ReporterEMail],[ReporterName],[Timestamp],[Version],[Priority]) ");
            cmd.Append("VALUES (");
            cmd.Append("0,0,0,0,1,'"+summary+"','"+fulltext+"','"+reporteremail+"','"+reportername+"','"+date+"',0,0)");

            dbm.ExecuteQuery(cmd.ToString());

            cmd = new StringBuilder();
            cmd.Append("SELECT [TicketID] FROM [SupportData].[dbo].[Tickets] WHERE [Timestamp] = '" + date + "'");
            dbm.OpenQuery(cmd.ToString());
           
            int id = 0;
           
            if (dbm.RecordCount > 0 && !dbm.EOF)
            {
                id = dbm.GetIntegerData("TicketID");
            }
            dbm.CloseQuery();
            dbm.CloseDatabase();
            dbm = null;
            return id;

        }
0
 
TimCotteeCommented:
RepriseMIS,

Change this line
            cmd.Append("0,0,0,0,1,'"+summary+"','"+fulltext+"','"+reporteremail+"','"+reportername+"','"+date+"',0,0)");
To
            cmd.Append("0,0,0,0,1,'"+summary.replace("'","''")+"','"+fulltext.replace("'","''")+"','"+reporteremail+"','"+reportername.replace("'","''")+"','"+date+"',0,0)");

As the reporteremail shouldn't contain ' characters and the date will not either.


Tim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now