Problems call DataAdapter.Update and Concurrency

[Steve Lloyd]

Hi all,

I wonder if anyone can explain what is happening here?
I have a dataadapter that I am using to correlate the changes in an SQL
Table to a DataTable. I create a new DataRow then call .Update and
acceptchanges, all works fine. The problem I have is when I try to amend
some of the data and the call the Update again. This time I get a
Concurrency error, which is understandable as the data has changed since I
initially created the table but is not the desired outcome and I was
expecting that after accepting changes can calling the .Fill the concurrency
conflict would be resolved.

Is there a way to get the adpater to update the changed rows. I have tried
calling .Fill after the update but this does not seem to help. Ideally I
would like to work on the datatable and not have to repopulate from the
database every time I update a cell/row.

Thanks for any help you can give.

Steve

Here is the code it use:
Try
_tradeAdapter.Update(_detailsTable)
_detailsTable.AcceptChanges()
_tradeAdapter.Fill(_detailsTable)
Catch exTran As DBConcurrencyException
MsgBox("There has been a conflict error on this trade, please refresh your
details and check", MsgBoxStyle.Critical, "Update Conflict")
s.Logging.ApplicationLog.LoggingLevels.ApplicationError)
End Try

 

[Steve Lloyd]

Sorry, I did not call the .Fill correctly, it works if i do this, however,
the question still remains of whether this can be done without calling the
..fill and avoiding another trip to the database.

Thanks again

Steve

 

[JoWilliam]

I also had Concurrency violation when updating an existing record. Check
the OleDbDataAdapter's UpdateCommand and the OleDbParameters used in that
command.

See my thread
http://groups.google.co.uk/group/mi..._frm/thread/4cf3fd3eabb5b85c/6102ad1bb75554ad
for how I solved it.

 

[JoWilliam]

Hi,

In my case only one user accesses the database.

Your comment has puzzled me a bit though: if the original code generated by
the OleDbDataAdapter and DataSet designer has the extra benefit of allowing
concurrent access to a multi-user database, could you explain why updating
an existing record didn't work in my single user environment?

 

[Bart Mermuys]

Hi,

Sorry, I did not call the .Fill correctly, it works if i do this, however,
the question still remains of whether this can be done without calling the
.fill and avoiding another trip to the database.

By default the DataAdapter will call AcceptChanges on each succesfull
updated row, so you should not call it. Although not necesairly the case
here, calling AcceptChanges at the wrong time may even cause a false
"Concurrency Violation".

If i understand it correctly, you only have this problem when you do : add
record, update, modify same record, update and the problem is helped with
calling Fill in between. Do you have an autonumber PK column in the DB
table, if so , which DB are you using and how are you currently building
the insert command (DataAdapter) ?

HTH,
Greetings

 

[William \(Bill\) Vaughn]

Ah, you should not have to call AcceptChanges unless you're executing your
own code to post the changes to the database and not using the action
Commands associated with the DataAdapter.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Steve Lloyd]

Hi Bart,

Yes there is a PK column that I am returning by using the Rowupdated event
for any .Inserted row.
I use a command builder for update command as I want to make use of the
concurrency exception.
I dont know if it is by design for if I have got something wrong, but I can
call .update many times as long I don't change the data (pretty pointless)
as soon as the data gets changed it causes a concurrency violation. In some
ways this is as desired but I wasn't expecting the violation while using the
same Connection as the first update. The only solution I can find is to
repopulate the datatable using the .fill after each update. This works fine
but is costly as the application uses a heavily lagged WAN.

Any thoughts?

Thanks again,

Steve.

 

[Bart Mermuys]

Hi,

Hi Bart,

Yes there is a PK column that I am returning by using the Rowupdated event
for any .Inserted row.
I use a command builder for update command as I want to make use of the
concurrency exception.

Sounds ok, you already verified you're getting back the right key (DB
generated key) after update for new records...?

I dont know if it is by design for if I have got something wrong, but I
can call .update many times as long I don't change the data (pretty
pointless) as soon as the data gets changed it causes a concurrency
violation.

I'm a bit confused here, in your OP you did mention a certain sequence
(add,update,ammend,update) but here you are only talking about changing
data. When exactly do and don't you have this concurrency violation
exception.

In some ways this is as desired but I wasn't expecting the violation while
using the same Connection as the first update. The only solution I can
find is to repopulate the datatable using the .fill after each update.
This works fine but is costly as the application uses a heavily lagged
WAN.

I don't get what you mean with "same connection" and not expecting a
concurrency violation. To be clear, the optimistic concurrency control will
cause a concurrency violation exception when the record has been changed in
the DB by something else/other between the last fill and current update.

You could extend your code a bit:

Try
_tradeAdapter.Update(_detailsTable)
Catch ex As DBConcurrencyException
Console.WriteLine( ex.Message )

If ( ex.Row.RowState = DataRowState.Modified ) Then
For Each col As DataColumn In ex.Row.Table.Columns
Console.WriteLine( "{0} oldvalue={1} newvalue={2}", _
col.ColumnName, _
ex.Row( col, DataRowVersion.Original ), _
ex.Row( col, DataRowVersion.Current ) )
Next
End If
End Try

When you get a violation exception, you can compare the old values with the
current values in the DB and if they are different then it's normal you get
a concurrency violation, wrong keys may also cause this exception.


HTH,
Greetings

 

[RobinS]

Here's your problem:

I use a command builder for update command as I want to make use of
the concurrency exception.

The commands built by this do a match based on all of the fields in the
record when doing updates, so if any of your fields change and you don't
retrieve them back, like a date_updated or something like that, it can't
find the record when it does the second update because it's looking for
matches for all of the fields.

I usually use a stored procedure that performs the update and then
returns the fields back with a SELECT statement so I can refresh
my object accordingly. Or you can re-query. Or write your own
logic and just use the primary key if you feel comfortable with
that.

I think there's a setting in the table adapter properties to change
this to only use the PK, but I don't remember what it is offhand.

As Bill pointed out, it's best to write your own logic to do updates.
the CommandBuilder is dangerous.

Robin S.
------------------------------

 

[Steve Lloyd]

Hi Guys,

Thanks for all the info, I think my understanding of the DataAdapter is
lacking somewhat but is now getting there thanks to you. I am trying to do
too many updates with out re-syncing the data which is causing the violation
exception.

Thanks for all your help.