using parameters inserting data into access databa

问题:

I have the following method to inserting data into a an access databasewhich works fine but I do get a problem if I try to insert text that contains single quotes I have learned.

[WebMethod]
public void bookRatedAdd(string title, int rating, string review, string ISBN, string userName)
{
    OleDbConnection conn;
    conn = new OleDbConnection(@\"Provider=Microsoft.Jet.OleDb.4.0;
                                               Data Source=\" + Server.MapPath(\"App_Data\\\\BookRateInitial.mdb\"));

    conn.Open();

    OleDbCommand cmd = conn.CreateCommand();

    cmd.CommandText = @\"INSERT INTO bookRated([title], [rating],  [review], [frnISBN], [frnUserName])VALUES(\'\" + title + \"\', \'\" + rating + \"\',\'\" + review + \"\',\'\" + ISBN + \"\', \'\" + userName + \"\')\";
    cmd.ExecuteNonQuery();
    conn.Close();
}

From what I understand one of the ways to solve the problem is by using parameters. I am not sure how to do this to be honest. How could I change the above code so that I insert the data by using parameters instead?

Kind regards Arian

回答1:

Same as for any other query:

a) Replace actual hardcoded parameters in your OleDbCommand with placeholders (prefixed with @),
b) Add instances of OleDbParameter to the DbCommand.Parameters property. Parameter names must match placeholder names.

[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 also implements IDisposable
      using (OleDbCommand cmd = conn.CreateCommand())
      {
           // create command with placeholders
           cmd.CommandText = 
              \"INSERT INTO bookRated \"+
              \"([title], [rating],  [review], [frnISBN], [frnUserName]) \"+
              \"VALUES(@title, @rating, @review, @isbn, @username)\";

           // add named parameters
           cmd.Parameters.AddRange(new OleDbParameter[]
           {
               new OleDbParameter(\"@title\", title),
               new OleDbParameter(\"@rating\", rating),
               ...
           });

           // execute
           cmd.ExecuteNonQuery();
      }
   }
}

回答2:

You have to use Parameter to insert Values. Its is allso a security Issue. If you do it like that a sql injection could by made.

Try like this:

string ConnString = Utils.GetConnString();
string SqlString = \"Insert Into Contacts (FirstName, LastName) Values (?,?)\";
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
  {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue(\"FirstName\", txtFirstName.Text);
    cmd.Parameters.AddWithValue(\"LastName\", txtLastName.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}

回答3:

For Microsoft Access the parameters are positional based and not named, you should use ? as the placeholder symbol although the code would work if you used name parameters provided they are in the same order.

See the documentation for OleDbCommand.Parameters Property

Remarks

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Be sure to include the expected schema type where the parameter will be used AND the schema length if applicable.

I also recommend you always use using statements around your instances where the type implements IDisposable like the OleDbConnection so that the connection is always closed even if an exception is thrown in the code.

Changed Code:

var connectionStringHere = @\"Provider=Microsoft.Jet.OleDb.4.0;Data Source=\" + Server.MapPath(\"App_Data\\\\BookRateInitial.mdb\";
using (var conn = new OleDbConnection(connectionStringHere))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = \"INSERT INTO bookRated ([title], [rating],  [review], [frnISBN], [frnUserName]) VALUES(?, ?, ?, ?, ?)\";
    cmd.Parameters.Add(new OleDbParameter(\"?\", OleDbType.VarChar, 100) { Value = title});
    cmd.Parameters.Add(new OleDbParameter(\"?\", OleDbType.Integer) { Value = rating });
    cmd.Parameters.Add(new OleDbParameter(\"?\", OleDbType.VarChar, 2000) { Value = review });
    cmd.Parameters.Add(new OleDbParameter(\"?\", OleDbType.VarChar, 60) { Value = ISBN });
    cmd.Parameters.Add(new OleDbParameter(\"?\", OleDbType.VarChar, 256) { Value = userName });

    conn.Open();
    var numberOfRowsInserted = cmd.ExecuteNonQuery();
}

回答4:

                OleDbCommand cmd = new OleDbCommand(\"insert into table_name (ID,Type,SrNo) Values (\'\" + textboxId.Text + \"\',\'\" + textboxType.Text + \"\' ,\'\" + textboxSr.Text + \"\');\", oc);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                MessageBox.Show(\"Data has been saved successfully\");
               cmd.Dispose();