CODEWITHSUNDEEP
c#ms-access
tonyy
tonyy

Reputation: 27

Is it possible to insert a value using an apostrophe using this command? Microsoft Access

New to C#. Trying to insert values into a Microsoft Access Database using this code:

string value = "It's a nice day"
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Table1 values('"+ value + "')";
cmd.ExecuteNonQuery();
con.Close();

But I get the error 'Syntax error (missing operator) in query expression' which I'm going to assume, stems from the apostrophe in the string value. Is there any way around this?

Upvotes: 0

Views: 436

Answers (1)

Steve
Steve

Reputation: 216333

Every time you need to pass values to execute an sql query you should ALWAYS use a parameterized query. As you have experienced, apostrophes mess with the syntax when you concatenate strings.

A parameterized query for your case should be

string value = "It's a nice day"
OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Table1 values(@value)";
cmd.Parameters.Add("@value", OleDbType.VarWChar).Value = value;
cmd.ExecuteNonQuery();

This will remove the problem with apostrophes, interpretation of the decimal point symbol, date format, but, most important even is not easy to exploit with Access, the Sql Injection ack.

Upvotes: 2

Related Questions