CODEWITHSUNDEEP
c#sql-server-2008unicodeencodingcharacter-encoding
Martin at Mennt
Martin at Mennt

Reputation: 5737

Special character not saved in MS SQL

I have a small problem with this Czech character ů. This little bugger won't save into my MS SQL table when the update is triggered from my C# code.

If I manually add it in SQL Management Studio it is saved as it should, and it is also shown on the website as normal.

But the process of saving it from C# only ends in the character u beeing saved in the table and not ů.

My DB fields are of type nvarchar and ntext and the collation of the DB is French_CI_AS. I'm using MSSQL 2008.

Code:

SqlCommand sqlcomLoggedIn = new SqlCommand("UPDATE Table SET id = 1, title = 'Title with ů' WHERE id = 1", sqlCon, sqlTrans);
int status = sqlcomLoggedIn.ExecuteNonQuery();

Any thoughts?

Upvotes: 2

Views: 3784

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062510

The immediate problem is that you aren't using a unicode literal; it would have to be:

UPDATE Table SET id = 1, title = N'Title with ů' WHERE id = 1

the N is important.

The much bigger issue is that you should be using parameters:

int id = 1;
string title = "Title with ů";
// ^^ probably coming from C# parameters

using(var sqlcomLoggedIn = new SqlCommand(
    "UPDATE Table SET title = @title WHERE id = @id", sqlCon, sqlTrans))
{
    sqlcomLoggedIn.Parameters.AddWithValue("id", id);
    sqlcomLoggedIn.Parameters.AddWithValue("title", title);
    int status = sqlcomLoggedIn.ExecuteNonQuery();
}

then the problem goes away, you get to use cached query plans, and you avoid sql injection

Upvotes: 7

Related Questions