In MSAcess Database, Insert query to insert the character with apostrophe

Question

In MS Access Database

Insert query to insert the character------> N'tetarnyl

I have an insert query

OleDbCommand cmd = new OleDbCommand("insert into checking values('" + _
                       dsGetData.Tables[0].Rows[i][0].ToString() + "','" +  _
                       dsGetData.Tables[0].Rows[i][1].ToString()+ "')", con);

but it is showing me error...

syntax error (missing operator) in query expression

any idea??? How can I write insert query to insert the N'tetarnyl (including apostrophe)

Answer 1

In MS Access apostrophes are quoted by doubling them.

OleDbCommand cmd = new OleDbCommand("insert into checking values('" 
+ dsGetData.Tables[0].Rows[i][0].ToString().Replace("'", "''") + "','" 
+ dsGetData.Tables[0].Rows[i][1].ToString().Replace("'", "''")+ "')", con);

Answer 2

You have to escape your ' by writing it twice. The easiest way of doing that is YourString.Replace("'", "''")

OleDbCommand cmd = new OleDbCommand("insert into checking values('" + dsGetData.Tables[0].Rows[i][0].ToString().Replace("'", "''") + "','" + dsGetData.Tables[0].Rows[i][1].ToString().Replace("'", "''") + "')", con);

Answer 3

Hi,

You can do dsGetData.Tables[0].Rows[i][0].ToString().Replace("'","''"), and the same with the Rows[i][1].

That ought to replace all single quotes with 2x single quotes, which is the escape character for SQL.