How to Safely Insert Data with Special Characters into an Access Database Using Parameters?

Using parameters to insert data into an Access database

Inserting data containing special characters into an Access database can be challenging due to single quote issues. Using parameters is an effective way to solve this problem.

Here's how to modify your code to use parameters instead of hard-coded values:

a) Replace actual parameters with placeholders:

In the cmd.CommandText attribute, replace the actual value with the placeholder prefixed with @:

<code class="language-csharp">cmd.CommandText = @"INSERT INTO bookRated([title], [rating],  [review], [frnISBN], [frnUserName]) VALUES (@title, @rating, @review, @ISBN, @userName)";</code>

b) Add OleDbParameter instance:

Create an instance of OleDbParameter and add it to the DbCommand.Parameters attribute. Make sure the parameter name matches the placeholder name in cmd.CommandText:

<code class="language-csharp">// 创建包含占位符的命令
cmd.CommandText =
   "INSERT INTO bookRated " +
   "([title], [rating], [review], [frnISBN], [frnUserName]) " +
   "VALUES (@title, @rating, @review, @isbn, @username)";

// 添加命名参数
cmd.Parameters.AddRange(new OleDbParameter[]
{
   new OleDbParameter("@title", title),
   new OleDbParameter("@rating", rating),
   // ... 添加其他参数
});</code>

Example:

The modified code is as follows:

<code class="language-csharp">[WebMethod]
public void bookRatedAdd(string title, int rating, string review, string ISBN, string userName)
{
   using (OleDbConnection conn = new OleDbConnection(
         "Provider=Microsoft.Jet.OleDb.4.0;" +
         "Data Source=" + Server.MapPath("App_Data\BookRateInitial.mdb"));
   {
      conn.Open();

      // DbCommand 也实现了 IDisposable
      using (OleDbCommand cmd = conn.CreateCommand())
      {
         // 创建包含占位符的命令
         cmd.CommandText =
            "INSERT INTO bookRated " +
            "([title], [rating], [review], [frnISBN], [frnUserName]) " +
            "VALUES (@title, @rating, @review, @isbn, @username)";

         // 添加命名参数
         cmd.Parameters.AddRange(new OleDbParameter[]
         {
            new OleDbParameter("@title", title),
            new OleDbParameter("@rating", rating),
            // ... 添加其他参数
         });

         // 执行
         cmd.ExecuteNonQuery();
      }
   }
}</code>

By using parameters, special characters inserted into text can be correctly handled, ensuring data integrity.

The above is the detailed content of How to Safely Insert Data with Special Characters into an Access Database Using Parameters?. For more information, please follow other related articles on the PHP Chinese website!