Storing asian text in sql server express using sqlclient  
Author Message
RolandKlein





PostPosted: .NET Framework Data Access and Storage, Storing asian text in sql server express using sqlclient Top

I have a problem storing Chinese text in an sql server express 2005 database which runs on a western version of XP.

There is no binding in my app. The data is typed in a user form and in a button_click event stored in a table using the command object of the sqlclient, executing sql statements like insert or update. Everything's fine, except that Chinese text will not be stored in the tables correctly, instead of the characters I only get question marks.

In the sql server express managing tool, I am not able to type in Chinese characters into the fields directly either, but I am able to copy and paste them there. That way the Chinese characters are being stored well in the db.

That's why I am thinking, the sql server is able to store Chinese characters, the GUI of my app is able to display Chinese charaters as well, only within the storing process using sqlclient something is going wrong.

Can anybody help me here. Actually I am quite disappointed, I thought these kind of problems belong to the past, when early access db showed similar behavior.

Thanks for your help

Roland



.NET Development14  
 
 
Jimmy Wu - MSFT





PostPosted: .NET Framework Data Access and Storage, Storing asian text in sql server express using sqlclient Top

Roland,

There are a couple of things to watch out for when dealing with globalization issues.

  1. Does the client application support unicode (UTF-8, UTF-16, etc.)
  2. Is the table column storing these strings unicode (nvarchar, nchar, ntext)

Since .Net Frameworks by default support unicode characters, I believe you will not encounter a problem for (1).

I would double check (2). If the table column is not unicode then you will have to make sure the column has the proper collation for Chinese characters, else the text data will be converted into the collation of the column which can cause the issue you are seeing where you get ' ' as the text value.

HTH

Jimmy



 
 
RolandKlein





PostPosted: .NET Framework Data Access and Storage, Storing asian text in sql server express using sqlclient Top

Hi Jimmy,

thanks for your quick reply.

The table columns are nvarchar(50). As I desribed, I can store Chinese text in the db through the SQL Server Management Studio Express, so there should not be a problem with the table or column settings.

That's how I do it:

cmdCommand = New SqlClient.SqlCommand(Anweisung, cnnSoc)

cmdCommand.ExecuteNonQuery()

As an example, Anweisung has the value "Insert into Daten values (' ', '', '', ' ','', '', '', '', '', '', '', '1', '', '', '', '', '', '', '1', '')".

However, the first string will be stored in the db as ' '.

How can I get around this

Thank you very much

Roland


 
 
RolandKlein





PostPosted: .NET Framework Data Access and Storage, Storing asian text in sql server express using sqlclient Top

Hi Jimmy, hi all of you who might be interested in this topic as well,

your keyword 'collation' took me on the right way. I searched the web again and found a solution for the problem:

It is necessary to type 'N' in front of the strings within the SQL statement. In my example, it should be "Insert into Daten values (N' ', N'', N'', N' ',N'', N'', N'', N'', N'', N'', N'', '1', N'', N'', N'', N'', N'', N'', '1', '')". See http://seba.studentenweb.org/thesis/db-sql.php.

Well, in Unicode times I find it kind of a strange thing having to do so, but it works.

This behavior should be communicated much more. It's globalization time, and you never know who will use your app with what kind of language. Especially when you use SQL Server on a web service...

Hope you won't be spending as long time on this problem as I had been.

Thanks

Roland


 
 
adinaronson





PostPosted: .NET Framework Data Access and Storage, Storing asian text in sql server express using sqlclient Top

Just wanted to say this was a big help for me too. So if you have the same problem with hebrew turning into question marks using N' ' will work.

My problem was while using Visual Studio 2005 - ASP.NET and SQL Server 2005

Adin