MS Access concurrency exception

  • Thread starter Thread starter Charlie Williams
  • Start date Start date
C

Charlie Williams

I am having difficulty performing updates and deletions
on an Access database using the Update() method of the
OleDBDataAdapter. I can insert rows without a problem,
but I get a concurrency exception when trying to update
or delete.

I am quite sure that no concurrency conflicts actually
exist. Is there a reason why the data adapter I am using
may have a different row version that the database that
it got its data from?

I can work around the problem by removing the concurrency
check from the update command, but would rather not
remove checking all together, for obvious reasons.

Has anybody experienced a similar problem with MS Access?

Thank you for any help.

P.S. The exact same code works flawlessly with SQL
Server, but I can't use it in this particular situation.
 
Just to illistrate my point, I created a new project and
included this code:

DataTable dt = new DataTable();
oleDbDataAdapter1.Fill(dt);
dt.Rows[5]["Superintendent"] = "Craig Birch";
try{
oleDbDataAdapter1.Update(dt);
Console.WriteLine("Update successful.");
}
catch(Exception ex){
Console.WriteLine("Update unsuccessful.");
Console.WriteLine(ex.Message);
}


Minimalist code to be sure. All I did was replace one
string value with another and try to update. I get the
following error:

Concurrency violation: the UpdateCommand affected 0
records


Any ideas?
 
What's your DataAdapter SelectCommand and UpdateCommand?

Charlie Williams said:
Just to illistrate my point, I created a new project and
included this code:

DataTable dt = new DataTable();
oleDbDataAdapter1.Fill(dt);
dt.Rows[5]["Superintendent"] = "Craig Birch";
try{
oleDbDataAdapter1.Update(dt);
Console.WriteLine("Update successful.");
}
catch(Exception ex){
Console.WriteLine("Update unsuccessful.");
Console.WriteLine(ex.Message);
}


Minimalist code to be sure. All I did was replace one
string value with another and try to update. I get the
following error:

Concurrency violation: the UpdateCommand affected 0
records


Any ideas?
-----Original Message-----
I am having difficulty performing updates and deletions
on an Access database using the Update() method of the
OleDBDataAdapter. I can insert rows without a problem,
but I get a concurrency exception when trying to update
or delete.

I am quite sure that no concurrency conflicts actually
exist. Is there a reason why the data adapter I am using
may have a different row version that the database that
it got its data from?

I can work around the problem by removing the concurrency
check from the update command, but would rather not
remove checking all together, for obvious reasons.

Has anybody experienced a similar problem with MS Access?

Thank you for any help.

P.S. The exact same code works flawlessly with SQL
Server, but I can't use it in this particular situation.
.
Click to expand...
Click to expand...
 
The update command was generated by VS.net I did not
alter it.

Select:
SELECT Added, Builder, Complete, CompleteDate, Details,
JobName, JobNumber, LeaveOffList, Notes, StartDate,
Super, TrenchDate, WorkType FROM Jobs WHERE (LeaveOffList
= false)

Update:
UPDATE Jobs SET Added = ?, Builder = ?, Complete = ?,
CompleteDate = ?, Details = ?, JobName = ?, JobNumber
= ?, LeaveOffList = ?, Notes = ?, StartDate = ?, Super
= ?, TrenchDate = ?, WorkType = ? WHERE (JobNumber = ?)
AND (Added = ? OR ? IS NULL AND Added IS NULL) AND
(Builder = ? OR ? IS NULL AND Builder IS NULL) AND
(Complete = ?) AND (CompleteDate = ? OR ? IS NULL AND
CompleteDate IS NULL) AND (Details = ? OR ? IS NULL AND
Details IS NULL) AND (JobName = ? OR ? IS NULL AND
JobName IS NULL) AND (LeaveOffList = ?) AND (Notes = ?
OR ? IS NULL AND Notes IS NULL) AND (StartDate = ? OR ?
IS NULL AND StartDate IS NULL) AND (Super = ? OR ? IS
NULL AND Super IS NULL) AND (TrenchDate = ? OR ? IS NULL
AND TrenchDate IS NULL) AND (WorkType = ?)

-----Original Message-----
What's your DataAdapter SelectCommand and UpdateCommand?

Just to illistrate my point, I created a new project and
included this code:

DataTable dt = new DataTable();
oleDbDataAdapter1.Fill(dt);
dt.Rows[5]["Superintendent"] = "Craig Birch";
try{
oleDbDataAdapter1.Update(dt);
Console.WriteLine("Update successful.");
}
catch(Exception ex){
Console.WriteLine("Update unsuccessful.");
Console.WriteLine(ex.Message);
}


Minimalist code to be sure. All I did was replace one
string value with another and try to update. I get the
following error:

Concurrency violation: the UpdateCommand affected 0
records


Any ideas?
-----Original Message-----
I am having difficulty performing updates and deletions
on an Access database using the Update() method of the
OleDBDataAdapter. I can insert rows without a problem,
but I get a concurrency exception when trying to update
or delete.

I am quite sure that no concurrency conflicts actually
exist. Is there a reason why the data adapter I am using
may have a different row version that the database that
it got its data from?

I can work around the problem by removing the concurrency
check from the update command, but would rather not
remove checking all together, for obvious reasons.

Has anybody experienced a similar problem with MS Access?

Thank you for any help.

P.S. The exact same code works flawlessly with SQL
Server, but I can't use it in this particular situation.
.
Click to expand...
Click to expand...


.
Click to expand...
 
One problem I had before was when I was missing a parameter the
UpdateCommand, for some strange reason when you are missing a parameter it
throws a very misleading concurrency exception rather than something more
informative. Now I'm not saying that's your problem but you might check the
parameter collection of your update command and see if they are all there.
Another thing you might try is hooking the RowUpdating event of your
DataAdapter and examining the actual values that are being passed. I use
something like this:

public void MyRowUpdateHandler(object sender, OleDbRowUpdatingEventArgs e)

{

System.Diagnostics.Debug.WriteLine(e.Command.CommandText);

string myparams = "";

foreach (OleDbParameter thisparam in e.Command.Parameters)

{

if (thisparam.Value!=null)

{

myparams += thisparam.SourceColumn + " " + thisparam.Value.ToString() +
"\n";

}

}

System.Diagnostics.Debug.WriteLine(myparams);

}

It sometimes helps to diagnose a problem. Give it a try and see if you spot
anything odd.



Charlie Williams said:
The update command was generated by VS.net I did not
alter it.

Select:
SELECT Added, Builder, Complete, CompleteDate, Details,
JobName, JobNumber, LeaveOffList, Notes, StartDate,
Super, TrenchDate, WorkType FROM Jobs WHERE (LeaveOffList
= false)

Update:
UPDATE Jobs SET Added = ?, Builder = ?, Complete = ?,
CompleteDate = ?, Details = ?, JobName = ?, JobNumber
= ?, LeaveOffList = ?, Notes = ?, StartDate = ?, Super
= ?, TrenchDate = ?, WorkType = ? WHERE (JobNumber = ?)
AND (Added = ? OR ? IS NULL AND Added IS NULL) AND
(Builder = ? OR ? IS NULL AND Builder IS NULL) AND
(Complete = ?) AND (CompleteDate = ? OR ? IS NULL AND
CompleteDate IS NULL) AND (Details = ? OR ? IS NULL AND
Details IS NULL) AND (JobName = ? OR ? IS NULL AND
JobName IS NULL) AND (LeaveOffList = ?) AND (Notes = ?
OR ? IS NULL AND Notes IS NULL) AND (StartDate = ? OR ?
IS NULL AND StartDate IS NULL) AND (Super = ? OR ? IS
NULL AND Super IS NULL) AND (TrenchDate = ? OR ? IS NULL
AND TrenchDate IS NULL) AND (WorkType = ?)

-----Original Message-----
What's your DataAdapter SelectCommand and UpdateCommand?

Just to illistrate my point, I created a new project and
included this code:

DataTable dt = new DataTable();
oleDbDataAdapter1.Fill(dt);
dt.Rows[5]["Superintendent"] = "Craig Birch";
try{
oleDbDataAdapter1.Update(dt);
Console.WriteLine("Update successful.");
}
catch(Exception ex){
Console.WriteLine("Update unsuccessful.");
Console.WriteLine(ex.Message);
}


Minimalist code to be sure. All I did was replace one
string value with another and try to update. I get the
following error:

Concurrency violation: the UpdateCommand affected 0
records


Any ideas?
-----Original Message-----
I am having difficulty performing updates and deletions
on an Access database using the Update() method of the
OleDBDataAdapter. I can insert rows without a problem,
but I get a concurrency exception when trying to update
or delete.

I am quite sure that no concurrency conflicts actually
exist. Is there a reason why the data adapter I am
using
may have a different row version that the database that
it got its data from?

I can work around the problem by removing the
concurrency
check from the update command, but would rather not
remove checking all together, for obvious reasons.

Has anybody experienced a similar problem with MS Access?

Thank you for any help.

P.S. The exact same code works flawlessly with SQL
Server, but I can't use it in this particular situation.
.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top