How to insert and retrieve data from sqlite database with correct character set?

Question

I am writing a program in C# that's using a sqlite database (throug ADO, System.Data.SQLite).

The problem is that when I insert strings from my c# app containing Swedish or German characters (åäöüÅÄÖÜ etc) they are stored incorrectly in the db. (looking like Ã¶ and Ã¤ ect), but if I insert them directly to the db using SQLite Administrator they are saved correctly.

Furthermore, when I try to retrieve data with my C# app that is stored correctly, it's messed up in the same way...

What am I doing wrong?

Code to save data:

SQLiteConnection cnn = new SQLiteConnection("Data Source=C:\temp\database.s3db");
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = "INSERT INTO images (image_name, tags, relevance) VALUES (\"Example Image åäö.jpg\", \"test\", 3)";
mycommand.ExecuteNonQuery();
cnn.Close();
cnn.Dispose();

you have to do some sort of Encoding wouldn't or Culture Type of check / Encoding..? — MethodMan
– MethodMan, Commented Jan 25, 2012 at 14:46
Try this link an read the answer at the bottom where the guy talks about installing SQLLite stackoverflow.com/questions/498670/… — MethodMan
– MethodMan, Commented Jan 25, 2012 at 15:05
Thank you very much! That solved it! SQLite Administrator messed it up... — distractedhamster
– distractedhamster, Commented Jan 25, 2012 at 16:03

Marc · Accepted Answer · 2012-02-04 10:57:52Z

Try this:

private IDbDataParameter AddParameter(IDbCommand command, string paramName, DbType type, object value)
{
    IDbDataParameter parameter = command.CreateParameter();
    parameter.ParameterName = paramName;
    parameter.DbType = type;
    if (value != null)
        parameter.Value = value;
    else
        parameter.Value = DBNull.Value;
    command.Parameters.Add(parameter);
    return parameter;
}

mycommand.CommandText = "INSERT INTO images (image_name, tags, relevance) VALUES (@image_name, @tags, @relevance)"; 
AddParameter(mycommand, "@image_name", DbType.String, "Example Image åäö.jpg");
AddParameter(mycommand, "@tags", DbType.String, "Test");
AddParameter(mycommand, "@relevance", DbType.Int32, 3);
mycommand.ExecuteNonQuery();

Don't use the DbType.AnsiString or you will have the same problems as you're having now.

Collectives™ on Stack Overflow

How to insert and retrieve data from sqlite database with correct character set?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related