DataGridView bound to TypedDataSet - Update causes Concurrency Violation

[rmgalante]

I have a Windows Forms Application with a DataGridView which is bound
to a typed DataSet. For reasons specific to the application, I add a
record to the database table when a button is clicked on the form. This
button creates a record with default values so the user only has to
change what they do want as defaults.

During the add logic, I programmatically add a row to the typed DataSet
view with the AddMyTableNameRow method. This works. I call the row's
AcceptChanges(). Then I attempt to refresh the DataGridView with the
Refresh() command.

The problem I have is, when I edit one of the rows in the DataGridView
and call the update command, I get the Concurrency violation. The
UpdateCommand affected 0 of the expected 1 rows.

However, if I Fill the adapter after I add a record, then edit a row,
and call the update command, I do not get the Concurrency violation.

This leads me to believe that the grid is not synchronized with the
underlying table after I add a row, and the concurrency violation is
one of those error messages that means "alot of different things could
have caused the problem."

So how do I add a row to the underlying dataset, and make sure the
datagridview is synchronized with the underlying dataset, so that
subsequent updates do not fail?

Thanks.

 

[Earl]

This sounds like you are not repopulating the datasource for the
datagridview with fresh data. I've seen this problem with "natural" primary
keys that have no other method of enforcing concurrency (i.e., timestamp),
and also if your stored procedure for Insert does not return a fresh row of
data as part of the Insert procedure (which is what this sounds like, since
you can Fill after Insert and eliminate the error).

 

[Cor Ligthert [MVP]]

Hi,

Try to learn yourself never to use the Acceptchanges except as you are
really sure you need it.

Cor

 

[RobinS]

From Brian Noyes' data binding book:

If you have made any changes to the rows in your data set and call
AcceptChanges, the original values for all modified rows will be
replaced with the current values, and the state of all rows will
be set to Unchanged.

You should be aware when using these methods, however, that you
generally want to avoid using them if the data in the data set
will be used to update a database through a data adapter.
The data adapter figures out which commands to execute (update,
insert or delete) for which rows based on their row state.

Rows with a row state of Unchanged don't get any command
executed against them when performing an update.

Instead of calling AcceptChanges, why don't you update the
dataset through the data adapter? I think this would write
the data to the underlying database, and update your dataset
at the same time.

That's my two cents' worth. Hope it helps.

Robin S.

 

[Michael Milonov]

hi!
What RDBMS do you use?

//-----------------------------
Best Regards,
Michael Milonov
http://www.snotratech.com (connected DataSet for Oracle DB)

 

[rmgalante]

I'm using SQL Server 2005.

In my table, I don't configure the primary key as an Identity field
that auto-increments. I use a stored procedure to insert and update
records, and this stored procedure gets the next available id from a
table of ids for the primary key of the record.

I don't use the standard table adapter logic to perform updates and
inserts, at least not the logic that is generated by the typed dataset,
because it doesn't generate a unique primary key for me on Insert.

So I use a business object to store the new record. Then I call the
AddMyTableNameRow method on the typed dataset's table. Then to make
sure I don't get a primary key violation when I update that record
later, I call AcceptChanges on the row.

But when I update that row, now I get the concurrency violation instead
of the primary key violation. Filling the data adapter fixes the
problem for now. But I need to figure out how to do this eventually.

Rob