Using parameters that insert data into an access database

Question

I have the following way of inserting data into an access database, which works fine, but I have a problem if I try to insert text containing single quotes that I recognized.

[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 way to solve the problem is to use parameters. I'm not sure how to do this, to be honest. How can I modify the above code to insert data using parameters instead?

Regards Arian

Answer 1

Same as for any other request:

a) Replace the actual hardcoded parameters in OleDbCommand with placeholders (with the @ prefix),
b) Add the OleDbParameter examples to the DbCommand.Parameters property. Parameter names must match placeholders.

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

Answer 2

You must use the parameter to insert values. This is also a security issue. If you do this like that, do an SQL injection.

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

Answer 3

For Microsoft Access, options are position-based and unnamed, should you use ? as a placeholder, although the code will work if you used the name parameters, provided that they are in the same order.

See the documentation for the OleDbCommand.Parameters Property

Notes

The OLE DB.NET provider does not support named parameters for passing parameters to an SQL statement or stored procedure called by OleDbCommand when the CommandType parameter is set to Text. In this case, you must use the question mark (?). For instance:

 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 indicate the expected type of circuit in which the parameter will be used, and the length of the circuit, if applicable.

I also recommend that you always use using expressions around your instances, where the type implements IDisposable as OleDbConnection , so the connection always closes, even if an exception is added to the code.

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

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