delete rows from both DataGridView and database

Question

I'm searching all over every forum but I can't find the solution. It should be easy but it doesn't work.

I have created DataGridView called "doctorsDataGridView" that has Doctors Table as source. I don't want to allow the user to add items but allow removing and editing. For the deleting first I put this code:

private void doctorsDataGridView_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
{
    // Update the balance column whenever rows are deleted.
    try
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM [Doctors] WHERE ([DoctorCode] = @code)", Welcome.con);
        cmd.Parameters.Add("@code", SqlDbType.Int).Value = doctorsDataGridView.Rows[e.RowIndex].Cells["DoctorCode"].Value;
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
        { MessageBox.Show(ex.ToString()); }
}

private void doctorsBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
    this.Validate();
    this.doctorsBindingSource.EndEdit();
    this.tableAdapterManager.UpdateAll(this.clincDataSet);
}

private void AllDoctors_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the 'clincDataSet.Doctors' table. You can move, or remove it, as needed.
    this.doctorsTableAdapter.Fill(this.clincDataSet.Doctors);
}

But when I try to delete any row it's only deleted from the DatagridView and not the database.

Dumb question, but have you substituted the value returned from doctorsDataGridView.Rows[e.RowIndex].Cells["DoctorCode"].Value into your SQL query in Management Studio and verified the query works directly on the database? — Daniel Kelley
– Daniel Kelley, Commented Feb 2, 2013 at 19:28
And just to clarify I meant my question was dumb not yours (incase it came across that way). — Daniel Kelley
– Daniel Kelley, Commented Feb 2, 2013 at 19:30
You are executing SQL. have you checked it works directly on the database with the blue from your cell? — Daniel Kelley
– Daniel Kelley, Commented Feb 2, 2013 at 19:40
Just to clarify, even though it is pretty clear but this seems strange, it is definitely removed from the DataGridView and not the database? — Leon Newswanger
– Leon Newswanger, Commented Feb 2, 2013 at 19:57

Leon Newswanger · Accepted Answer · 2013-02-03 03:42:09Z

3

Try to place your code in DataGridView.UserDeletingRow Event

private void doctorsDataGridView_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
    // Update the balance column whenever rows are deleted.
    try
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM [Doctors] WHERE ([DoctorCode] = @code)", Welcome.con);
        cmd.Parameters.Add("@code", SqlDbType.Int).Value = doctorsDataGridView.Rows[e.RowIndex].Cells["DoctorCode"].Value;
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
        { MessageBox.Show(ex.ToString()); }
}

edited Feb 3, 2013 at 3:42

Leon Newswanger

6171 gold badge5 silver badges17 bronze badges

answered Feb 2, 2013 at 23:36

spajce

7,0905 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dragan Radivojevic · Accepted Answer · 2013-02-02 19:39:10Z

1

I can't see anything wrong here but I'd do following if I were you:

Open SQL Server profiler and see if anything gets to SQL server in the first place
Check your database references? Is Doctors table referenced by some other table? Maybe that is preventing you from deleting data
Step through code using debugger and check out SQL statement before executing cmd.ExecuteNonQuery() - try executing same statement manually in SSMS

answered Feb 2, 2013 at 19:39

Dragan Radivojevic

2,0022 gold badges14 silver badges14 bronze badges

Comments

Mohammadreza · Accepted Answer · 2013-02-02 23:06:25Z

Are you sure that your sql command Execute correctly ?
MSDN: "ExecuteNoneQuery() For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command" , so your ExecuteNonewQuery() must return 1;

Note: The datagridview rowsremoved event gets called every time the data gets loaded. It also makes sense to a certain extent that every time the data loads, the existing rows are removed. so technically the event should get called.

You could have a private boolean variable to know when you are loading and when you are not.

    private bool IsLoading { get; set; }

    private void MyForm_Load(object sender, EventArgs e)
{
    this.IsLoading = true;
// do stuff
    this.IsLoading = false;
}

private void DataGridView_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
{
            if (!this.IsLoading)
            {
                return;
            }

   //do stuff
}

Collectives™ on Stack Overflow

delete rows from both DataGridView and database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related