Concurrency violation: the UpdateCommand affected 0 of the expected 1records.

[bixbarton]

Hi, I've got a small program, using a DataSet and OracleDataAdapter.

Fills a table ok.

Loop through the rows ok.

Change a couple of values.

Then perform an Update on the OracleDataAdapter and it gives me the
dreaded...

"Concurrency violation: the UpdateCommand affected 0 of the expected 1
records."


I've searched all over the net for help on this, but nothing I find
seems to address the problem.

I've got no AcceptChanges in there.
I've tried FillSchema() before the Update()
I've tried setting any string.empty values to DBNull.

No joy.

And of course, there is no inner exception to provide more help.

Any help would be much appreciated, this is driving me crazy.

 

[Mel Weaver]

I don't know much about Oracle but with SQL server each table must have a
primary unique key, if not you will get the concurrency error.

 

[Arne Vajhøj]

I don't know much about Oracle but with SQL server each table must have
a primary unique key, if not you will get the concurrency error.

Note that this must be a DataAdapter/DataSet requirement, both
Oracle and SQLServer actually allows tables with no PK.

My guess is that it is extremely rare in Oracle database - if you
can afford the Oracle license, then you can also afford to hire
a DBA that has a clue.

Arne

 

[bixbarton]

Hi, yes it is very rare on Oracle.

I've checked, just in case, and the table I'm working with does have a
primary key.

Another oddity, is that I've discovered this problem is only happening
when you use the fresh QA version of our database. If you use the Dev
version used when it was built there are no issues. So trying to
ascertain whether there are differences inside the two databases, at
present it doesn't appear so.

Is there any way of debugging inside the Update operation, so that you
can see the individual SQL statements being run, rather than trying to
piece it together from the command templates on UpdateCommand with the
parameters?

I'm also trying to get a trace operation running on the Oracle DB like
a SQL Server Profiler to see if I can get the raw SQL statements.

 

[J.B. Moreno]

Hi, yes it is very rare on Oracle.

I've checked, just in case, and the table I'm working with does have a
primary key.

Another oddity, is that I've discovered this problem is only happening
when you use the fresh QA version of our database. If you use the Dev
version used when it was built there are no issues. So trying to
ascertain whether there are differences inside the two databases, at
present it doesn't appear so.

Is there any way of debugging inside the Update operation, so that you
can see the individual SQL statements being run, rather than trying to
piece it together from the command templates on UpdateCommand with the
parameters?

Do you mean something like:

string updStr = da.UpdateCommand.CommandText;

It's going to show the parameters, not their values, but you should be
able to figure out the values that went into by examining the exception
(take a look at Row).

 

[bixbarton]

Yeah, I've figured that out, I was hoping to avoid patching it
together myself, incase I miss any obvious bloopers caused by the
state of some of the parameters. If there's no avoiding it, then I
suppose I'll give it a go.

 

[Harlan Messinger]

Note that this must be a DataAdapter/DataSet requirement, both
Oracle and SQLServer actually allows tables with no PK.

Possibly. I had a similar situation years ago, when beginning to work on
an existing application, and using Microsoft Access to work with SQL
Server data. I'd get an error message when I'd try to delete a row in
some tables--and then discovered that primary keys had been defined for
them.

 

[bixbarton]

Possibly. I had a similar situation years ago, when beginning to work on
an existing application, and using Microsoft Access to work with SQL
Server data. I'd get an error message when I'd try to delete a row in
some tables--and then discovered that primary keys had been defined for
them.

How annoying, I constructed the update command from the template in
UpdateCommand and applied the parameter values (cheated by dropping in
at UpdatingRow point and getting it to do all the parsing for me).

Ran the command manually in SQLPlus, no problems.

And yet the update as part of the DataSet still gives the concurrency
error.

Grr... no useful error messages.

 

[bixbarton]

How annoying, I constructed the update command from the template in
UpdateCommand and applied the parameter values (cheated by dropping in
at UpdatingRow point and getting it to do all the parsing for me).

Ran the command manually in SQLPlus, no problems.

And yet the update as part of the DataSet still gives the concurrency
error.

Grr... no useful error messages.

I've even stripped the DataSet UpdateCommand back to only the bare few
columns that need updating.

UPDATE LE_PALC_REQUEST SET ACCEPTANCE_SENT_LAST_ATTEMPT
= :LastAttempt, ACCEPTANCE_RECEPTION_CODE = :RecCode,
ACCEPTANCE_RECEPTION_ERROR = :RecError WHERE (INTERNAL_ID
= :Original_INTERNAL_ID)

Then submitted the exact same LastAttempt, RecCode and RecError
parameters in four updates.

LastAttempt from DateTime.Now
RecCode = 0
RecError = null

The only difference between the attempts is the Original_INTERNAL_ID
identifying which record we're updating.

126 works
127 fails
131 fails
165 works

Utter madness...

 

[bixbarton]

I've even stripped the DataSet UpdateCommand back to only the bare few
columns that need updating.

    UPDATE LE_PALC_REQUEST SET ACCEPTANCE_SENT_LAST_ATTEMPT
= :LastAttempt, ACCEPTANCE_RECEPTION_CODE = :RecCode,
ACCEPTANCE_RECEPTION_ERROR = :RecError WHERE (INTERNAL_ID
= :Original_INTERNAL_ID)

Then submitted the exact same LastAttempt, RecCode and RecError
parameters in four updates.

LastAttempt from DateTime.Now
RecCode = 0
RecError = null

The only difference between the  attempts is the Original_INTERNAL_ID
identifying which record we're updating.

126 works
127 fails
131 fails
165 works

Utter madness...

I'm still getting nowhere with this.

Think I'm going to have to dump the use of DataSets and just handcode
the DB transactions.

Shame that DataSets aren't more transparent.