How to unescape special character when populating dropdownlist from a sql query

Question

I have the following C# code which populates a dropdownlist:

public void PopulateInsurance()
{
    SqlCommand cmd = new SqlCommand("GetInfo", new SqlConnection(ConfigurationManager.AppSettings["connectionstr"]));
    cmd.Connection.Open();

    SqlDataReader ddles = default(SqlDataReader);
    ddles = cmd.ExecuteReader();

    ddlI.Items.Clear();
    ddlI.DataSource = ddles;
    ddlI.DataValueField = "id";
    ddlI.DataTextField = "title";
    ddlI.DataBind();
    //set the default value for the drop down
    ListItem Item = new ListItem();
    Item.Text = "Any";
    Item.Value = "0";
    ddlInsurance.Items.Insert(0, Item);

    cmd.Connection.Close();
    cmd.Connection.Dispose();
}

One of the row in the SQL table has the following values:

id     title
9      Worker&#39;

The dropdownlist shows the title as displayed above (' -> which a single apostrophe')

How can I modify the code so it takes the ' or any other special character and unescapes it to show the character, in this example '.

You shouldn't have those characters in your database at all. Your escaping is broken elsewhere. — SLaks, Jan 12 '15 at 19:17
See [Decoding all HTML Entities](http://stackoverflow.com/questions/8348879/decoding-all-html-entities) and figure out how you can get that to work with the SqlDataReader. — CodeCaster, Jan 12 '15 at 19:19
We had a similar issue (someone's last name had an apostrophe). When we stored the data in the database, we escaped it then with a single quote. ex: O''Connor. — Jenn, Jan 12 '15 at 20:49

score 1 · Answer 1 · answered Jan 12 '15 at 19:42

1

The system.Uri class contains a UnescapeDataString method. Will that solve your problem?

http://msdn.microsoft.com/en-us/library/system.uri.unescapedatastring(v=vs.110).aspx

answered Jan 12 '15 at 19:42

Spock

4,330
2
14
20

1

Maybe. But what I want to unescape isn't an Uri per say but a column value in a SQL table... – SearchForKnowledge Jan 12 '15 at 19:49
You'll need to modify your code to add the items manually without using data binding. The fact that the value you want to Unescape isn't a uri shouldn't matter – Spock Jan 13 '15 at 04:25
I will test it out. Thanks. – SearchForKnowledge Jan 14 '15 at 15:51

How to unescape special character when populating dropdownlist from a sql query

1 Answers1