string - How do I get around the "'" problem in sqlite and c#?

Question

I'm working in Microsoft Visual C# 2008 Express with Sqlite.

I understand that an apostrope (') in my text has problems in a query. My problem is that I thought I could replace it with '. It doesn't seem to be working... Here's a parred down example of my code:

string myString = "I can't believe it!";
cmd.CommandText = "Insert into myTable (myid,mytext) values (1,'" + myString.Replace("'","\'") + "');";

The error I get is: SQLite error: near "t": syntax error

I've tried a couple other replacements... like the other slash. And I wrote my string and a replaced version of my string out to the console to make sure it was coming out right.

What stupid error am I making here?

Thanks!

-Adeena

Answer 1

The solution presented by Robert will work (i.e. replacing ' by '').

Alternatively you can use parameters as in:

DbCommand   cmd = new DbCommand();
DbParameter param = cmd.CreateParameter();
// ...
// more code
// ...
cmd.CommandText = "Insert table (field) values (@param)";
param.ParameterName = "param"
param.DbType = DbType.String;
param.Value  = @"This is a sample value with a single quote like this: '";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();