Reputation: 580
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;
}
}
Upvotes: 10
Views: 53489
Reputation: 486
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
Upvotes: 0
Reputation: 429
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
Upvotes: 0
Reputation: 239
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.
Upvotes: 3
Reputation: 141
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);
}
Upvotes: 12
Reputation: 139
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.
Upvotes: 4
Reputation: 889
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 youUpdate
, this will cause adbconcurrency
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.
Upvotes: 2
Reputation: 8736
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
Upvotes: -1
Reputation: 273429
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?
Upvotes: 3