How to abort delete action on confirmation message box

Question

Hello I have the code for deleting a record but i noticed even though user cancels the action it removes the current record from the form. Any ideas how can i stop the action when user cancel the action on the confirmation message box? private void bindingNavigatorDeleteItem_Click(object sender EventArgs e) { if (MessageBox.Show(Delete? Confirm Delete MessageBoxButtons.OKCancel) == DialogResult.OK) MessageBox.Show(DELETE); else { //Code for Cancel action MessageBox.Show(ABORT); } } Thank you in advance.

Nilanka Dharmadasa · Answer

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...

Kirtan Patel · Answer

Friend Its Happening Because its a BindingNaviagtor and its performing some operation automatic after binding it to source so this is also its automatic operation .and we are performing manual methods on it :) so we cant to on what it does automatically so we have to find some way as i suggested you in your other post :) if you use your own button control then it will not be the issue :) thank you :)

bkwdesign · Answer

I wanted to reiterate the key aspects of Nilanka 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); }

Dealy · Answer

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 :)

theLizard · Answer

//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...

Dealy · Answer

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.

theLizard · Answer

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.

Dealy · Answer

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!

theLizard · Answer

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?

Dealy · Answer

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

theLizard · Answer

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

Dealy · Answer

There is no e.Cancel on EventArgs

theLizard · Answer

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

Dealy · Answer

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.

theLizard · Answer

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.

Dealy · Answer

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.

Nilanka Dharmadasa · Answer

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. } }

Dealy · Answer

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.

Nilanka Dharmadasa · Answer

private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {

            if (MessageBox.Show("Delete?", "Confirm Delete", MessageBoxButtons.OKCancel) == DialogResult.OK)
            MessageBox.Show("DELETE");
            else
            {
//Code for Cancel action
             MessageBox.Show("ABORT");
            }
       }

Dealy · Answer

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

Kirtan Patel · Answer

Provide me the code you are using ...to remove record so that figure it out better whats the problem because code i provided only fires events on correct button press :-)

Kirtan Patel · Answer

Friend Here it is how to do that :-) private void button1_Click( object sender EventArgs e) { DialogResult x= MessageBox .Show( Are you Sure Delete?  Delete Confirmation  MessageBoxButtons .OKCancel); if (x == DialogResult .OK) { MessageBox .Show( Deleted ); } else if (x == DialogResult .Cancel) { MessageBox .Show( Canceled  ); } }