SQLDataAdapter and concurrency violations

[STom]

I have a data grid that has a datasource that is a dataset with one table.

I am using a SQLDataAdapter to get the data from the database and do the
updates against and MSDE database. I am consistently running into Update and
Delete concurrency violations.

I am having a hard time pinpointing the exact circumstances where the update
concurrency violation happens. Basically, what I see consistently is an
Update violation when I do:
1. I have 3 rows in my data grid.
2. I delete the bottom two rows.
3. I add a new row.

When I do a save, I have my calls in this order:
1. insert
2. update
3. delete

Apparently this is going to be a little more complex than I thought. For
example, if I add and then delete rows before I save it, what state would
that row be in? It should never be in the database anyway although at one
point it was added and deleted to the table.

Should I have to order of my insert/update/delete in any specific order?

Thanks.

STom

 

[Scott M.]

The order of your sql statements makes no difference.

Make sure that in each of your sql statements you include a "where" that
isolates the primary key field of the record(s) being modified and then make
sure that insert, update and delete also have a select appended on to the
end of each of them to essentially "refresh" the data after each operation.

The key to managing concurrency is with the update statements that you
write, make sure primary key values are used.

 

[scorpion53061]

STom,

My experience wiht concurrency violations usually occured when somehow the
column values somehow were changed and no longer matched those in the
dataset schema.

In this example:
Me.SqlInsertCommand4.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@UCOST", System.Data.SqlDbType.Float, 8,
"UCOST"))

go through each of your fields and make sure the datatype is the same, AND
the value (which in this case is '8') is the same.

When I got concurrency violations it was usually becasue some well meaning
person changed the value of a field in the table on the server.

 

[STom]

Scott,

Thanks for the info. I found that in the case of the insert, I did not need
to have a primary key because the record(s) being inserted have an identity
key, but I did add a select statement at the end where the primary key =
@@IDENTITY.

I then added select statements to my delete and update command. Both of
these already included primary key info in the where statement.

For now, it appears that the beast has been tamed...didn't get any
concurrency violations.

We'll see how it goes.

Thanks again.

STom

 

[Scott M.]

NP - Good luck!

Scott,

Thanks for the info. I found that in the case of the insert, I did not need
to have a primary key because the record(s) being inserted have an identity
key, but I did add a select statement at the end where the primary key =
@@IDENTITY.

I then added select statements to my delete and update command. Both of
these already included primary key info in the where statement.

For now, it appears that the beast has been tamed...didn't get any
concurrency violations.

We'll see how it goes.

Thanks again.

STom