Optimistic Concurrency on UpdateCommand...

[Guest]

Hey all,

I have a dataset generated from a SQL server table using Visual Studio 2005
beta2. All of the SQL commands are auto-generated. I just dragndrop the
table over and let it do the rest.

I then created a row in the dataset and the row was successfully sent to the
sql server when I called Update(<dataset>) from the auto-generated
datasettableadapter.

I then modified the a few columns of the row and called Update(<dataset>) on
the table adapter.

I then get a concurrency violation:
'System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand
affected 0 of the expected 1 records'

I am the only one on the system so I don't see how the sql server database
is changing between the addition of the row and the update of that row.

I looked around and read two threads on this board related to the problem.
I can successfully get rid of the concurrency exception by removing the
'Optimistic Concurrency' option in the configure dataAdapter screen.

However -- I would rather have the concurrency checking on...and I'd prefer
to the use the auto-generated commands instead of manually making em (speed
isn't too big of an issue). Should it be possible, is there a bug on the
optimistic concurrency, or am I just doing something wrong?

Thanks.

 

[Jim Hughes]

Does the table have a primary key? If not, add/define one.

 

[Sahil Malik [MVP]]

Zack,

What are the auto generated command texts?? Maybe there will be a clue in
there. You should be able to find the command texts in the properties of the
tableadapters.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Guest]

In response to Jim, I do have a PK defined on the table.

Regarding the actual updatecommand...I simplified my table a bit more so
that I could see what is happening. I have come up with some new
observations:

1) The fourth column in my table is a datetime, nullable field. When I add
a record to the database, this 4th column is initially set to null. On my
client device, I update that column with a non-null datetime and then try to
send the update to the server. That is when I receive my concurrency
violation. I ran the profiler to see the offending UpdateCommand text. The
part that is causing the trouble is in the WHERE statement regarding the 4th,
nullable column (Called BARCLOCK_OutTime):
(@IsNull_BARCLOCK_OutTime = 1 AND [BARCLOCK_OutTime] IS NULL)
OR
([BARCLOCK_OutTime] = @Original_BARCLOCK_OutTime))

The above is fine. However...when I look at the variables being passed into
the sql function, I see:
@IsNull_BARCLOCK_OutTime = NULL,
@Original_BARCLOCK_OutTime = NULL

The @IsNull_BARCLOCK_OutTime parameter is set to NULL but it should
1!!!(since the original OutTime was NULL) If I copy and paste the command
into query-analyzer and then change it to "IsNull_BARCLOCK_OutTime = 1" the
command works. So: somehow the parameter doesn't realize that the 4th column
used to be NULL, even though it clearly knows the Original_BARCLOCK_OutTime
was null?

[Note: I initially add the row to the client dataset with no null
values...and then retrieve that row and set the OutTime column to null, since
I can't pass a null value or DBNull.Value into the AddRow function of the
dataset. The client is indeed setting that 4th column to NULL, though: the
insert command properly sets the 4th column parameter to null when it is
inserted into the database.]

2) After realizing that having the 4th column (OutTime) set to NULL was
causing some problems...I changed the database table to not allow NULLs and
modified my code a little bit.

With the 4th column not allowing nulls, the optimistic concurrency code
works fine when I modify and update the row.

3) Next, I tried to re-allow NULLs on the 4th column, but not set the column
to null in my client code at all (just to try it and see what happens).

So, I added a row of the data with no NULLs, sent it to the server, and then
tried to modify the 4th column. It didn't work...and I got this new
exception:
"Process Error: System.InvalidCastException: InvalidCastException at
DateTime.System.IConvertible.ToInt32() at Convert.DefaultToType()..."
...."at SqlParameter.get_CoercedValue() at TdsParser.TdsExecuteRPC()..."

I couldn't get to the specific line through the stack trace...but it looks
like something is going wrong converting DateTime to Int when doing the
parameters...

This happen to anyone else? Can I have a nullable datetime column in my Sql
table and use the autogenerated optimistic concurrency?

Any ideas?
- Zack

p.s. I am doing all this with the CompactFramework on a emulated PocketPC
2003 device. It says that I am using Microsoft .net CF 2.0 and Sql Client
2.0.

The problem comes from one of the columns being nullable.