Concurrency violation - concurrency_error_watch.xls (0/1)

[nick]

I'm having trouble updating from a datagrid. It's says "Concurrency
violation: the UpdateCommand affected 0 records", though I can't see
how it's related to "concurrency". I can insert and delete with no
problem. Here's the code that's causing the problem:

public void UpdateDataSource(Dancers.allbookings ChangedRows)
{
try
{
if ((ChangedRows != null))
{
this.oleDbConnection1.Open();
oleDbDataAdapter1.Update(ChangedRows);
}
}
catch (System.Exception updateException)
{
throw updateException;
}
finally
{
this.oleDbConnection1.Close();
}

}

An error occurs trying to execute the line:

oleDbDataAdapter1.Update(ChangedRows);

I found nothing wrong when trying to track from the watch list the
data from the row to be updated, as below (can be viewed a lot clearer
from the attached excel file):

- ChangedRows.tbprivate[0]
{Dancers.allbookings.tbprivateRow}
Dancers.allbookings.tbprivateRow
+ System.Data.DataRow {Dancers.allbookings.tbprivateRow}
System.Data.DataRow
cancelled FALSE bool
dancerid 6 int
finished TRUE bool
len 20 byte
pclub 30 short
pdancer 55 short
privateid 65 int
room A string
+ shift {8/2/2004} System.DateTime
start 20:20 string
+ tabletbprivate {Dancers.allbookings.tbprivateDataTable}
Dancers.allbookings.tbprivateDataTable
ChangedRows.Tables["tbprivate"].Rows[0].RowError
Concurrency violation: the UpdateCommand affected 0 records. string

[Cor]

Hi Nick,

When you do it this way,

oleDbDataAdapter1.Update(ChangedRows);

I asume that ChangedRows is the dataset.ChangedRows than the acceptchanges
is not done automaticly by the oleDbDataAdapter, the next time you do than
an update you get a concurrency errror.

Can it be that?

Cor

[nick]

On Tue, 10 Feb 2004 13:37:59 +0100, "Cor" <no*@non.com> wrote:

Hi Nick,

When you do it this way,

oleDbDataAdapter1.Update(ChangedRows);

I asume that ChangedRows is the dataset.ChangedRows than the acceptchanges
is not done automaticly by the oleDbDataAdapter, the next time you do than
an update you get a concurrency errror.

Can it be that?

Cor

OK here's the code sequece: When the update button is pressed,
"UpdateDataSet" is called:

public void UpdateDataSet()
{
Dancers.allbookings objDataSetChanges = new
Dancers.allbookings();

this.BindingContext[objallbookings,"tbprivate"].EndCurrentEdit();
objDataSetChanges =
((Dancers.allbookings)(objallbookings.GetChanges() ));

// Check to see if any changes have been made.
if ((objDataSetChanges != null))
{
try
{
this.UpdateDataSource(objDataSetChanges);
objallbookings.Merge(objDataSetChanges);
objallbookings.AcceptChanges();
}
catch (System.Exception eUpdate)
{
throw eUpdate;
}
}
}

which then calls "UpdateDataSource" from:

this.UpdateDataSource(objDataSetChanges);

"objDataSetChanges" is passed in as "ChangedRows" to
"UpdateDataSource". As mentioned before, it breaks down when executing
"oleDbDataAdapter1.Update(ChangedRows)" within "UpdateDataSource". I
suspect there's some data inconsistency but by looking at the
"ChangeRows" (which only have 1 row in this case) it all seems fine.

[Tian Min Huang]

Hello Nick,

Thanks for your post. As I understand, the problem you are facing is that
it throws DBConcurrencyException when calling DbDataAdapter.Update method.
By default, the Update method ends and raises a DBConcurrencyException
exception when the method encounters a row that fails to update. I think
more information is needed before moving forward:

1. What's the database, SQL Server, Microsoft Access, etc?

2. In the DataRow, does it contain the "System.Data.OleDb.OleDbType.DBDate"
type for mapping DateTime data? If so, based on my experience, there is a
known issue to cause such error and we need to use
System.DataOleDb.OleDbType.Date instead of
System.Data.OleDb.OleDbType.DBDate.

3. If the problem persists, is it possible for you to post a sample project
and database which is able to reproduce the problem? I will be glad to
check it on my side.

I look forward to hearing from you.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Wed, 11 Feb 2004 05:36:23 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hello Nick,

Thanks for your post. As I understand, the problem you are facing is that
it throws DBConcurrencyException when calling DbDataAdapter.Update method.
By default, the Update method ends and raises a DBConcurrencyException
exception when the method encounters a row that fails to update. I think
more information is needed before moving forward:

1. What's the database, SQL Server, Microsoft Access, etc?
It's MSAccess.

2. In the DataRow, does it contain the "System.Data.OleDb.OleDbType.DBDate"
type for mapping DateTime data? If so, based on my experience, there is a
known issue to cause such error and we need to use
System.DataOleDb.OleDbType.Date instead of
System.Data.OleDb.OleDbType.DBDate.

Here's the code to create the updatecommand for oleDbDataAdapter1:

updatesql = "UPDATE tbprivate SET dancerid = @dancerid, len = @len,
start = @start, pdancer = @pdancer, pclub = @pclub, room = @room,
finished = @finished, cancelled = @cancelled, shift = #" + GetShift()
+ "# WHERE privateid = @privateid";

where GetShift() will return a date like "2/8/2004".

oleDbDataAdapter1.UpdateCommand = new
OleDbCommand(updatesql,oleDbConnection1);
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p rivateid",
OleDbType.BigInt,4, "privateid");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@d ancerid",
OleDbType.BigInt,4, "dancerid");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@l en",
OleDbType.TinyInt,1, "len");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@r oom",
OleDbType.VarChar,1, "room");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@s tart",
OleDbType.VarChar,5, "start");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p club",
OleDbType.TinyInt,4, "pclub");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p dancer",
OleDbType.TinyInt,4, "pdancer");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@f inished",
OleDbType.Boolean,1, "finished");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@c ancelled",
OleDbType.Boolean,1, "cancelled");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@s hift",
OleDbType.Date,0, "shift");

[nick]

Also have you looked at the xls file? Is it useful?

On Wed, 11 Feb 2004 05:36:23 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hello Nick,

Thanks for your post. As I understand, the problem you are facing is that
it throws DBConcurrencyException when calling DbDataAdapter.Update method.
By default, the Update method ends and raises a DBConcurrencyException
exception when the method encounters a row that fails to update. I think
more information is needed before moving forward:

1. What's the database, SQL Server, Microsoft Access, etc?

2. In the DataRow, does it contain the "System.Data.OleDb.OleDbType.DBDate"
type for mapping DateTime data? If so, based on my experience, there is a
known issue to cause such error and we need to use
System.DataOleDb.OleDbType.Date instead of
System.Data.OleDb.OleDbType.DBDate.

3. If the problem persists, is it possible for you to post a sample project
and database which is able to reproduce the problem? I will be glad to
check it on my side.

I look forward to hearing from you.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[Cor]

Hi Nick,

What is the reason you do this,

this.UpdateDataSource(objDataSetChanges);
objallbookings.Merge(objDataSetChanges);
objallbookings.AcceptChanges();

If I see it good you try to merge to the dataset a set of rows which are
already completly in this dataset if the program works correct than that
should give an concurrency error..

I think it is better to do an update of the whole dataset. (I have done it a
while a little bit the same as you although without the merge, I am not
secure if all checkings are done correct with this method).

You do than also not need that acceptchanges, because the dataadapter does
it for you if everything went correct.

Cor

[nick]

On Wed, 11 Feb 2004 13:02:24 +0100, "Cor" <no*@non.com> wrote:

Hi Nick,

What is the reason you do this,

this.UpdateDataSource(objDataSetChanges);
objallbookings.Merge(objDataSetChanges);
objallbookings.AcceptChanges();

objallbookings is the existing dataset. UpdateDataSource updates the
underlying datasource (actually updating the database). The last 2
lines commits changes to the dataset. How else would you do it??

If I see it good you try to merge to the dataset a set of rows which are
already completly in this dataset if the program works correct than that
should give an concurrency error..
See above.

I think it is better to do an update of the whole dataset. (I have done it a
while a little bit the same as you although without the merge, I am not
secure if all checkings are done correct with this method).

I have been doing it this way ALL the time, only this particular grid
fails me. I am suspecting it's the data in the row(s) that's giving
the underlying database inconsistent update, but I can't figure out
which column, cause as you can see from the watch (excel file) all
data seems pretty normal.

Anyone?

[Cor]

Hi Nick,

I would first try what I did advice when than the concurrency error is not
gonne than it is something else.

:-)

Cor

I have been doing it this way ALL the time, only this particular grid
fails me. I am suspecting it's the data in the row(s) that's giving
the underlying database inconsistent update, but I can't figure out
which column, cause as you can see from the watch (excel file) all
data seems pretty normal.

[nick]

On Wed, 11 Feb 2004 14:34:17 +0100, "Cor" <no*@non.com> wrote:

Hi Nick,

I would first try what I did advice when than the concurrency error is not
gonne than it is something else.

:-)

Cor
Umm.. I don't quite understand what you're suggesting. Perhaps some
code might help?

I have been doing it this way ALL the time, only this particular grid
fails me. I am suspecting it's the data in the row(s) that's giving
the underlying database inconsistent update, but I can't figure out
which column, cause as you can see from the watch (excel file) all
data seems pretty normal.

[Cor]

To test
this.UpdateDataSource(objallbookings);
// objallbookings.Merge(objDataSetChanges);
// objallbookings.AcceptChanges();

[nick]

On Wed, 11 Feb 2004 15:08:01 +0100, "Cor" <no*@non.com> wrote:

To test
this.UpdateDataSource(objallbookings);
// objallbookings.Merge(objDataSetChanges);
// objallbookings.AcceptChanges();

Umm I thought it was pretty clear I mentioned it's "UpdateDataSource"
that's causing the problem?

[Tian Min Huang]

Hi Nick,

Thanks for your response. I reviewed your code snippet carefully, I noticed
that "shift" value is directly got from GetShft so that we should not add
@shift parameter to the UndateCommand. Please remove it and then check
whether or not the problem still exists.

I am standing by for your result.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Thu, 12 Feb 2004 08:30:14 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hi Nick,

Thanks for your response. I reviewed your code snippet carefully, I noticed
that "shift" value is directly got from GetShft so that we should not add
@shift parameter to the UndateCommand. Please remove it and then check
whether or not the problem still exists.

I am standing by for your result.
Exactly the same error.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[Tian Min Huang]

Hi Nick,

Since the problem persists, could you please post a sample project with
MDB file and tell me the detailed steps to reproduce the problem? That
will be most helpful for me to pinpoint the problem and resolution.

I am standing by for your response.

Have a nice day!

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Fri, 13 Feb 2004 07:43:24 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hi Nick,

Since the problem persists, could you please post a sample project with
MDB file and tell me the detailed steps to reproduce the problem? That
will be most helpful for me to pinpoint the problem and resolution.

Do I just email the project (slimmed down version) to you? It's about
1.5mb compressed, including the Access db.

[Tian Min Huang]

Hello Nick,

You can ZIP the files and email it to me directly. Thanks.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Fri, 13 Feb 2004 11:30:56 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hello Nick,

You can ZIP the files and email it to me directly. Thanks.

Did you get the email? I sent it to timhuang at microsoft dot com.

[Tian Min Huang]

Hi Nick,

Thanks for your project. I received it and am now performing research on
it. I will update you with my information.

Have a nice day!

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Mon, 16 Feb 2004 10:43:50 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hi Nick,

Thanks for your project. I received it and am now performing research on
it. I will update you with my information.

Thanks for your help. Waiting for the good news :)

[Tian Min Huang]

Hi Nick,

I reproduced the problem on my side. However, due to the complexity of this
issue, I will need more time to investigate. Thanks for your patience.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Wed, 18 Feb 2004 16:27:27 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hi Nick,

I reproduced the problem on my side. However, due to the complexity of this
issue, I will need more time to investigate. Thanks for your patience.

No worries. Thanks again for your help.

[nick]

On Wed, 18 Feb 2004 16:27:27 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hi Nick,

I reproduced the problem on my side. However, due to the complexity of this
issue, I will need more time to investigate. Thanks for your patience.

Don't mean to rush you but just like to know if you have any progress.

Cheers.

[Tian Min Huang]

Hi Nick,

This issue seems strange. To narrow down the problem, I built a simple
project of my own which works properly on updating the dataset. I am now
digging into your project to see what's going wrong.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[Tian Min Huang]

Hello Nick,

Thanks for your patience. After further research on this issue, I found out
that the problem was caused by the improper order of SQL parameters. Based
on my research, the OLE DB Provider handles parameters differently than
SQLClient which accepts named parameters. Please adding the parameters in
the correct order and see if it works on your side:

oleDbDataAdapter1.UpdateCommand.Parameters.Add("@d ancerid",
OleDbType.BigInt,4, "dancerid");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@l en", OleDbType.Integer,1,
"len");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@s tart",
OleDbType.VarChar,5, "start");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p dancer",
OleDbType.Integer,2, "pdancer");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p club",
OleDbType.Integer,2, "pclub");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@r oom",
OleDbType.VarChar,1, "room");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@f inished",
OleDbType.Boolean,1, "finished");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@c ancelled",
OleDbType.Boolean,1, "cancelled");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p aid",
OleDbType.Boolean,1, "paid");
oleDbDataAdapter1.UpdateCommand.Parameters.Add("@p rivateid",
OleDbType.BigInt,4, "privateid");

I am standing by for your result.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[nick]

On Thu, 26 Feb 2004 01:56:48 GMT, ti******@online.microsoft.com (Tian
Min Huang) wrote:

Hello Nick,

Thanks for your patience. After further research on this issue, I found out
that the problem was caused by the improper order of SQL parameters. Based
on my research, the OLE DB Provider handles parameters differently than
SQLClient which accepts named parameters. Please adding the parameters in
the correct order and see if it works on your side:

Damn didn't think of that possibility... Thanks a bunch!

Gotta read more into OleDbCommand.Parameters when I got time :)