locked
Problem with DBConcurrency Exception in c# RRS feed

  • Question

  • Hi,

    i have a windows c# application. I have a windows form which have a search button,Edit button,delete button and a datagridview to view the data. I am having a problem when i edit the data in the form. Initially the code was working. However when i delete a record and edit it afterwards there is an error: 

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

    Is their a possibility as how to solve this exception and error so that i can continue to edit and delete data?

    Thanks in advance

    Mirfath


    *mirfath*

    Saturday, December 29, 2012 8:51 AM

Answers

  • I quite not sure about that but when I close my visual studio and open it up again it works!!

    *mirfath*

    • Marked as answer by Mirfath_ Monday, December 31, 2012 2:36 AM
    Monday, December 31, 2012 2:36 AM

All replies

  • Hello,

    Are you saying the deleted record is still seen in the DataGridView after you have deleted it or after the delete you attempt to edit another record and an exception is raised?

    Suggested reading

    http://blogs.msdn.com/b/spike/archive/2010/04/07/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records.aspx


    KSG

    Saturday, December 29, 2012 12:40 PM
  • No the deleted record is not visible in the datagridview view but is visible in the database. When I delete the row and afterwards edit a another record I am having the error! I hope you can give me a suitable solution for this! Awaiting for your reply and thanking you in advance Mirfath

    *mirfath*

    Saturday, December 29, 2012 1:40 PM
  • Hello Mirfath,

    Still not enough information. Let's say your DataGridView is populated via OleDb into a DataTable, the DataTable becomes the DataSource of a BindingSource and the BindingSource is the DataSource of the DataGridView. You would have an event i.e. button click event to remove the current item where you would access the primary key for the row via BindingSource.Current. The primary key would be used in the Where clause of your SQL Delete statement. Upon executing the OleDb command check the result, if 1 then that indicate the row was removed from the database table then you would use BindingSource.RemoveCurrent to remove the row from the underlying DataTable thus removing the row from the DataGridView. At this point opening the database table the remove should be gone. Since the row is still visible indicates to me you did not run a SQL delete statement successfully.

    Here is a slightly different twist on deleting a row or if using a TableAdapter to delete the row.

    With that said I need to know how your method differs from the above.


    KSG

    Saturday, December 29, 2012 2:28 PM
  • Hi,

    Thanks for the reply.I am sorry i did not provide the required information.

    The form looks like this

    The Search and Reset buttons are working fine.

    The problem is with the delete button and the Edit button.

    Once you select the record from the datagridview and click the delete button the record gets deleted from the datagridview. Even if you close and open the form it is not visible.However when i check the sql database the record is still there.

    the code for the delete button is:

     SqlConnection con = new SqlConnection("Data Source=HP-PC;Initial Catalog=Stock Control;Integrated Security=True");
    
            private void Bind()
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter("select * from Order_Table", con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;
                con.Close();
                
            }
    
    
    private void button5_Click(object sender, EventArgs e)
            {
                SqlCommand delcmd = new SqlCommand();
                if (dataGridView1.Rows.Count > 1 && dataGridView1.SelectedRows[0].Index != dataGridView1.Rows.Count - 1)
                {
                    delcmd.CommandText = "DELETE FROM Order_Table WHERE Order_ID=" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + ";select last_insert_id();";
                    con.Open();
                    delcmd.Connection = con;
                    delcmd.ExecuteNonQuery();
                    con.Close();
                    dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
                    MessageBox.Show("Row Deleted");
                }
                Bind();
            }
    

    And the edit button code is:

    DataSet ds;
            SqlDataAdapter da;
            public Order_Search()
            {
                InitializeComponent();
                Bind();
                ds = new DataSet();
                
                SqlCommandBuilder cmdBldr = new SqlCommandBuilder(da);
                da.Fill(ds, "Order_Table");
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "Order_Table";
            }
    
    
    private void button4_Click(object sender, EventArgs e)
            {
                da.Update(ds, "Order_Table");
                MessageBox.Show("Edited");
            }

    The edit worked fine before.However when i runned the form once i used the delete command to delete a record and ever since that the DBConcurrency Exception Error has been occurring making me unable to edit the data in the form.I hope the above information is sufficient for you.

    Expecting your reply

    Mirfath


    *mirfath*

    Sunday, December 30, 2012 12:25 AM
  • Hello,

    What happens when you look at the return value of ExecuteNonQuery, does it return 1 for the delete SQL ?


    KSG

    Sunday, December 30, 2012 5:15 AM
  • I quite not sure about that but when I close my visual studio and open it up again it works!!

    *mirfath*

    • Marked as answer by Mirfath_ Monday, December 31, 2012 2:36 AM
    Monday, December 31, 2012 2:36 AM
  • Please note that is not really a solution but a fix as if it happened once there is no telling it will not happen again.

    KSG

    Monday, December 31, 2012 3:21 AM