user3696331 user3696331 - 5 days ago 7
SQL Question

Send Unicode string from MS Access to SQL Server using a DataSet

I am trying to get a string from a MS Access database and insert into a SQL Server database by using a dataset. But the utf8 string in my SQL statement inserted like

?????????
- what can I about this?

This is my code:

OleDbCommand cmd2 = new OleDbCommand("select * from t_about_us", con_access);
OleDbDataAdapter da2 = new OleDbDataAdapter(cmd2);

DataSet ds2 = new DataSet();
da2.Fill(ds2, "t_about_us");

con.Open();
string command2 = "insert into t_about_us(matn,see,metatag_description,metatag_keywords,metatag_author) values('" +
Encoding.UTF8.GetString(Encoding.UTF8.GetBytes(ds2.Tables[0].Rows[0]["matn"].ToString())) + "','" +
Convert.ToInt32(ds2.Tables[0].Rows[0]["see"].ToString()) + "','" +
ds2.Tables[0].Rows[0]["metatag_description"].ToString() + "','" +
ds2.Tables[0].Rows[0]["metatag_keywords"].ToString() + "','" +
ds2.Tables[0].Rows[0]["metatag_author"].ToString() + "')";

SqlCommand cmdd2 = new SqlCommand(command2, con);
cmdd2.ExecuteNonQuery();
con.Close();

Answer

By using dynamic SQL to construct an SQL statement with sting literals like 'this' you are implicitly converting the string from Unicode into the single-byte character set used by the SQL Server, and any Unicode characters that do not map to that target character set will be replaced by question marks.

So, for example, with my SQL Server ...

cmd.CommandText = "INSERT INTO myTable (textCol) VALUES ('γιορτή')";
cmd.ExecuteNonQuery();

... will be inserted as ...

????t?

... even though [textCol] is defined as an NVARCHAR column.

The correct approach is to use a parameterized query, like so

cmd.CommandText = "INSERT INTO myTable (textCol) VALUES (@word)";
cmd.Parameters.Add("@word", System.Data.SqlDbType.NVarChar).Value = "γιορτή";
cmd.ExecuteNonQuery();
Comments