0

It is a simple insert, update, delete operation Application in C# I am working on and I am using Access 2010 database (Accdb). Insert and Update are working and data is properly populating in DataGridView but delete operation is glitch.

I run the app, previously stored records populated in DataGridView at Form load, I click on delete and selected row deleted from the database and datagridview populates to show updated data.

Now I select another row and click again on delete button but it doesn't delete any row.

ExecuteNonQuery return 0 this time and I debugged it. Cust_ID is the same which is selected on row click. Don't know what is happening here. Need a look from another person's perspective.

Here is code.

//Some Global Variables
int r;
int R_ID;
string cid = "";
string scon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\DB_Medical.accdb;Persist Security Info=True";
OleDbConnection con;
OleDbCommand cmd = new OleDbCommand();

private void btnDelete_Click(object sender, EventArgs e)
{
    if (dgv.SelectedRows.Count == 0)
    {
        MessageBox.Show("No row selected");
    }
    else
    {
        DeleteRecord();
    }
}

public void DeleteRecord()
{
    con = new OleDbConnection(scon);
    con.Open();
    cmd.Connection = con;

    int id = int.Parse(dgv.Rows[dgv.SelectedRows[0].Index].Cells[0].Value.ToString());
    string delquery = "DELETE FROM tbl_Customer WHERE Cust_ID=@id";
    cmd.Parameters.AddWithValue("@id", id);
    cmd.CommandText = delquery;
    int row = cmd.ExecuteNonQuery();
    con.Close();
    MessageBox.Show(row + " rows deleted.");

    LoadDataInGridView();
}

private void LoadDataInGridView()
{
    string q1 = "SELECT * FROM tbl_Customer";
    con = new OleDbConnection(scon);
    con.Open();
    cmd.Connection = con;
    OleDbDataAdapter adapter = new OleDbDataAdapter(q1, con);
    DataSet s1 = new DataSet();
    adapter.Fill(s1);
    dgv.DataSource = s1.Tables[0];
    con.Close();
    ViewAdjust();
}

private void ViewAdjust()
{
    dgv.Columns[0].HeaderText = "ID";
    dgv.Columns[1].HeaderText = "Name";
    dgv.Columns[2].HeaderText = "Address";
    dgv.Columns[3].HeaderText = "City";
    dgv.Columns[4].HeaderText = "Mobile";
    dgv.Columns[5].HeaderText = "Phone";
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Silent Coder
  • 101
  • 11
  • 1
    Maybe try `cmd = new OleDbCommand();` before `cmd.Connection = con;`? Or at least clear the parameters. See [Reusing SqlCommand?](https://stackoverflow.com/q/670407/8967612) – 41686d6564 stands w. Palestine Oct 23 '19 at 03:34
  • 3
    This: `OleDbConnection con;` is a mistake. Worse with the `ACE.OLEDB` provider. Declare your connection when you need it, use it and dispose of it in-place. The same goes for the Command. I'ld consider to use a [BindingSource](https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.bindingsource) to bind your data to the Controls that are meant to use it. – Jimi Oct 23 '19 at 03:50
  • Thanks guys. I used OleDbCommand cmd = new OleDbCommand(); before con.open() and cmd.Connection = con; lines and it did the trick. Thanks again – Silent Coder Oct 23 '19 at 04:06

0 Answers0