System.Data.DBConcurrencyException after insert of new row, SQLCE 3.5 SP1/(2?)

[Anthony Wieser]

I knocked up a very quick database editing program using C# 2008 express.

I had my database which has an integer autonumber ID, and a bunch of other
text fields.

I then built a simple form with a DataGridView, and a couple of stray edit
fields, that are bound to values of the selected row.

If I insert a new row, it gets inserted into the database, but if I then
click on another row, and back to the new one, I get a
System.Data.DBConcurrencyException which I suspect is caused by the ID of
the row not being updated in the data table.

My OnRowValidated handler of the form says:

if (this.libraryDataSet.HasChanges())
{
this.libraryTableAdapter.Update(libraryDataSet.Library);
}

I've seen code on the web that suggests refilling the table after an insert,
but that seems like overkill to reload the whole table.
I seen another that suggests setting the "Refresh the data table" option on
the TableAdapter configuration wizard, but that is disabled with SQL Server
CE 3.5 SP1

Any suggestions on the right way to do this seemingly simple task?

[Anthony Wieser]

Post by Anthony Wieser
I knocked up a very quick database editing program using C# 2008 express.
I had my database which has an integer autonumber ID, and a bunch of other
text fields.
I then built a simple form with a DataGridView, and a couple of stray edit
fields, that are bound to values of the selected row.
If I insert a new row, it gets inserted into the database, but if I then
click on another row, and back to the new one, I get a
System.Data.DBConcurrencyException which I suspect is caused by the ID of
the row not being updated in the data table.
if (this.libraryDataSet.HasChanges())
{
this.libraryTableAdapter.Update(libraryDataSet.Library);
}
I've seen code on the web that suggests refilling the table after an
insert, but that seems like overkill to reload the whole table.
I seen another that suggests setting the "Refresh the data table" option
on the TableAdapter configuration wizard, but that is disabled with SQL
Server CE 3.5 SP1
Any suggestions on the right way to do this seemingly simple task?

More search shows that instead of updating the whole table, I can simply get
the changed row in the OnRowValidated call, and update just that.

I also added a RowUpdated handler, that creates a new SqlCeCommand that
executes SELECT@@IDENTITY by using ExecuteScalar to retrieve the new row
number.

However, I still can't see how to update the row in my DataSet, as the
column I need to change is marked read-only.

--

Anthony Wieser
Wieser Software Ltd

[ErikEJ]

Maybe you can use this:
http://blogs.msdn.com/bethmassi/archive/2009/09/15/inserting-master-detail-data-into-a-sql-server-compact-edition-database.aspx
--
Erik Ejlskov Jensen, Mobile App Dev MCTS
Check out my SQL Compact blog at
http://erikej.blogspot.com

Post by Anthony Wieser

Post by Anthony Wieser
I knocked up a very quick database editing program using C# 2008 express.
I had my database which has an integer autonumber ID, and a bunch of
other text fields.
I then built a simple form with a DataGridView, and a couple of stray
edit fields, that are bound to values of the selected row.
If I insert a new row, it gets inserted into the database, but if I then
click on another row, and back to the new one, I get a
System.Data.DBConcurrencyException which I suspect is caused by the ID of
the row not being updated in the data table.
if (this.libraryDataSet.HasChanges())
{
this.libraryTableAdapter.Update(libraryDataSet.Library);
}
I've seen code on the web that suggests refilling the table after an
insert, but that seems like overkill to reload the whole table.
I seen another that suggests setting the "Refresh the data table" option
on the TableAdapter configuration wizard, but that is disabled with SQL
Server CE 3.5 SP1
Any suggestions on the right way to do this seemingly simple task?

More search shows that instead of updating the whole table, I can simply
get the changed row in the OnRowValidated call, and update just that.
I also added a RowUpdated handler, that creates a new SqlCeCommand that
number.
However, I still can't see how to update the row in my DataSet, as the
column I need to change is marked read-only.
--
Anthony Wieser
Wieser Software Ltd