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

Not relevant to your question, but this looks like a _classic_ example of a [SQL Injection](http://bobby-tables.com/) vulnerability. — Dan Pichelman, Apr 19 '13 at 18:38
You are begging for a SQL injection attack. Use a parameterized query. — Brent Stewart, Apr 19 '13 at 18:38

score 3 · Answer 1 · edited May 23 '17 at 12:08

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

score 1 · Accepted Answer · answered Apr 19 '13 at 18:40

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.

score 0 · Answer 3 · edited Apr 07 '15 at 09:40

public static void DeleteLine(string kv)
{
    OleDbConnection myConnection = GetConnection();
    string myQuery = "DELETE FROM Cloth WHERE [ClothName] = '" + kv + "'";
    OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);

    try
    {
        myConnection.Open();
        myCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception in DBHandler", ex);
    }
    finally
    {
        myConnection.Close();
    }
}

score 0 · Answer 4 · edited Jul 02 '16 at 11:46

try

    {
        OleDbConnection con = new OleDbConnection("provider = microsoft.ace.oledb.12.0;data source = E:\\Sohkidatabase\\Sohki.accdb");
        con.Open();
        str = "select * from compny_info where id=" + comboBox1.Text.Trim() + "";
        com = new OleDbCommand(str, con);
        OleDbDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                textBox1.Text = reader["regis_no"].ToString();
                textBox2.Text = reader["comp_oner"].ToString();
                textBox3.Text = reader["comp_name"].ToString();
                textBox4.Text = reader["comp_add"].ToString();
                textBox5.Text = reader["tin_no"].ToString();
                textBox6.Text = reader["email"].ToString();
             }

            con.Close();
            reader.Close();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

score -1 · Answer 5 · edited Apr 07 '15 at 10:11

-1

 public static void DeleteLine(string kv) {
      OleDbConnection myConnection = GetConnection();
      string myQuery = "DELETE FROM Cloth WHERE [ClothName] = '" + kv + "'" ;
 }

edited Apr 07 '15 at 10:11

Amit Joki

58,320
7
77
95

answered Apr 07 '15 at 09:20

Kevin

1

Did you mean this to be an edit to your [other answer](http://stackoverflow.com/a/29487991/519348)? – TZHX Apr 07 '15 at 09:38

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

5 Answers5