Silent Coder
Silent Coder

Reputation: 101

ExecuteNonQuery works only one time to delete record in one run (No exception, No Error)

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";
}

Upvotes: 0

Views: 370

Answers (0)

Related Questions