1

The columns that I have in my DB are [CusName], [CusNo], [CusAdvance], [TotalAmount], [ID] and [CusItem]. I get the input of the name of the Customer to be deleted from textBox4.Text. I have modified the code as per what I got from you guys. But still the entries are not being deleted. They still remain inside the DB.

My Code:

void Button1Click(object sender, EventArgs e)
{
   try
   {
      OleDbConnection delConn = new 
          OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data 
          Source=C:/NavneethCollection.accdb;Persist Security Info=False;");
      delConn.Open();
      String delQuery = "DELETE FROM NavColl WHERE [CusName]= @CustName";
      OleDbCommand delcmd = new OleDbCommand();
      delcmd.CommandText = delQuery;
      delcmd.Connection = delConn;
      delcmd.Parameters.AddWithValue("@CustName", textBox4.Text);
      delcmd.ExecuteNonQuery();
      MessageBox.Show("Customer has been successfully removed!");
   }
   catch(Exception exc)
   {
      MessageBox.Show("Error: "+exc.Message);
   }
}
Linus Gingerbread
  • 11
  • 1
  • 3
  • 1
    Your delete should be Delete FROM NavColl WHERE [CusName] = 'some name'. You don't have to specify the columns. – William Xifaras Apr 13 '17 at 14:32
  • DELETE with columns works as well with Access. They are simply ignored. It is more probable that there is something wrong in the textbox4 and no record has been deleted. You could try to read the return value of ExecuteNonQuery. If it is zero then the WHERE statement has not found a record to delete – Steve Apr 13 '17 at 14:54
  • By the way, the connection string should not be splitted between Data and Source. Any extra spaces should result in an _Installable ISAM not found_ exception – Steve Apr 13 '17 at 14:56
  • Hardly a duplicate. Access works also with the column names listed. And the duplicate used has nothing to do with ADO.NET. Please reopen – Steve Apr 13 '17 at 14:58
  • The data is not being removed from the DB. I removed the columns and added the parameter @CustName. Still no success. – Linus Gingerbread Apr 14 '17 at 03:42
  • I read the return value of ExecuteNonQuery. It shows a zero. – Linus Gingerbread Apr 14 '17 at 03:43
  • @Steve When I removed the space between 'Data' and 'Source' in connection string, I got an ISAM exception. – Linus Gingerbread Apr 14 '17 at 08:59
  • I mean you need to have just one space. As it is written now you have a newline and a lot of spaces between the two parts (or do you just reformat that connection string to show all of it without scrolling?) – Steve Apr 14 '17 at 09:02
  • If the return value of ExecuteNonQuery is zero then you should really try to debug this code. The content of the textBox4 is not what do you expect it to be. The return value means that the command has not found any row to delete with that parameter – Steve Apr 14 '17 at 09:04
  • A possible explanation could be the presence of spaces in either the TextBox or in the database field – Steve Apr 14 '17 at 09:05

3 Answers3

4

In your delete command you should use only 

try
 {
  OleDbConnection delConn = new 
      OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data 
      Source=C:/NavneethCollection.accdb;Persist Security Info=False;");
  delConn.Open();
  String delQuery = "DELETE FROM NavColl WHERE [CusName]= @CustName";
  OleDbCommand delcmd = new OleDbCommand();
  delcmd.CommandText = delQuery;
  delcmd.Connection = delConn;
  delcmd.Parameters.AddWithValue("@CustName", textBox4.Text);
  delcmd.ExecuteNonQuery();
  MessageBox.Show("Customer has been successfully removed!");
 }
 catch(Exception exc)
 {
  MessageBox.Show("Error: "+exc.Message);
 }
Coskun Ozogul
  • 2,389
  • 1
  • 20
  • 32
0

Try the following code

you have to pasa ? as parameter name instead of [CusName]

  String delQuery = "DELETE FROM NavColl WHERE [CusName]= ?";
  OleDbCommand delcmd = new OleDbCommand();
  delcmd.CommandText = delQuery;
  delcmd.Connection = delConn;
  delcmd.Parameters.AddWithValue("?", textBox4.Text);
  delcmd.ExecuteNonQuery();
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 2
    OleDb doesn't use named parameters. It just has the ? placeholder, and has to match up the parameter value based on position/order of the placeholder compared to the order of items in the parameters collection. – Joel Coehoorn Apr 13 '17 at 14:48
  • Well, I did the modification. But there's no success. The data is still in the DB. The code doesn't show any error when executed. But at the same time, it doesn't do what it's supposed to. Could you help? – Linus Gingerbread Apr 14 '17 at 03:33
  • @JoelCoehoon you`re right it was the way to add a parameter to Sqlcommand not OledbCommand. I fixed it. Thx a lot – Hadi Apr 14 '17 at 06:15
  • @Hadi No success still. It can't delete. I tried reading the return value of EecuteNonQuery. It shows a 0. – Linus Gingerbread Apr 14 '17 at 08:56
0

You can't delete rows in Excel with OleDb. You can insert and update rows, but deleting is not allowed. If your application is not running on a server, you could always use Excel interop to delete the row, but not through OleDb.

Aprendendo
  • 1
  • 1