communication Issue between ASP.net and MySQL

Question

I have the following scenario-

User may enter text in any language in the text box and need to store it in my database along with language name. Following is the code for this on button 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 short for running the query

after clicking button the text in the Textbox becomes '??????' and the data inserted in the data base is like the following

  Language     |     characters
  --------------------------
  Japanese     |      ?????

My table structure is

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 query directly in the query browser then it will executed properly,

whats wrong with my coding? what i need to add to get proper result?

This is the screenshot for the comparison of insert from the asp.net page and query browser

the language input is Unicode therefore you have to change data type for characters column to `NVARCHAR` — Jibran Khan, Nov 22 '14 at 11:09
its inserted in the database when i executed the query in the database as: `INSERT INTO multi_language(language, characters) VALUES ('Japanese', '音読み現代仮名遣い人名用漢字');` but i can't executed it from the asp.net — , Nov 22 '14 at 11:13
you cannot add Japaneses language in text format directly see this http://dev.mysql.com/doc/refman/5.0/en/charset-table.html — Rashid, Nov 22 '14 at 12:05
@ Rashid : its working fine for me when i execute the insert query through query browser, but not from the asp.net — , Nov 22 '14 at 12:20
Can you try with `\u....` values instead of unicode characters? Probably the encoding for the compiler is not set properly to support unicode characters. — Gábor Bakos, Nov 22 '14 at 12:32
Pretty sure it has to do with passing UTF8 encoded string from asp.net. See http://msdn.microsoft.com/en-us/library/system.text.utf8encoding(v=vs.110).aspx — paparazzo, Nov 22 '14 at 17:34

score 0 · Answer 1 · edited May 23 '17 at 12:06

You can try this proposed solution,

If your application want to save data in to database in multiple language, make sure your database stores data in UTF-8 and also the connection to your database is in UTF-8 (commonly people forget this).

Make sure to Execute this query when establishing a connection:

mysql_query("SET NAMES utf8");

In Your application end, where user input data, set the accept-charset attribute on your forms.

<form accept-charset="utf-8">

Set 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');

So overall the problem is, everything is not in UTF-8, If you keep everything in UTF-8, usually don't need to worry about anything.

Refer Strategy for supporting unicode & multi language in PHP5

Storing and displaying unicode string (हिन्दी) using PHP and MySQL

score 0 · Accepted Answer · answered Nov 29 '14 at 09:11

I am also suffering from a similar situation, i solved it in a different way as follows:

while inserting Use your Query as :

Dim s As String = txtLanguage.Text '<----"音読み現代仮名遣い人名用漢字"
mySQL = "INSERT INTO multi_language(language, characters)" & _
        " VALUES ('Japanese', '" & encodeUTF(s) & "')"

Encode the string before inserting

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 the string before retriving

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

While Retrieving this text to a text box you can use your query as :

 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

score -1 · Answer 3 · answered Nov 22 '14 at 16:51

perhaps you should use parametrized query (which anyway is always a better choice than string concatenation, which is susceptible to sql injection)

modify your query to use parameters (I am not sure if for mysql the @param is correct syntax):

"INSERT INTO multi_language(language, characters) VALUES ('Japanese',  @val)"

then add parameter to your query:

cmd = New OdbcCommand(mySQL, con)
cmd.Parameters.AddWithValue("@val", txtLanguage.Text)
cmd.ExecuteNonQuery()
con.Close()

communication Issue between ASP.net and MySQL

3 Answers3