Inserting special chars (such as ' or +) into an Access database

Question

I have not much experience with SQL, Access nor C# but I find no solution to a problem that should look quite simple for someone who has more expertise.

Basically, the user fill some textboxes in a Winform and he might insert some "special" characters (at least, special for DB strings) such as '. These data are hence transferred into the database through an OleDb connection; let's say that string myString = this.textBox1.Text is the value that I would like to insert into the field MY_FIELD of the table myTable.

Starting code

My starting code was straight-forward:

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.DBstring);
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "INSERT INTO myTable (MY_FIELD) VALUES ('" + myString + "')";
comm.CommandType = CommandType.Text;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
conn.Close();

The above code will easily fail in the case where myString is something like guns'n'roses, because the comm.CommandText will be the following string value which is not valid SQL: INSERT INTO myTable(MY_FIELD) VALUES ('guns'n'roses').

Further research

I wasn't obviously the first newbie having this kind of problem. So I searched a bit through Stack Overflow and found the following thread where a guy had an issue inserting brackets into the command string. Hence, I've tried to adapt my code as for the accepted answer:

comm.CommandText = "INSERT INTO myTable (MY_FIELD) VALUES (?)";
comm.Parameters.Add(myString);

but this raises an InvalidCastException saying that The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.

Could anyone please lead me to what's the best practice to insert any kind of string into the Access database without failing the SQL command due to characters that have a "special" meaning to the SQL interpreter?

Answer 1

You are correct in using OleDbParameter for this. Every time you want to pass values to your database engine you should use parameters. The only problem with your second attempt is the fact that you don't use the correct syntax to create and add a parameter to the command collection

comm.CommandText = "INSERT INTO myTable (MY_FIELD) VALUES (?)";
comm.Parameters.Add("@name", OleDbType.VarWChar).Value = myString;

This of course if your MY_FIELD is a text field, if it is numeric then you need to use the appropriate OleDbType enum.

Said that I would suggest to change your code to this example

string cmdText = "INSERT INTO myTable (MY_FIELD) VALUES (?)";
using(OleDbConnection conn = new OleDbConnection(....))
using(OleDbCommand comm = new OleDbCommand(cmdText, conn))
{
     conn.Open();
     comm.Parameters.Add("@name", OleDbType.VarWChar).Value = myString;
     comm.ExecuteNonQuery();
}

The main difference is the Using Statement. With this syntax your disposable objects (connection and command) are correctly closed and disposed after you have finished to use them releasing any system resource used. And this happens also in case of Exceptions

Answer 2

The .Add method of an OleDbParameterCollection has several overloads, and the ones with a single argument expect that argument to be an OleDbParameter object.

If you want to pass the string value of a parameter then you'll need to use the overload that accepts the Name, Type, and Column Width of the parameter, i.e., this one, and then assign the .Value, like so:

comm.Parameters.Add("?", OleDbType.VarWChar, 255).Value = myString;

And, as @Steve said, you should always use parameters instead of "dynamic SQL" (which is what your first attempt was doing by "gluing" the value into the SQL statement itself).