Unicode problem: Can read, but not store Chinese characters in database!

M

Morten

Hi there!

I'm building a VB.NET application that connects to a MS SQL 2000 Server
through ADO.NET.

NOTE: My application can correctly read and show Chinese characters
retrieved from the database (they are stored in a 'nchar' column).

However, when I try to store some Chinese characters through an INSERT or
UPDATE statement, the database only receives a questionmark ('?') for each
Chinese character.

I have tried through both System.Data.OleDb.* and System.Data.SqlClient.*,
but they both yield the same unsatisfying result.

My code is something like (here with System.Data.OleDb):

------------------------------------------
Dim cn As New System.Data.OleDb.OleDbConnection("Data Source=XXXXX;
Provider=SQLOLEDB; Initial Catalog=XXXXX; User Id=XXXXX; Password=XXXXX;")
Dim cmd As New System.Data.OleDb.OleDbCommand("UPDATE myTable SET
chineseText=? WHERE id=1;", cn)
Dim par As New System.Data.OleDb.OleDbParameter("name",
System.Data.OleDb.OleDbType.Char)
par.Value = myChineseTextBox.Text
cmd.Parameters.Add(par)
cmd.ExecuteNonQuery()
cn.Close()
------------------------------------------

Oh yeah: I'm using the .NET 1.0 framework.

Please help me, since this is driving me nuts. Am I missing something in the
connection string, or should I do something special to my objects, so they
don't drop the Chinese characters?

Cheers,

~Morten ;-)
 
J

Jon Skeet [C# MVP]

Morten said:
I'm building a VB.NET application that connects to a MS SQL 2000 Server
through ADO.NET.

NOTE: My application can correctly read and show Chinese characters
retrieved from the database (they are stored in a 'nchar' column).

However, when I try to store some Chinese characters through an INSERT or
UPDATE statement, the database only receives a questionmark ('?') for each
Chinese character.

I have tried through both System.Data.OleDb.* and System.Data.SqlClient.*,
but they both yield the same unsatisfying result.
Click to expand...

See http://www.pobox.com/~skeet/csharp/debuggingunicode.html for some
hints.
My code is something like (here with System.Data.OleDb):

------------------------------------------
Dim cn As New System.Data.OleDb.OleDbConnection("Data Source=XXXXX;
Provider=SQLOLEDB; Initial Catalog=XXXXX; User Id=XXXXX; Password=XXXXX;")
Dim cmd As New System.Data.OleDb.OleDbCommand("UPDATE myTable SET
chineseText=? WHERE id=1;", cn)
Dim par As New System.Data.OleDb.OleDbParameter("name",
System.Data.OleDb.OleDbType.Char)
par.Value = myChineseTextBox.Text
cmd.Parameters.Add(par)
cmd.ExecuteNonQuery()
cn.Close()
------------------------------------------
Click to expand...

Are you sure that type should be OleDbType.Char rather than
OleDbType.WChar?

(In SqlClient.* you'd use SqlDbType.NChar.)
 
K

Kevin Yu [MSFT]

Thanks for Jon's quick response!

Hi Morten,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you were unable to insert records with
Chinese characters. If there is any misunderstanding, please feel free to
let me know.

Based on the code you have provided, I think the problem has something to
do with the type you specified for the parameter. The type currently used
is System.Data.OleDb.OleDbType.Char. This type doesn't support Unicode. So
please try to use System.Data.OleDb.OleDbType.WChar. If you are using the
Sql provider, please use System.Data.SqlClient.SqlDbType.NChar.

For more information, please check the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataoledboledbtypeclasstopic.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqldbtypeclasstopic.asp

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
M

Morten

Hi Kevin & Jon!

Thanks a lot for your responses. -- Sorry for not being back before, but
I've been busy with other [less interesting!] work....

Anyway: yes, it was the column type that was wrong in my code. After
changing it to wchar / nchar it works fine with whistles, horns and
everything.

Just a related question: When submitting INSERT or UPDATE statements to a
database in the "parameter way" (i.e. not writing the actual data values
directly in the statement), can I be sure that my values are correctly
wrapped in quotes (or whatever is needed), so I don't get security flaws
through SQL injections?

Cheers,

~Morten
 
J

Jon Skeet [C# MVP]

Morten said:
Thanks a lot for your responses. -- Sorry for not being back before, but
I've been busy with other [less interesting!] work....

Anyway: yes, it was the column type that was wrong in my code. After
changing it to wchar / nchar it works fine with whistles, horns and
everything.

Just a related question: When submitting INSERT or UPDATE statements to a
database in the "parameter way" (i.e. not writing the actual data values
directly in the statement), can I be sure that my values are correctly
wrapped in quotes (or whatever is needed), so I don't get security flaws
through SQL injections?
Click to expand...

Absolutely. That's part of the point of using them. In fact, there's no
guarantee that you'll end up ever having a full SQL statement -
depending on the driver/protocol, the parameters may end up being sent
up as they are, *as* parameters.
 
Top