How to abort delete action on confirmation message box

I wanted to reiterate the key aspects ofNilanka Dharmadasa's answer, with a more concise code snippet. The key is to divorce the linkage of the bindingnavigator to the 'x' button (it's linked up by default when you first drag it onto the form). Again, you must clear out the .DeleteItem property as spelled out below in step 2, coupled with calling the bindingsource's .RemoveCurrent() method.

1. First go to bindingnavigator's properties window.
 
2. Then set 'DeleteItem' property to '(none)'.
 
3. Then write the following code under bindingNavigatorDeleteItem_Click event.

if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK) { PerformDataDeleteLogic(); bindingSource.RemoveCurrent(); MessageBox.Show("Success"); } else { MessageBox.Show("Abort"); }

wow thank you for the code.

I will try to change it in order to work with sproc instead of having sql statements in my code.

I hope this works for me!

Thanks again :)

//Here is a class to handle sql read writes
//=================================

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

//copyright theLizard 2006 - 2009
//

///

/// Summary description for sql
///

public class sqlCon
{
    public SqlConnection con = null;
    public SqlDataReader reader = null;
    public SqlCommand cmd = null;
    public sqlCon()
    {

        cmd = new SqlCommand();

    }
    //-----------------------------------------------------------------
    public void connect()
    {
        System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        con = new SqlConnection((string)asr.GetValue("Connection", typeof(string)));
        con.Open();

    }
    //-----------------------------------------------------------------
    private static SqlDataAdapter SetupDataAdapter(String sqlString)
    {
        // Assuming all the default settings, create a SqlDataAdapter working
        // current database in SQL Server.
        System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();
        return new SqlDataAdapter(sqlString, (string)asr.GetValue("Connection", typeof(string)));
    }
    //-----------------------------------------------------------------
    public string get(string field)
    {
        return reader[field].ToString();
    }
    public void ExecuteReader()
    {
        reader = cmd.ExecuteReader();
    }
    //-----------------------------------------------------------------
    public SqlDataReader ExecReader()
    {
        return (cmd.ExecuteReader());
    }
    ///

    ///
    ///

    //-----------------------------------------------------------------
    public void write()
    {
        int i = cmd.ExecuteNonQuery();
    }
    //-----------------------------------------------------------------
    public bool read()
    {
        return reader.Read();
    }
    //-----------------------------------------------------------------
    public void command(String s)
    {
        cmd.Connection = con;
        cmd.CommandText = s;
    }
    //-----------------------------------------------------------------
    public void terminate()
    {
        try
        {
            if (reader != null)
            {
                reader.Close();
                reader.Dispose();
            }
        }
        catch (Exception err)
        {
        }

        try
        {
            if (cmd != null)
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
        catch (Exception err)
        {
        }
        try
        {
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }
        catch (Exception err)
        {
        }
        openConnections(false);
    }
    //-----------------------------------------------------------------
    public void beginTrans()
    {
        cmd.Transaction = con.BeginTransaction();
    }
    //-----------------------------------------------------------------
    public void commitTrans()
    {
        cmd.Transaction.Commit();
    }
    //-----------------------------------------------------------------
    public void rollbackTrans()
    {
        try
        {
            if (cmd != null)
                cmd.Transaction.Rollback();
        }
        catch (Exception err)
        {
        }
    }
    //-----------------------------------------------------------------
    public void openConnections(bool add)
    {
    }
    //-----------------------------------------------------------------
    public bool execSQL(string s)
    {
        bool success = true;
        sqlCon con = new sqlCon();
        try
        {
            con.connect();
            con.command(s);
            con.beginTrans();
            con.write();
            con.commitTrans();
        }
        catch (Exception err)
        {
            con.rollbackTrans();
            success = false;
        }
        finally
        {
            con.terminate();
        }
        return (success);

    }
    //-----------------------------------------------------------------
}

//app configuration file


 
   
 




//on your form

        private void button2_Click(object sender, EventArgs e)
        {
          sqlCon q = new sqlCon();  //class with SQL Records handling
          q.connect();
          q.command("SELECT * FROM Customer ORDER BY name");
         
          q.ExecuteReader();

          if (q.read())  //this can be a while if you are loading a grid or a combo box
            {
                orderID_txt.Text = q.get("id");
                custID_txt.Text = q.get("name");
            }
            q.terminate();
        }
Where you put this code depends on what you want to achieve


As for navigating records, there are a number of ways you can do this, 1st you can create an array of ids then use the array to retrieve  records like

load the array with ALL the ids from the DB using a

while(q.read())
  {
  ......
  }

in ButtonNext_click

recordId = array[currentIndex +1];
currentIndex ++;

in ButtonPrevious_Click

recordId = array[currentIndex -1];
currentIndex --;

q.command("SELECT * FROM Table WHER id = " + recordId)

            q.ExecuteReader();
                if(q.read())  //this can be a while if you are loading a grid or a combo box
                    {
                        orderID_txt.Text = q.get("OrderID");
                        custID_txt.Text = q.get("CustomerId");
                    }
                q.terminate();   

in DeleteRecord_Click

if(messagebox .... ) == ID_YES)
  {
  q.execSql("DELETE FROM Customer WHERE id = " + recordId);

  // at this point you would also delete the array element by looping until you find the record id to remove from the arry
  // or simply reload the array from the DB, whichever is easire for you.
  // you should also ensure that if there are records in other tables which rely on the record you are deleting that you also
  // delete these records by issuing a multi statement sql.
  // execSQL will take care of errors and roll back any transactions that fail.
  // eg. string sql = "DELETE * FROM siblingTable WHERE ownerId = " + IntToStr(recordId);
  // sql += ", DELETE FROM table WHERE id=" + IntToStr(recordId)  if any part fails the transaction will roll back...
  }


hope you understand this...

I used binding navigator beacause i didn't know how to show more than one records (with next/previous buttons etc). I'm not sure I understood what i should do.

If it's not too much trouble can you change the code in the example I sent you for me?

Thanks again for all the help.

Looking at your code, why on earth are you using a binding navigator for? you have all the code to get and set
records all you need is the method to assign field values to text and combo boxes its not that much more code and you will be more able to get jobs done without needing to resort to components you have little control over.

public SqlDataReader Reader = null;

 public string get(string field)
    {
    return Reader[field].ToString();
    }

TextBox1.Text = ClassNameHoldingFuncttion.get("CustomerId");

there is a bit more to this but you can do everything you need to without a binding navigator, research SqlDataReader.

You should control whether changes are saved or if you really want to delete a record or not.

Here's a test project i uploaded:
http://rapidshare.com/files/298481532/NorthWindTesting.rar

It uses Northwind database, I added the stored procedures as well.

Thank you!

ok, seeing that you are using SqlConnections an Commands, you could do this a little easier by not using bound controls.

can you post your form so I can get a picture of what you are trying to do,

If you are using SQL Server you may be interested in the controls I have created to handle data entry forms without using data aware controls.

without a bindingNavigator you have a add, edit, delete, save and cancel button, a Next and Previous buttons.

then you create your sql statements for the various events,

then you could use the code that you have and control all aspects of the work flow.

A standard grid to show records and standard buttons to do what the bindingNavigaor does without the head aches.


eg:
 if(MessageBox( whatever the message.......)== ID_OK)
     if(con.ExecuteNoQuery("DELETE FROM Orders WHERE id=23")  == 0)
          MessagebOX(SUCCESS....);

ELSE
   //nothing to do really because there are no pending actions as you would have using the bindingNavigator.

do you understand what I am saying?

Hi Dealy,
You can try this method.
 
1. First go to bindingnavigator's properties window.
 
2. Then set 'DeleteItem' property to '(none)'.
 
3. Then write the following code under bindingNavigatorDeleteItem_Click event.

if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK) { string connectionString = GetConnection(); SqlConnection conn = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("DELETE_ORDER", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@ORDER_ID", orderID); conn.Open(); int rows = command.ExecuteNonQuery(); conn.Close(); if (rows > 0) MessageBox.Show("Success"); else MessageBox.Show("Failure"); bindingSource.RemoveCurrent(); } else MessageBox.Show("Abort"); }

 Please accept this anser if it helps...

The code for delete:

if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK) { string connectionString = GetConnection(); SqlConnection conn = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("DELETE_ORDER", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@ORDER_ID", orderID); conn.Open(); int rows = command.ExecuteNonQuery(); conn.Close(); if (rows > 0) MessageBox.Show("Success"); else MessageBox.Show("Failure"); } else MessageBox.Show("Abort"); }

ok, what code do you use to delete the record through sproc?

try this in the else part of your if statement

BindingSourceName.CancelEdit()


You may also have to do a BindingSourceName.ResetBindindings or BindingSourceName.ResetItem

There is no e.Cancel on EventArgs

I still cannot see any code that cancels the delete operation just by looking at the bindingNavigatorDeleteItem_Click event tells me that the delete operation has already been commenced and if cancel instruction has not been issued the code you have in the click event has NO purpose.

See if EventArgs e has an cancel like e.Cancel = true;

 private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
   {
     if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK)
     MessageBox.Show("DELETE")
       //Code for accessing the db and deleting the record through sproc. 

what code do you use here to delete from the db?

     else

e.Cancel = true;

       MessageBox.Show("ABORT");
   }

Well, this is my code:

private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)   {      if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK)      MessageBox.Show("DELETE") //Code for accessing the db and deleting the record through sproc. else       MessageBox.Show("ABORT");    }

If I abort the action the record dissappears from the bindingNavigator (like is being deleted) and i have to refresh the bindingSource in order to get it again.

I don't know much about bindingNavigator but if it gets into here "private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)" you have already started the process so by getting another button to confirm delete or cancel is pointless and unnecessary.

Is the record deleted from the database? if not, how did you cancel the delete operation, I cannot see any code in any example in this post that shows the operation is canceled when the bindingNavigatorDeleteItem_Click event is fired.

I tried the solution you gave me but it doesn't seem to work. It still needs refresh in order to see the record I aborted the deletion.

First you create the button called 'btnCommit'. Then bind 'btnCommit_Click' method for button's click event.Then use this code.
This will help you overcome the said issue. If you still have any problem let me know.
 
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{ 
      
btnCommit.PerformClick();

}
private void btnCommit_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//Write your code to delete the item here.
MessageBox.Show("DELETE");
}
else
{
//Code for Cancel action
MessageBox.Show("ABORT");
return; //This line will help you to abort the action.
}
}

Thanks for the answer.

I still have the same problem though. I need to refresh the form in order to get the record that i aborted the deletion.

Kirtan thanks again for the answer.

If you check my code, you will see this i what i do. The problem is that it still removes the CurrentItem :S