0

I'm building a project that required (in-project) local SQL Server but when I insert Chinese character into table, I got this "???"

Ex. when executing the following SQL, the word '你好' becomes '??'

INSERT INTO [dbo].[TraceData] 
VALUES (N'z', N'0', N'A', N'A', N'2021-06-28', N'Y', N'你好')

result of above SQL

Should change the collation of the database or is there any solution to solve the problem?

I've tried to change the collation to 'Chinese_Taiwan_Stroke_CI_AI' but it's not working, and unfortunately my local SQL Server doesn't seem to support utf-8 collation either.

I'm using Visual Studio 2019 community edition and SQL Server built-in Visual Studio (suppose to be SQL Server 2019)

(Example of local DB:https://www.youtube.com/watch?v=mgtfxtjKoaA&ab_channel=FoxLearn)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
陳冠廷
  • 1
  • 3
    You should be using `nvarchar` for your column types instead of `varchar` when you are storing most type of texts. – Igor Jun 28 '21 at 18:44
  • Sorry, this doesn't work, I still get '??' when insert Chinese character. – 陳冠廷 Jun 28 '21 at 18:50
  • Make sure the type is `nvarchar` for the column in the table `TraceData`. You are also using a statement that is dependent on the ordinal position of the columns in the table schema which is not recommended so the chance is that you might have made a mistake on which column you are altering. Make sure you include the column names in the INSERT statement. `INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...) VALUES (@value1, @value2, ....)` – Igor Jun 28 '21 at 18:52
  • I say this because if you are inserting the constant value `N'你好'` into a table where the column type is `nvarchar(length)` then it will never convert that value to question marks. This can only happen if the type in `varchar`. – Igor Jun 28 '21 at 18:53
  • I've changed the type to nvarchar(255), it's fine when direct insert with sql query, but the text still insert the data as '??' when insert the value programmatically – 陳冠廷 Jun 28 '21 at 19:21
  • 1. You need to make sure you change the type to `nvarchar` 2. It does not update existing values, those are lost. To see if you actually did change the data type: `select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = 'Participants' and COLUMN_NAME = 'your column name here'` – Igor Jun 28 '21 at 19:25
  • If for some reason you still doubt that `nvarchar` is the fix you need here is a demo: `declare @myTable TABLE (col1 nvarchar(255) not null);insert into @myTable (col1) values (N'你好');select col1 from @myTable;` – Igor Jun 28 '21 at 19:25
  • Thank you @Igor , problem solved, I've just noticed that I forgot to add N in my SQL command when updating data – 陳冠廷 Jun 28 '21 at 19:29
  • 1
    BTW if you are using ADO.NET (like `SqlCommand`) do be sure to use parameters. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). – Igor Jun 28 '21 at 19:32

0 Answers0