Hello
I am trying to insert some unicode data (hindi language) in a table.
Everything is ok, and data correctly get inserted in table if I am sending the data from a Query.
Problem raises if i am using the stored procedures.
Connection String defined in Web.Config
<add name="DBConnection" connectionString="server=localhost; userid=localuser; password=1234568987; database=DBUNICODEDATA; CharSet=utf8; pooling=false;"/>
CREATE TABLE `ztesthindi`
(
`rid` int(10) unsigned NOT NULL,
`engcontent` varchar(500) NOT NULL,
`hindicontent` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(in this table 'hindicontent' column will contain the unicode data)
'----------------------- THIS ASP.NET 3.5 CODE WORKING PERFECTLY ------------------
Dim Qstr As String
Qstr = "INSERT INTO ztesthindi(rid, engcontent, hindicontent) values (110, '" & TextBox1.Text & "', '" & TextBox2.Text & "')"
Constring = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
dbCon = New MySqlConnection(Constring)
dbCon.Open()
Dim MySqlCommand As New MySqlCommand("", dbCon)
MySqlCommand.CommandText = "SET character_set_results=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET NAMES utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET character_set_client=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET character_set_connection=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET collation_connection=utf8_general_ci"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = Qstr
MySqlCommand.ExecuteNonQuery()
'-----------------------
The above asp.net 3.5 code is executing perfectly and inserting the data as well in the above table.
Now i created a stored procedure to insert the data in above table
CREATE DEFINER=`localuser`@`%` PROCEDURE `zAdd_ContentRow`(
IN p_engcontent VARCHAR(500),
IN p_hindicontent TEXT
)
BEGIN
DECLARE p_rid Integer;
Select Max(rid)+1 into p_rid From ztesthindi;
IF p_rid IS NULL THEN
SET p_rid = 101;
END IF;
SET character_set_results=utf8;
SET NAMES utf8;
SET character_set_client=utf8;
SET character_set_connection=utf8;
SET collation_connection=utf8_general_ci;
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, p_hindicontent);
END
The following code is returning error while trying to insert the row in above table.
"Incorrect string value: '\xE0\xA4\x90 \xE0\xA4...' for column 'p_hindicontent' at row 1"
Try
dbCon = New MySqlConnection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
dbCmd = New MySqlCommand
dbCmd.Connection = dbCon
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "zAdd_ContentRow"
dbCmd.Parameters.AddWithValue("p_engcontent", TextBox1.Text)
dbCmd.Parameters.AddWithValue("p_hindicontent", TextBox2.Text)
dbCon.Open()
dbCmd.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
End Try
I tried to update the insert statement in stored proceudre
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, p_hindicontent);
with
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, convert(p_hindicontent using utf8));
but still getting the same error.
Please help me in this issue that how can i sortout the porblem.
Thank you.