I have written a Visual Studio .NET 2003 app, using ADO.NET to retrieve
several hundred records out of an Access 2000 database, and putting them all
into a DataSet.  Then my app goes against a SQL Server 2000 database,
retrieves other data and updates about 500 records in the DataSet.  I am
only interested in updating about 50 of the columns in each row (there are
about 150 columns in the table), I decided to write my own update command
and associate it with the OleDbDataAdapter that I use to retrieve the
original table from the Access database.  And I also thought I would
determine how many records there are to update, before I retrieve anything
from SQL Server.

When I run my app, it gives me an error that says, "Concurrency violation:
the UpdateCommand affected 0 records."

However, I have also discovered that this error message isn't exactly true.
It has, in fact, updated 1 record.  It does that each time I run the app, as
the total count of records needing updating is 1 less each time I run this.
So, my guess is that it is updating the first record and then something
stops it from updating anything more.

I have experimented, to a small degree, with isolation level, but Access
doesn't support much, along those lines.

So, what am I doing wrong, and what could I do to make it possible for the
app to submit all 500 records, rather than having me run my application 500
times?

The DBConcurrencyException happens when Update believes an update or delete
commands didn't actually affect any records on the backend, aka when
ExecuteNonQuery returned 0.

As a workaround, you can listen to the OleDbDataAdapter.RowUpdated event and
change the OleDbRowUpdatedEventArgs.Status from ErrorsOccurred to Continue
when you believe the correct thing actually happend.

But I belive it is more important to understand why the row in the database
was updated (corrrect?), yet the records affected is 0.

Show quoteHide quote

Rod wrote:
I have seen this error when using Access because the OleDbDataAdapter treats
parameter queries differently than the SqlDataAdapter. If you are writing
your UPDATE queries like this (based on SQL Server constructs):

UPDATE [table] SET [col1] = @col1, [col2] = @col2 WHERE [pk1] = @pk1 AND
[pk2] = @pk2

You have to be careful, because although the SQL driver will correctly
interpret the parameter names (@col1), the OleDb driver will not. To OleDb,
the query looks like this:

UPDATE [table] SET [col1] = ?, [col2] = ? WHERE [pk1] = ? AND [pk2] = ?

So your named parameters for SQL Server suddenly become anonymous
placeholders for Access. So when sending this type of query to Access, the
order in which you add the parameters to the Command object becomes more
important than the parameter name.

If you add the parameter for @pk2 before the parameter for @pk1, then you
can end up with the concurrency error because the values are switched for
the two columns. Note that you can still give the parameters names, but the
OleDb provider will simply ignore them.

I am using an Access update query, with parameters, for the
OleDBDataAdapter's UpdateCommand.  Because there is roughly 50 parameters, I
won't list them all, but a partial listing follows:

PARAMETERS [Client ID] Text ( 255 ), [Grant ID] Text ( 255 ), [Intake ID]
Long, [Interview Date] Text ( 255 ), [Interview Type] Long, GM_1 Short, GM_2
Short, GM_3 Short, GM_4 Short, GM_5
.... (several more like this)
GS_33 Short, GS_34 Short, GS_35 Short, GS_36 Short;
UPDATE ATR_NM_0009_200604140933_CR_00001_RCV SET
ATR_NM_0009_200604140933_CR_00001_RCV.GM_1 = [GM_1],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_2 = [GM_2],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_3 = [GM_3],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_4 = [GM_4],
ATR_NM_0009_200604140933_CR_00001_RCV.GM_5 = [GM_5],
.... (several more like this)
ATR_NM_0009_200604140933_CR_00001_RCV.GS_33 = [GS_33],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_34 = [GS_34],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_35 = [GS_35],
ATR_NM_0009_200604140933_CR_00001_RCV.GS_36 = [GS_36]
WHERE (((ATR_NM_0009_200604140933_CR_00001_RCV.[Client ID])=[Client ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Grant ID])=[Grant ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Intake ID])=[Intake ID]) AND
((ATR_NM_0009_200604140933_CR_00001_RCV.[Interview Date])=[Interview Date])
AND ((ATR_NM_0009_200604140933_CR_00001_RCV.[Interview Type])=[Interview
Type]));

Then, a partial listing of the C# code which creates the UpdateCommand
follows:

OleDbCommand cmd = new OleDbCommand("[Update GM and GS]", oleDbConn);

cmd.CommandType = CommandType.StoredProcedure;

OleDbParameterCollection pc = cmd.Parameters;

OleDbParameter param;

//WHERE parameters

param = pc.Add("Client ID", OleDbType.VarChar, 255, "Client ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Grant ID", OleDbType.VarChar, 255, "Grant ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Intake ID", OleDbType.Integer, 0, "Intake ID");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Interview Date", OleDbType.VarChar, 255, "Interview Date");

param.SourceVersion = DataRowVersion.Original;

param = pc.Add("Interview Type", OleDbType.Integer, 0, "Interview Type");

param.SourceVersion = DataRowVersion.Original;

//new values for parameters

//GM section

pc.Add("GM_1", OleDbType.SmallInt, 0, "GM_1");

pc.Add("GM_2", OleDbType.SmallInt, 0, "GM_2");

pc.Add("GM_3", OleDbType.SmallInt, 0, "GM_3");

pc.Add("GM_4", OleDbType.SmallInt, 0, "GM_4");

pc.Add("GM_5", OleDbType.SmallInt, 0, "GM_5");

//several more like this...

I'd appreciate whatever help/insight you can give.

Rod


Show quoteHide quote

Rod wrote:
Show quoteHide quoteWow, that's a lot. Offhand, I would say it's the DateTime field that's
causing the problem. Are all those fields necessary in the WHERE clause?

Yes, all of the fields are necessary in the WHERE clause.

The Datetime field is defined as a text field, although it doesn't have to
be 255 characters wide.  I've shortened the length of the datetime fields to
10 characters, which is consistent with the length of the field as it is
defined in the database.

Rod


Show quoteHide quote

I found the error, and it was mine.  I had left out the invocation of the
OleDbCommand object's ExecuteNonQuery() method.

Rod


Show quoteHide quote

ReadXMLSchema Doesn't Work
Problem with updating MDF Data file as opposed to updating a running SQL Server DB
Batch Update Problem
Getting a file's ContentType
Need advice for Parent Child insert in transaction mode