Single quote in a text field data in SQL Sever causes to throw an error when displayed in Gridview

skaleem1 used Ask the Experts™
I have the following SQL Query:

SELECT CurrencyID, Currency, [ISO-4217], Symbol FROM Currencies

The Currency column has one row value as:

Tongan Pa'anga

When I try to bind data to a gridview in the code behind (see the code section), I see the error (Please see the screen shot image file) that points to the following line of html generated code:

                  <td style="border-color:#344F3E">Tongan Pa'anga</td><td style="border-color:#344F3E">TOP</td><td style="border-color:#344F3E">T$</td><td style="border-color:#344F3E">Tonga</td><td style="color:#344F3E;border-color:#344F3E"><a href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridView1','Edit$220')" style="color:#344F3E;">Edit</a></td><td style="color:#344F3E;border-color:#344F3E"><a onclick="return ConfirmOnDelete('Tongan Pa'anga');" class="LinkButton" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridView1','Delete$220')" style="color:#344F3E;">Delete</a></td>

The issue is with the single quote and if I remove the single quote from the text, the error is removed. How can I resolve this issue? Should I change my query (see above) and replace the single quote with CHR(34) in the SQL? Please help.
this.GridView1.DataKeyNames = new string[] { "CurrencyID" };
                    this.GridView1.Columns.Add(new BoundField() { DataField = "CurrencyID", HeaderText = "CurrencyID", Visible = false });
                    this.GridView1.Columns.Add(new BoundField() { DataField = "Currency", HeaderText = "Currency",SortExpression="Currency" });
                    this.GridView1.Columns.Add(new BoundField() { DataField = "ISO-4217", HeaderText = "[ISO-4217]",SortExpression="ISO-4217" });
                    this.GridView1.Columns.Add(new BoundField() { DataField = "Symbol", HeaderText = "Symbol",SortExpression="Symbol" });

DataTable dt = LU.Tables[CURRENCIES];

GridView1.DataSource = dt;

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

If you have a single quote in your field, then you have to place double single quotes like this to get it work:

Tongan Pa''anga
onclick="return ConfirmOnDelete('Tongan Pa\'anga')


 onclick="return ConfirmOnDelete('Tongan Pa''anga')
try this
SELECT CurrencyID, replace (Currency, '''', '´'), [ISO-4217], Symbol FROM Currencies



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial