How do I delete a row from a Microsoft Access table using c#

Question

I've tried this code:

string sql = " DELETE FROM HotelCustomers WHERE [Room Number] =" +  textBox1.Text;
OleDbConnection My_Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\\Users\\Documents\\HotelCustomersOld.mdb");

My_Connection.Open();

OleDbCommand My_Command = new OleDbCommand(sql, My_Connection);
My_Command.ExecuteNonQuery();

Error: Data type mismatch in criteria expression, at the line: My_Command.ExecuteNonQuery();

Solution 1

Which type is your [Room Number] column? If it is a string then you have to write the value with inverted comma or quotation mark (I'm not sure which of both is used in Access).

string sql = " DELETE FROM HotelCustomers WHERE [Room Number] = '" +  textBox1.Text + "'";

To avoid SQL injektion you should use Parameters instead of the string operation.

Solution 2

Use parametrized query to avoid all kind of errors

   string sql = " DELETE FROM HotelCustomers WHERE [Room Number] =?";
   using(OleDbConnection My_Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\\Users\\Documents\\HotelCustomersOld.mdb"))
   {
        My_Connection.Open();
        OleDbCommand My_Command = new OleDbCommand(sql, My_Connection);
        My_Command.Parameters.Add("@p1",  textBox1.Text);
        My_Command.ExecuteNonQuery();
   }

In your case the Room NUmber field is of Text type so, you need to enclose the value in single quotes, but this is really wrong. You expose your code to maliciuos text written by your user inside the text box. A very simple and funny example here

Comments

[TZHX]

Did you mean this to be an edit to your other answer?