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?
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.
