Sql server 2005 with ASP .NET encoding issue

Question

Hi,

I'm writing once again about my encoding issue... Now with some code samples.

In a nutshell: when saving to database input data, some language specyfic characters like polish 'ń' won't save - insted 'n' is saved. On the other hand, string: Adams æbler, with æ is saving.

Here is code begind code that does save stuff and displays data:

    protected void Page_Load(object sender, EventArgs e)
    {
        Label1.Text = "";
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["encoding"].ConnectionString))
        {
            conn.Open();
            var command = conn.CreateCommand();
            command.CommandText = "SELECT * FROM users";
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                Label1.Text += reader.GetString(0);
            }                
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["encoding"].ConnectionString))
        {
            conn.Open();
            var command = conn.CreateCommand();
            command.CommandText = "INSERT INTO users VALUES('" + Surname.Text + "')";
            command.ExecuteNonQuery();
        }  
    }

Default.aspx has meta tag:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Table looks like this:

CREATE TABLE [dbo].[Users]([Surname] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL ) ON [PRIMARY]

I don't know what else is needed to solve the issue. Help appreciated.

Thanks, Paweł

Answer 1

Your table definition appears to be the problem. Surname is defined with COLLATE Latin1_General_CI_AI. The AI part indicates Accent Insensitive. You would need to change the collation to Latin1_General_CI_AS, to preserve the data.

Answer 2

Hi,

finally I figured out what is wrong. I missed N prefix before string (it marks string as UNICODE):

command.CommandText = "INSERT INTO users VALUES(N'" + Surname.Text + "')";

Thanks, Paweł