Communication Problem between ASP.net and MySQL
I have the following scenario -
User can enter text in any language in the text box and store it in my database along with the language name. Below is the code for this onbutton Update
Dim conStr As String = "Dsn=search;database=search;description=search;option=0;port=0;server=localhost;uid=root;CharacterSet=UTF8;" Dim s As String = txtLanguage.Text '<----"音読み現代仮名遣い人名用漢字" mySQL = "INSERT INTO multi_language(language, characters)" & _ " VALUES ('Japanese', '" & s & "')" con.ConnectionString = conStr con.Open() cmd = New OdbcCommand(mySQL, con) cmd.ExecuteNonQuery() con.Close()
screen for query execution
after clicking the button, the text in Textbox
becomes '??????'
and the data inserted into the database looks like this:
Language | characters
--------------------------
Japanese | ?????
My table structure
CREATE TABLE multi_language
(
id INTEGER NOT NULL AUTO_INCREMENT,
language VARCHAR(30),
characters TEXT,
PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET = utf8;
when i execute the request directly in the request browser it will execute correctly,
What's wrong with my encoding? what do I need to add to get the correct result?
This is a screenshot for comparing pasting from 
I am also suffering from a similar situation, I solved it differently as follows:
and insert Use your query like:
Dim s As String = txtLanguage.Text '<----"音読み現代仮名遣い人名用漢字"
mySQL = "INSERT INTO multi_language(language, characters)" & _
" VALUES ('Japanese', '" & encodeUTF(s) & "')"
Encode string before pasting
Public Function encodeUTF(ByVal inputString As String) As String '<-- function for encoding the input string
Dim byt() As Byte = uni.GetBytes(inputString)
encodeUTF = ""
For Each b As Byte In byt
encodeUTF &= b & ","
Next
Trim(Replace(encodeUTF, ",", ""))
End Function
decode string before extracting
Public Function decodeUTF(ByVal inputString As String) As String '<-- function for decoding the input string
Dim strs() As String
strs = inputString.Split(",").ToArray
Dim temp(strs.Length) As Byte
Dim i As Integer
For i = 0 To strs.Length - 2
temp(i) = Byte.Parse(strs(i))
Next
decodeUTF = uni.GetString(temp)
decodeUTF = decodeUTF.Substring(0, Len(decodeUTF) - 1)
End Function
When extracting that text into a textbox, you can use your query like:
mySQL = "Select language, characters from multi_language"
Reader = objdb.GetDataReader(mySQL)'<--- is a class function which returns the datareader
If Reader.HasRows = True Then
Reader.Read()
txtlang.Text = objOdbcDataReader.Item("language")'<--- display the selected language
txtchar.Text = objOdbcDataReader.Item("characters ")'<--- display the selected characters
End If
You can try this suggested solution,
If your application wants to store data in a database in multiple languages, make sure your database stores data in UTF-8, and that your database connection is in UTF-8 (people usually forget this).
Be sure to run this query when establishing a connection:
mysql_query("SET NAMES utf8");
At the end of your application where user input data is, set the accept-charset attribute on your forms.
<form accept-charset="utf-8">
Set the appropriate meta tags for your site:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
or Serve your sites with an appropriate HTTP header:
header('Content-Type: text/html; charset=utf-8');
In general, the problem is that everything is not in UTF-8
. If you keep everything in UTF-8
, there is usually nothing to worry about.
Refer Strategy for Unicode and Multiple Language Support in PHP5
Storing and displaying Unicode string (हिन्दी) using PHP and MySQL
source to share
perhaps you should use a parameterized query (which is always a better choice anyway than string concatenation which is prone to SQL injection)
change your query to use parameters (I'm not sure if mysql @param is the correct syntax):
"INSERT INTO multi_language(language, characters) VALUES ('Japanese', @val)"
then add a parameter to your request:
cmd = New OdbcCommand(mySQL, con)
cmd.Parameters.AddWithValue("@val", txtLanguage.Text)
cmd.ExecuteNonQuery()
con.Close()
source to share