Escaping Special Characters for MS Access Query

I want to store these characters " '^+%&/()=?_ " via 开发者_如何学Goan insert query for an ms access database. How do I do this and prevent from every cases?

Use parameterized INSERT statement.

It looks like your code is assembling the SQL command string. I have to tell you: if that's the case, it makes your code vulnerable to SQL Injection.

In addition to using a parameterized query (see Adrian's Answer) you can use a Query Definition with a parameter and call it.

For example you could create a query named qry_InsSomeTable with the following sql

PARAMETERS P_SomeField Text ( 255 );
INSERT INTO tbl_SomeTable (  P_SomeField )
VALUES (P_SomeField );

Then you could call like you would any stored procedure

using(var cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Whatever.mdb")
{
    var cmd = new OleDbCommand();
    cmd.Connection = cn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = cmd.CommandText = "qry_InsSomeTable";
    cmd.Parameters.AddWithValue("P_SomeField", "'^+%&/()=?_ ");

    cn.Open();

    cmd.ExecuteNonQuery(); 


}

Its particularly helpful when you have a table with a bunch of fields

For example

  cmd.CommandText = @"Insert  Into TableWithAlotofFields 
                    (field1, field2, field3, field4, field5,...)
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?..)";


  cmd.Parameters.Add("value1");
  cmd.Parameters.Add("value2");
  cmd.Parameters.Add("value3");
  cmd.Parameters.Add("value4");
  cmd.Parameters.Add("value5");

vs.

Query already define in Access as

 PARAMETERS P_field1Text Text ( 255 ), P_field2 Number, P_field3 text(15), ...;
  Insert Into TableWithAlotofFields 
  (field1, field2, field3, field4, field5,...)
  VALUES(P_field1, P_field2, P_field3, P_field4, P_field5,...)

then in c#

 cmd.CommandText = cmd.CommandText = "qry_InsSomeTable";
 cmd.Parameters.AddWithValue("P_field1", "value1");
 cmd.Parameters.AddWithValue("P_field2", "value2");
 cmd.Parameters.AddWithValue("P_field3", "value3");
 cmd.Parameters.AddWithValue("P_field4", "value4");
 cmd.Parameters.AddWithValue("P_field5", "value5");

However as @David-W-Fenton points out the names on the parameters are ignored and only the position is taken into account. e.g.

This

  cmd.Parameters.AddWithValue("P_field1", "value1");
  cmd.Parameters.AddWithValue("P_field2", "value2");

is not equivalent to

 cmd.Parameters.AddWithValue("P_field2", "value2");
 cmd.Parameters.AddWithValue("P_field1", "value1");

So it seems it mostly stylistic in the differences.

To insert special Characters you should enclose the special characters in brackets ([]). I hope this helps.

on Visual Studio you must to convert the single to double character

i.e.:

sql = replace(sql, "'", "''");