Using parameters inserting data into access database

Question

I have the following method to inserting data into an 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?

Loser

As a side note, keep in mind that the @ prefix makes the string literal verbatim, meaning that your connection string spans multiple lines, and has a bunch of spaces in the middle. Also, all DbCommand implementations implement IDisposable, meaning you should dispose your command after use. – Kashmir

Just read the docs! msdn.microsoft.com/en-us/library/… – Haema

Answer 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();
      }
   }
}

Kashmir

@Milen: I don't have a pc where I can check right now, but are you sure that named parameters (with @ prefix) don't work with the OleDbCommand? – Kashmir

I know it is an old thread, but worth clarifying things a bit: "The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters." Therefor Insert statement should use ? instead of named parameters. – Chloramphenicol

That is as per MSDN article: msdn.microsoft.com/en-us/library/… – Chloramphenicol

Important: For Access databases, you must add the parameters in the same order they appear in the query. – Uvulitis

Answer 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();
  }
}

Arvonio

Answer 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();
}

Haunting