MySql's Exception : Variable 'character_set_client' can't be set to the value of 'utf16'

Question

Previously I use sql server 2005 as my website database, and everything works well. now I have changed to MySql server 5.5 database because it is open source.

I used Navicat Premium to transfer my data from sql server to mysql. I use mysql workbench and navicat to manage my database. Problems come when i declare the connection to mysql database. here is my code:

    MySqlCommand cmdselect;
    MySqlConnection conNDB;
    MySqlDataReader Mydtr;
    string server = "localhost";
    string database = "maindb";
    string uid = "root";
    string password = "abc123";
    string strCon = "SERVER=" + server + ";" + "DATABASE=" +
    database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
    string strSelect = "SELECT * FROM announcement"; 

    conNDB = new MySqlConnection(strCon);
    conNDB.Open();
    cmdselect = new MySqlCommand(strSelect, conNDB);
    Mydtr = cmdselect.ExecuteReader();

    rptAnnounce.DataSource = Mydtr;
    rptAnnounce.DataBind();

    Mydtr.Close();
    conNDB.Close();

Reference to MySql.Data already set. Here i got this error message :

Exception Details: MySql.Data.MySqlClient.MySqlException: Variable 'character_set_client' can't be set to the value of 'utf16' Error message stated this error occurs during connection.Open();

When i keep on refreshing the error page, i got another error sometime. here is it:

Exception Details: MySql.Data.MySqlClient.MySqlException: Expected end of data packet Error message stated this error occurs during Mydtr = cmdselect.ExecuteReader();

I am new to mysql. i don know what problem is this. i guess this problem comes from database's setting or data, not my source code.

anyone knows the solution? your help is greatly appreciated. i been trying for 4 days but cannot solve.

Answer 1

solved!! Thanks to @Etienne Rached and @jeremi

all the problems come from the character set problem.

solution: download navicat, to change the character set for database and every single table.

there are 2 places you need to check:

1) right-click on database eg. myDb. Then select properties and set the character set

2) right-click on a table and select design table. click every single row to change character set and finally go to "Option" tab and change the character set.

For your info: this is very rare case. I google it almost cannot find the solution. i created this mistake during installation of Mysql, I chose utf16 format ><

by the way, simple connectionstring will work. like

"server=localhost;database=maindb;uid=root;pwd=abc123;CharSet=utf8; port=3306";