Single row delete causes concurrency error via datagridview

[Glenn Welker]

Simply one row deleted from datagridview.

In the UserDeletedRow event we:
called endedit on the bindingsource for the grid and then call the
tableadapter.update on the dataset. The update method is when the
concurrency error happens.

Totally confused. This couldn't be a simpler example. One table with a
few columns with a single deleted row.

Ideas?

 

[Patrice]

Hello,

Optimistic concurrency is checked by selecting the row using the original
values in the where clause. If one of the values stored in the DB is
truncated because of a precision problem, the where clause won't return the
original row resulting in a concurrency error.

Which DB are you using With SQL Server just use the profiler to see the
delete statement and play with the where clause to find out which criteria
make the query to fail (starting with dates). You may want also to do the
same thing programmatically without using the datagridview. It would allow
to find out if the datagridview have something to do with the issue (IMO
not)...

 

[Andy O'Neill]

Hello,

Optimistic concurrency is checked by selecting the row using the original
values in the where clause. If one of the values stored in the DB is
truncated because of a precision problem, the where clause won't return
the original row resulting in a concurrency error.

Which DB are you using With SQL Server just use the profiler to see the
delete statement and play with the where clause to find out which criteria
make the query to fail (starting with dates). You may want also to do the
same thing programmatically without using the datagridview. It would allow
to find out if the datagridview have something to do with the issue (IMO
not)...

Rather than using compare all, use a datestamp.
Update the time when you update.
If someone else updated it since you read the data then you can tell because
the datestamp will be different.
Add Last_Updated_user Id as a colummn as well and you can also tell the user
who changed their data.

 

[Patrice]

Rather than using compare all, use a datestamp.

Update the time when you update.
If someone else updated it since you read the data then you can tell
because the datestamp will be different.
Add Last_Updated_user Id as a colummn as well and you can also tell the
user who changed their data.

Good point Andy, forgot to mention this as I focused on finding first if
this is the issue.

Glenn, if this is the issue and you are using SQL Server you can use the
rowversion datatype. The value is automatically updated when you insert or
update a row and is then use automatically to check for concurrency so you
have nothing to change to benefit from this beyond adding this column.

 

[Glenn Welker]

Good point Andy, forgot to mention this as I focused on finding first if
this is the issue.

Glenn, if this is the issue and you are using SQL Server you can use the
rowversion datatype. The value is automatically updated when you insert or
update a row and is then use automatically to check for concurrency so you
have nothing to change to benefit from this beyond adding this column.

Thanks guys,

I am using SQLite and this app only runs in a single user scenario. It
appears that the grid is doing something to one of my column values.
Haven't got it completely narrowed down, but I am guessing that it is
probably my date column. Once I stripped my 9 columns back to 2 it
works without errors.

Thanks again

 

[Patrice]

I am using SQLite and this app only runs in a single user scenario. It
appears that the grid is doing something to one of my column values.
Haven't got it completely narrowed down, but I am guessing that it is
probably my date column. Once I stripped my 9 columns back to 2 it
works without errors.

Ok then you could dump the Dataset to see differences between the new values
and the original values. It should reveal what value unexpectedly changed
because of a possible precision problem

Also a quick look at http://www.sqlite.org/datatype3.html made me think it
could be some kind of dynamic typing issue (something such as reading a 3.2
value being stored back later as a "3,2" (due to country convention) string
as SQLite seems to be able to dynamically type each value).

 

[Glenn Welker]

Ok then you could dump the Dataset to see differences between the new values
and the original values. It should reveal what value unexpectedly changed
because of a possible precision problem

Also a quick look athttp://www.sqlite.org/datatype3.htmlmade me think it
could be some kind of dynamic typing issue (something such as reading a 3..2
value being stored back later as a "3,2" (due to country convention) string
as SQLite seems to be able to dynamically type each value).

It is my datetime value but I'm not sure why it would change. The
datagridview doesn't display it so I don't think it is the problem.
The dataset must have a modified value when it is being read from the
database.

You mentioned dumping the dataset. How do I go about this?

 

[Glenn Welker]

It is my datetime value but I'm not sure why it would change. The
datagridview doesn't display it so I don't think it is the problem.
The dataset must have a modified value when it is being read from the
database.

You mentioned dumping the dataset. How do I go about this?

Here is the code I used for the dump.
foreach (DataTable table in joeDataSet1.Tables)
{
System.Diagnostics.Debug.WriteLine("TABLE " +
table.TableName.ToString() + "");
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
if (row.RowState == DataRowState.Deleted)
{
System.Diagnostics.Debug.WriteLine("Column
<strong>" + column.ToString() + "</strong> = " + row[column,
DataRowVersion.Original].ToString() + "");
}
else
{
//
System.Diagnostics.Debug.WriteLine("Column <strong>" +
column.ToString() + "</strong> = " + row[column].ToString() + "");
}
}
}
}

The curious thing is the SQLite doesn't actually have a datetime data
type. This was news to me. However the System.Data.SQLite does
implement one.