9

I am new to c# and try to bind a datagridview to a mssql database in visual studio 2010. The databindings are OK and everything seems to work. Except for a few strange errors:

I get the error in the subject after: updating the same row 2 times, deleting a new inserted row, after updating a row when an other row was deleted (word changes to DeleteCommand)

None of the solutions I found on Google workes for me. I hope someone can help me with this. Here is te code:

    private void fillDatagrid()
        {
            //fill datagrid ADO.NET
            conn = new SqlConnection(TestApp.Properties.Settings.Default.TestdatabaseConnectionString);
            cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = "SelectFrom";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
            cmd.Parameters.Add("@filters", SqlDbType.NVarChar, 300).Value = "";

            adapt = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adapt.Fill(dt);
            dt.TableName = "Countries";

            conn.Close();

            BindingSource src = new BindingSource();
            src.DataSource = dt;
            dt.RowChanged += new DataRowChangeEventHandler(dt_RowChanged);

            dgDatabaseGrid.DataSource = src;
            dgDatabaseGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            //dgDatabaseGrid.RowValidating += new DataGridViewCellCancelEventHandler(dgDatabaseGrid_RowValidating);

            //disable columns:
            dgDatabaseGrid.Columns[0].Visible = false;
            dgDatabaseGrid.Columns["date_insert"].Visible = false;
            dgDatabaseGrid.Columns["user_insert"].Visible = false;
            dgDatabaseGrid.Columns["date_change"].Visible = false;
            dgDatabaseGrid.Columns["user_change"].Visible = false;
            dgDatabaseGrid.Columns["deleted"].Visible = false;

            //auto size last column
            dgDatabaseGrid.Columns["remarks"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


            SqlCommandBuilder cb = new SqlCommandBuilder(adapt);
        }

        void dt_RowChanged(object sender, DataRowChangeEventArgs e)
        {
            try
            {
                adapt.Update(dt);
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }

private void dgDatabaseGrid_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (!e.Row.IsNewRow)
            {


                DialogResult response = MessageBox.Show("Are you sure?", "Delete row?",
                                     MessageBoxButtons.YesNo,
                                     MessageBoxIcon.Question,
                                     MessageBoxDefaultButton.Button2);

                if (response == DialogResult.Yes)
                {

                    //ipv delete --> deleted=1
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "DeleteFrom";
                    cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = e.Row.Cells[0].Value;
                    cmd.ExecuteNonQuery();
                    conn.Close();


                    //delete from datagrid:
                    dt.Rows[dgDatabaseGrid.SelectedCells[0].RowIndex].Delete();

                }

                //always cancel!
                e.Cancel = true;

            }
        }
3
  • 2
    When using update, table rows need to be complete. In other word, the fill 'select' statement must have -all- the columns.
    – fcm
    Nov 20, 2019 at 22:45
  • @fcm not true. the update commands is based on the select command and if the select command misses 2 columns from the table then simply the update command "does not know" about them and simply will not update them
    – kuklei
    Nov 25, 2020 at 15:55
  • @fcm, your comment gave me some hint. In my case, I used select * from table instead of listing individual columns, and I was running into this fake concurrency issue issue.
    – Patel
    Feb 4, 2022 at 23:51

8 Answers 8

11

I know it's very late but maybe it will help someone.

Made the following changes to your code:

try
{
    adapt.Update(dt);

Put these lines here and use your variable

    Me.yourTableAdapter.Update(Me.yourDataSet.yourTable)
    Me.yourDataSet.youTable.AcceptChanges()
    Me.yourTableAdapter.Fill(Me.yourDataSet.yourTable)

it worked like a charm for me hope it will work for you.

}
catch (SqlException ex)
{
    Debug.WriteLine(ex.Message);
}
3
  • Nice! this helped me to fix my issue, however, edited for better outcome.. filling a dataset.table that already contains data will show duplicated rows..
    – CularBytes
    Dec 23, 2014 at 19:45
  • Me.yourTableAdapter.Update(Me.yourDataSet.yourTable) - I have exception at this line already, so not sure the above answer works
    – phanvugiap
    Apr 23, 2016 at 8:21
  • 1
    But there is a problem in this, if u are binding a datagridview to the dataset the values will be duplicated until u reload the form Sep 3, 2016 at 19:54
4

If I may add my two cents worth.

I have struggled with this for a while. In our application we have calculated columns where the column is the result of some calculation of two or more other columns.

This Recalc threw the Adapter.

I had to set SqlCommandBuilder.ConflictOption = ConflictOption.OveriteChanges to get around this issue.

I do not know if there is an option to tell the Adapter to ignore read only columns when it does the checking.

3

after updating the same row 2 times

Is there a Timestamp column (or any other column that is changed/filled on the Db server) ?

Your problem could happen when the in-memory row is different from what's in the Db. And because you use a SP for the SelectCmd there (probably) is no refresh after an update.

after deleting a new inserted row

Similar, caused not fetching the new Id after an insert

after updating a row when an other row was deleted (word changes to DeleteCommand)

totally unclear.
But why do you Delete rows 'manually' instead of leaving it to the adapt.Update() ? And are you sure that not both methods are executed?

1
  • Your answer is exactly what I should knew, giving me enough clue to solve my problem. Thanks Henk. Dec 12, 2015 at 16:09
3

To resolve it I simply turned Optimistic Concurrency on the Table Adaptor:
- Open the Dataset
- Right-click the Table Adaptor, choose Configure...
- Click the Advanced Options button
- Deselect 'Use optimistic concurrency', click OK
- Click through the rest of the wizard.

1
  • 1
    After chasing this error for days this was the only fix that worked. Thank you for your post! This should be much higher than it really is.
    – Siniseus
    Nov 2, 2021 at 3:17
2

I have been chasing this error in my application for weeks! I finally found my issue.

What I found in my application...

I have many textboxes, comboboxes, etc. bound with databindings. Some of these fields are being updated from combinations of other fields. This all works great with one exception

If one of the calculated fields gets re-calculated after you EndEdit and before you Update, this will cause a dbconcurrency violation.

This error doesn't have to mean that the row doesn't exist any more; it simply means that it didn't update a row for some reason. My reason was that the data had three different states so it thought that someone else had changed the data before I called the Update.

BTW, this is a single MDF located on the users computer so no one else has access to it to change it during the Update. One user, One Update. My code was the "other" user.

Hope this can help point someone else in the right direction for their application.

1
  • 1
    After all the changes simply call dataTable.AcceptChanges();
    – Vectoria
    Jan 11, 2017 at 11:06
0

Different solution for different situation. I got the same concurrency error but it was happening due to auto-generated update command being incorrect. Most likely, it happened because I might be missing keys on table when I created the table adapter from existing table.

so, my update command was looking to update was comparing every single column in where clause which will never match if there are differences.

e.g. following will return 0 for table x having 3 columns if you change @a,@b or @c.

Update table [x] 
set a = @a, b = @b, c = @c 
where a = @a, b = @b, c = @c
0

I ran into this issue when I added a column while my application was running, after restating my application it worked fine.

This was due to my Select * from abcde that filled my dataGrid

-1

Simple answer:

This simply means that if you are trying to update a row that no longer exists in the database.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.