Re: Optimistic Concurrency on UpdateCommand...
- From: "Zack" <Zack@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 9 Oct 2005 11:27:02 -0700
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.
"Sahil Malik [MVP]" wrote:
> 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
> ----------------------------------------------------------------------------
>
> "Zack" <Zack@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:0134B50C-E89D-42BF-ABDC-B569F47C42AB@xxxxxxxxxxxxxxxx
> > 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.
>
>
>
.
- References:
- Re: Optimistic Concurrency on UpdateCommand...
- From: Sahil Malik [MVP]
- Re: Optimistic Concurrency on UpdateCommand...
- Prev by Date: Re: how to get a column value of the last row in dataset
- Next by Date: Re: I can't get 5 line web.config to work because it has connectio
- Previous by thread: Re: Optimistic Concurrency on UpdateCommand...
- Next by thread: Re: Errors encountered after generating a dataset
- Index(es):
Relevant Pages
|
