You don't mention what database you are using, although the issue occurs in all databases and the solution is the same.
- You should always use Unicode text types like
nvarchar in the databases that support them (like SQL Server, Oracle), or use a UTF8 collation for those that don't (like MySQL).
- Data should be entered using parameterized queries to avoid encoding conversion errors
- If it's absolutely impossible to use parameterized queries (can't imagine why) you should use the database's syntax for passing Unicode values. In SQL Server this is done by adding a
N before the value, eg N'my unicode text'
You should also never use text, ntext, varchar(max) or nvarchar(max) values as all of these are BLOB types. They are used to store large data (up to 2 GB) so they are stored outside normal table storage resulting in large performance hits.
UPDATE
I just noticed in the comments that no special characters were involved after all, just string concatenation gone wrong. String concatenation should be avoided at all costs because:
- They are error prone
- They expose your code to SQL Injection attacks (image if instead of
<text> someone entered moo');truncate table Notes;--)
- Result in slower performance (the server can reuse the execution plans of parameterized queries)
- Are ugly to code and maintain
To avoid such errors in the future you should use parameterized queries, which allow you to pass any value without conversion, encoding or formatting errors. The actual code is actually much cleaner, eg:
var myInsertCommand=new SqlCommand("INSERT into NOTES(Note) VALUES(@note)");
myInsertCommand.Parameters.Add("@note", SqlDbType.NVarChar,txtNew.Text.Length);
myInsertCommand.Parameters["@note"].Value = txtNew.Text;
myInsertCommand.ExecuteNonQuery();
If your field is actually a blob you can use the SqlDbType.Text or SqlDbType.NText parameter types.
