CODEWITHSUNDEEP
c#mysqlutf-8
jimmyan
jimmyan

Reputation: 13

c# Having trouble with mysql : Incorrect string value code 1366 for column at row 1

ERROR C# MVC5 using ADO.NET when update columns using Chinese in mysql(version:5.7.27-0ubuntu0.18.04.1 - (Ubuntu)): {"Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'Title_1' at row 1"}

I have already tried charset for database, table and columns.

(1)'utf8' 'utf8_general_ci'
(2)'utf8' 'utf8_unicode_ci'
(3)'utf8mb4' 'utf8mb4_general_ci'
(4)'utf8mb4' 'utf8mb4_unicode_ci'

Also,I've added charset=utf8mb4 or charset=utf8 in connectionstring in Web.config

my connection string is

<add name="constr" connectionString="Data Source=ServerIP;port=3306;Initial Catalog=jn001;User Id=Userid;password=Password;charset=utf8mb4">

Google all day finding answers, Still can't find a solution, Please tell me a solution,Thanks!

here is my mysql charset

I've followed LocEngineer's advertisement and set mysql server to utf8mb4

here is my new mysql charset

And this is my collation

All change to utf8mb4 (including connectionstring) ,but still the same error occur.

Anyway ,thanks for the suggestions.

------------------------------9/5update--------------------------------

my SHOW CREATE TABLE

 public bool UpdateDetails(Class1 content)
        {
            connection();
            MySqlCommand cmdSet = new MySqlCommand("set names utf8mb4", con);
            MySqlCommand cmd = new MySqlCommand("update", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Title_1",content.Title_1);
            cmd.Parameters.AddWithValue("@Title_2", content.Title_2);
            cmd.Parameters.AddWithValue("@Title_3", content.Title_3);
            cmd.Parameters.AddWithValue("@Content_1", content.Content_1);
            cmd.Parameters.AddWithValue("@Content_2", content.Content_2);
            cmd.Parameters.AddWithValue("@Content_3", content.Content_3);

            con.Open();
            cmdSet.ExecuteNonQuery();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
                return true;
            else
                return false;
        }

this is mysql update Procedure

UPDATE Stop_Info SET Title_1 = Title_1,Title_2=Title_2,Title_3=Title_3,Content_1=Content_1,Content_2=Content_2,Content_3=Content_3 WHERE Pkey = 3

HTML form accept-charset="utf-8"

<form id="registerForm" method="post" action="~/Home/Index" class="form-horizontal" accept-charset="utf-8">

Upvotes: 1

Views: 1775

Answers (2)

shai
shai

Reputation: 111

This didnt work for me. After days of fiddling and trying practically everything, I had to add charset utf8mb4 to the parameter declaration in the stored proc. param text charset utf8mb4.

Upvotes: 1

Rick James
Rick James

Reputation: 142298

\xE4\xB8\xAD\xE6\x96\x87 is hex for 中文 ("Chinese language").

In MySQL, this will work with either utf8 or utf8mb4. However, since there are several Chinese characters that need 4 bytes, you are correct to use utf8mb4. Meanwhile, the COLLATION (eg, utf8mb4_unicode_ci) does not matter to the Question in hand.

The individual column's charset is important, not the database's. And the 191 kludge is needed only in 5.5 and 5.6. Please provide SHOW CREATE TABLE.

Another way to establish the connection parameters is to issue SET NAMES utf8mb4 immediately after connecting. (This is not preferred, but might be worth trying. It should be a workaround for the Gorm issue mentioned.)

Are you getting the error message when connecting? Or when issuing a particular query? If so, please provide that query.

Check that skip-character-set-client-handshake is not being used.

Check the "Best practice" in https://stackoverflow.com/a/38363567/1766831

Addenda Since you mentioned a stored procedure, I suggest you do SHOW CREATE PROCEDURE to see what charset it was built with. Here's an example of what can happen:

mysql> SET NAMES latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

mysql> DROP PROCEDURE x;
Query OK, 0 rows affected (0.01 sec)

versus:

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

If you don't see utf8mb4 on your PROCEDURE and FUNCTION declarations, rebuild them.

Upvotes: 1

Related Questions