ASP.NET 2.0 with MySQL 4.1.2 - unicode problem
Hey guys
This is my first post and the reason i've actually started this blog.
you see, it's now 3:00 AM here but i could not get sleep until I share this with the world :)
during the last few days i struggled connecting MySQL database, version 4.1.2, through my ASP.NET application (using SubSonic DAL btw - great project, thanks guys!), and sending unicode utf-8 format data.
The problem was that when I tried saving data (Insert or Update SQL Commands) i got question marks ('?????') saved instead of unicode characters, like hebrew for example.
I've looked up at all the forums and I have tried everything. I mean really - EVERYTHING.
For example, I've read that I should send, right after I'm establishing the connection,
SET NAMES utf8 COLLATE 'utf8_unicode_ci'; SET CHARACTER SET utf8;
So I've tried that... but no good, still got the question marks.
I've downloaded the connector code and recompiled it. tried to see what does it sends. The ASP Connector version was 5.1.3 btw, and i've also used the MySQL.WEB functionality in order to use the ASP:Login, membership and roles functionality, again - thanks guys :) works like magic.
I must mention that when I tried these on my local MySQL 5 installed on my computer - there were no unicode problems. Only when I've tried it on the MySQL 4 that is installed on the web server I'm using, then I had these issues.
Eventually I've tried it all. I've set every variable on the server to UTF-8. I've changed all the database, tables and fields collations to the same collation - utf8_unicode_ci.
But the problem was not corrected.
I could not sleep. I could not eat. I had to fix that damn issue.
I've started looking on the ASP Connector for MySQL code. I've learn that it uses encoding for the transmision... but setting it to UTF-8 instead of Latin1 did not help either.
Eventually I've found in some forum a suggestion. Adding CharSet=utf8 to the connection string.
And that was it :)
That made the unicode finally work.
So if you're reading my post right now and having the same problem... try all these, including sending the set names command of course.
Good luck! and happy programming ;)
Liad
This is my first post and the reason i've actually started this blog.
you see, it's now 3:00 AM here but i could not get sleep until I share this with the world :)
during the last few days i struggled connecting MySQL database, version 4.1.2, through my ASP.NET application (using SubSonic DAL btw - great project, thanks guys!), and sending unicode utf-8 format data.
The problem was that when I tried saving data (Insert or Update SQL Commands) i got question marks ('?????') saved instead of unicode characters, like hebrew for example.
I've looked up at all the forums and I have tried everything. I mean really - EVERYTHING.
For example, I've read that I should send, right after I'm establishing the connection,
SET NAMES utf8 COLLATE 'utf8_unicode_ci'; SET CHARACTER SET utf8;
So I've tried that... but no good, still got the question marks.
I've downloaded the connector code and recompiled it. tried to see what does it sends. The ASP Connector version was 5.1.3 btw, and i've also used the MySQL.WEB functionality in order to use the ASP:Login, membership and roles functionality, again - thanks guys :) works like magic.
I must mention that when I tried these on my local MySQL 5 installed on my computer - there were no unicode problems. Only when I've tried it on the MySQL 4 that is installed on the web server I'm using, then I had these issues.
Eventually I've tried it all. I've set every variable on the server to UTF-8. I've changed all the database, tables and fields collations to the same collation - utf8_unicode_ci.
But the problem was not corrected.
I could not sleep. I could not eat. I had to fix that damn issue.
I've started looking on the ASP Connector for MySQL code. I've learn that it uses encoding for the transmision... but setting it to UTF-8 instead of Latin1 did not help either.
Eventually I've found in some forum a suggestion. Adding CharSet=utf8 to the connection string.
And that was it :)
That made the unicode finally work.
So if you're reading my post right now and having the same problem... try all these, including sending the set names command of course.
Good luck! and happy programming ;)
Liad
Comments
i've been searching over the internet but nothing until i got to this blog.
thank you very much :)
Best Solution...
Incorrect string value: '\xE0\xB8\xA7\xE0\xB8\xB1...' for column 'title1' at row 1
i have added 'charset=utf8;' in my connection string and as well as my table.
any advise...??