Excel OLEDB reading Chinese characters problem

[mav8]

Hi All

I have an excel sheet with Chinese characters in them.

I'm using the OLEBE 4.0 Jet Driver in C# Microsoft Visual Studio 2005 to read from the Excel sheet and then enter into mySQL database.

The problem isn't with mysql but with C# reading the chinese characters from Excel.

Right now, All I get with chinese characters is question marks "?????????"

MY OLEDB driver should be able to support multi-language but it doesn't seem to be working.

Can anyone help?

 String sConnectionString = 
            "Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data Source=C:\language.xls;" + 
            "Extended Properties=Excel 8.0;"; 

            OleDbConnection objConn = new OleDbConnection(sConnectionString);

            objConn.Open();

            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
            OleDbDataReader reader = objCmdSelect.ExecuteReader();


            //---
            String myConnectionString = "server=192.168.168.6;uid=xxxx;pwd=xxxxx;database=xxxxxxx_db;";
            MySqlConnection myConnection = new MySqlConnection(myConnectionString);
             
 while (reader.Read())
             {

*this is where I generate my sql query
}

[Rune Bivrin]

I think you need to begin by making sure the question marks aren't just a an artifact of how you're displaying them. I've seen many instances where the debugger used a typeface which didn't include extended characters such as Chinese, and the only thing that is displayed are question marks.

Rune

[mav8]

Thank you for your reply Rune, you were the only one.

After working on this problem for almost the whole day yesterday, and with another person, I found my problem.

The only reason I want to post again here is so that people won't have to waste hours like I did.

Firstly, of course you have my to make sure that your tables AND fields are set to UTF-8 generial_ci. All the basics of course.

Whether you are using C#.Net or VB.Net, use the mysqlCommand >> Parameters Add Object to generate your queries.

I actually did ALL of the above and more. But the smallest thing I forgot was this :

Database=Test1;Data Source=pqc96;User Id=root;Password=password;CHARSET=utf8;

Make sure that CHARSET=utf8 IS INCLUDED IN THE CONNECTION STRING, whether in VB or C#.

Apparently, if you don't have the in there, no matter what your tables or your OS is set to, it will still input the stupid question marks ????????

ciao