CODEWITHSUNDEEP
c#.netmysqlmysql-workbench
Steven Yates
Steven Yates

Reputation: 2480

MySql C# Insert Error. Incorrect string value: '\xE1ra'

I'm using the C# .NET Mysql Connector, and when running this query:

INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES ('3', '1347396787', 'Chára', '........', '0', '0', '0.0.0.0:0000', 'C', 'óóóíííí', 'óóóíííí');

I get the following error:

Incorrect string value: '\xE1ra' for column 'from' at row 1

I understand my encoding, everything was configured for utf8, utf8_general_ci. Database, table and columns are configured for utf8. The data is sent from the client in utf8.

If i use a 3rd party tool like, Workbench to insert the query or use the mysql command line it works fine. I don't know if there is a bug with the connector or i need to be doing something else with the values before insert?

Any idea?

Thanks

Is there any in mysql to covert to the correct type?

Upvotes: 0

Views: 2805

Answers (6)

Daniel Nguyễn
Daniel Nguyễn

Reputation: 1

config mysql like below. it will solve the unicode error when insert into mysql from java or C#

enter image description here

Upvotes: 0

Ugnius Ramanauskas
Ugnius Ramanauskas

Reputation: 81

For someone who has tried all the suggestions, and nothing has worked (like myself), it is worth checking what MySQL types are your fields mapped to in C#. My text fields were automatically mapped as MySqlDbType.Blob and that was causing the error. I changed the type to MySqlDbType.Text, and I don't see the error any more.

Here is my original response to a similar thread: https://stackoverflow.com/a/16989466/2199026

Upvotes: 0

Steven Yates
Steven Yates

Reputation: 2480

I figured this out, its taken a while but it seems i was setting the charset too often. The database, tables, columns are all in UTF8. When i made a connection i had "CHARSET=UTF8" in the connection string. I was also running "SET NAMES 'utf8' COLLATE 'utf8_general_ci'" everytime i made a connection. I dropped the CHARSET=UTF8 and "SET NAMES 'utf8' COLLATE 'utf8_general_ci'" and its all working now.

Upvotes: 1

PawanS
PawanS

Reputation: 7193

Unicode string prefix with N

First see your table convos and make sure columns data types is either one of nchar, nvarchar and You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server

Tyr:

    insertQuery = "INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES
 (N'3', N'1347396787', N'Chára', N'........', N'0', N'0', N'0.0.0.0:0000', N'C', N'óóóíííí', N'óóóíííí')";

Upvotes: 0

GautamD31
GautamD31

Reputation: 28763

Update it

INSERT INTO convos (`userid`,`time`,`from`,`content`,`read`,`deleted`, `ip`, `source`, `charname`, `to`) VALUES ('3', '1347396787', 'Chara', '........', '0', '0', '0.0.0.0:0000', 'C', 'óóóíííí', 'óóóíííí');

I think for "chara"in your 3rd value gives it

Upvotes: 0

VIRA
VIRA

Reputation: 1504

I believe you need to alter the column's char set:

use below code for those columns which is using UTF-8.

ALTER TABLE database.table MODIFY COLUMN col VARCHAR(255)  
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Upvotes: 1

Related Questions