467,888 Members | 1,399 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,888 developers. It's quick & easy.

MS Access concurrency exception

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.
Jul 21 '05 #1
  • viewed: 2028
Share:
4 Replies
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 usingmay have a different row version that the database that
it got its data from?

I can work around the problem by removing the concurrencycheck 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.
.

Jul 21 '05 #2
What's your DataAdapter SelectCommand and UpdateCommand?

"Charlie Williams" <cw***********@hotmail.spamfree> wrote in message
news:0e****************************@phx.gbl...
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.
.

Jul 21 '05 #3
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?

"Charlie Williams" <cw***********@hotmail.spamfree> wrote in messagenews:0e****************************@phx.gbl...
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. >.
>

.

Jul 21 '05 #4
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.Comma ndText);

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" <cw***********@hotmail.spamfree> wrote in message
news:03****************************@phx.gbl...
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?

"Charlie Williams" <cw***********@hotmail.spamfree>

wrote in message
news:0e****************************@phx.gbl...
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. >.
>

.

Jul 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by David Rasmussen | last post: by
4 posts views Thread by Robert Schuldenfrei | last post: by
4 posts views Thread by Charlie Williams | last post: by
5 posts views Thread by Vayse | last post: by
4 posts views Thread by Bob | last post: by
1 post views Thread by Rotsey | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.