CODEWITHSUNDEEP
c#mysqlutf-8
mjb
mjb

Reputation: 7969

MySQL - Return Incorrect UTF8 characters

my codes return incorrect UTF8 characters. I'm not sure how to fix it.

I create my table by using this code:

string sql = @"
CREATE TABLE `งปลา` (
`วงปลา` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ทศไท` varchar(45) DEFAULT NULL,
PRIMARY KEY (`วงปลา`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
";

using (MySqlConnection conn = new MySqlConnection(txtConstring.Text))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        conn.Close();
    }
}

and I insert some sample data like this:

string sql = "";
using (MySqlConnection conn = new MySqlConnection(txtConstring.Text))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        sql = "insert into `งปลา`(`ทศไท`)values('งปลาทศไท');"; ;
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();

        conn.Close();
    }
}

now, if I execute this:

dataGridView1.DataSource = null;
DataTable dt = new DataTable();

using (MySqlConnection conn = new MySqlConnection(txtConstring.Text))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        cmd.CommandText = "show create table `งปลา`;";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dt);

        conn.Close();
    }
}

dataGridView1.DataSource = dt;

It return this:

CREATE TABLE `????` (
  `?????` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `????` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`?????`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

And this:

dataGridView1.DataSource = null;
DataTable dt = new DataTable();

using (MySqlConnection conn = new MySqlConnection(txtConstring.Text))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        conn.Open();

        cmd.CommandText = "select * from `งปลา`;";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dt);

        conn.Close();
    }
}

dataGridView1.DataSource = dt;

will return this table:

--------------------
| ????? | ????     |
--------------------
| 1     | ???????? |
| 2     | ???????? |
| 3     | ???????? |
--------------------

All the Thai characters are not showing properly.

This are the MySQL server chatacter set:

show variables like 'character%';

Variable_name              Value 
-------------              -----------
character_set_client       latin1 
character_set_connection   latin1 
character_set_database     latin1 
character_set_server       latin1 
character_set_system       utf8

I know that the default character set should be UTF8, however, I don't have the privilege to change the default character set.

This is the sample project that I have created >> TestThaiMySQL_Test2.zip
it will generate the error.
A sample database is created.
The connection string is coded together inside the sample project.

So, the question is, how to return the Thai characters correctly?

Any idea? hints?

Upvotes: 0

Views: 1917

Answers (1)

OSborn
OSborn

Reputation: 895

Several things need to be done to get this to work:

Change the charset of the connection itself: in the connection string use charset=utf8

Make sure the database and table charsets are set correctly. See here for setting the database charset: http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

(It's unclear, but my understanding is that these charset options need to be set before creating the table in question.)

Upvotes: 1

Related Questions