VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation

R

Rod

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?
 
G

Guest

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.
 
M

Matt Noonan

Rod said:
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.
Click to expand...

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
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
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.
 
R

Rod

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


Matt Noonan said:
Rod said:
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.
Click to expand...

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
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
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.
Click to expand...
 
M

Matt Noonan

Rod said:
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]));
Click to expand...

Wow, 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?
 
R

Rod

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


Matt Noonan said:
Rod said:
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]));
Click to expand...

Wow, 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?
Click to expand...
 
R

Rod

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

Rod


Rod said:
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


Matt Noonan said:
Rod said:
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]));
Click to expand...

Wow, 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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concurrency violation problem. 1
Concurrency violation at update 7
concurrency violation 1
Concurrency violation 5
Concurrency violation and DateTime 13
VB ADO.NET Concurrency, Access, date fields and parameters 4
Concurrency Violation 5
Concurrency Violation Help 1

Top