Concurrency violation trying to update a table in SQL Server

I have an intermittent error where I get the following exception: "Error:
Concurrency violation: the UpdateCommand affected 0 records."

Here are the steps I follow:
Create SQLConnection
Set connection string of the connection.
Open connection
Create SQL Data Adapter with the connection and a simple select statement
including an indexed column
Create SqlCommandBuilder on this adapter
Fill DataTable with the Adapter
Loop through each datarow and based on contents of a field, possibly update
another field in the same row
Call If Not tbl.GetChanges Is Nothing Then adapter.Update(tbl)

This last step is where the error occurs.

I am the only user of the system so there are no concurrency issues. When I
tried to run profiler while executing, the problem went away and I no longer
seem to be able to reproduce this problem on that machine.

Any help would be appreciated.

Thanks in advance,
Boris Zakharin

Sahil
Thu Mar 30 20:12:48 CST 2006

The where clause on your generated command is not modifying any rows, even
when there were modified rows in your datatable.

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


"Boris Zakharin" <bzakharin@primeassociates.com> wrote in message
news:OZbJ7iEVGHA.6112@TK2MSFTNGP10.phx.gbl...
>I have an intermittent error where I get the following exception: "Error:
> Concurrency violation: the UpdateCommand affected 0 records."
>
> Here are the steps I follow:
> Create SQLConnection
> Set connection string of the connection.
> Open connection
> Create SQL Data Adapter with the connection and a simple select statement
> including an indexed column
> Create SqlCommandBuilder on this adapter
> Fill DataTable with the Adapter
> Loop through each datarow and based on contents of a field, possibly
> update
> another field in the same row
> Call If Not tbl.GetChanges Is Nothing Then adapter.Update(tbl)
>
> This last step is where the error occurs.
>
> I am the only user of the system so there are no concurrency issues. When
> I
> tried to run profiler while executing, the problem went away and I no
> longer
> seem to be able to reproduce this problem on that machine.
>
> Any help would be appreciated.
>
> Thanks in advance,
> Boris Zakharin
>
>

Miha
Fri Mar 31 00:29:01 CST 2006

Hi Boris,

You should really check the code of that adapter - what exactly is its
CommandText.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Boris Zakharin" <bzakharin@primeassociates.com> wrote in message
news:OZbJ7iEVGHA.6112@TK2MSFTNGP10.phx.gbl...
>I have an intermittent error where I get the following exception: "Error:
> Concurrency violation: the UpdateCommand affected 0 records."
>
> Here are the steps I follow:
> Create SQLConnection
> Set connection string of the connection.
> Open connection
> Create SQL Data Adapter with the connection and a simple select statement
> including an indexed column
> Create SqlCommandBuilder on this adapter
> Fill DataTable with the Adapter
> Loop through each datarow and based on contents of a field, possibly
> update
> another field in the same row
> Call If Not tbl.GetChanges Is Nothing Then adapter.Update(tbl)
>
> This last step is where the error occurs.
>
> I am the only user of the system so there are no concurrency issues. When
> I
> tried to run profiler while executing, the problem went away and I no
> longer
> seem to be able to reproduce this problem on that machine.
>
> Any help would be appreciated.
>
> Thanks in advance,
> Boris Zakharin
>
>

Boris
Fri Mar 31 08:58:12 CST 2006

I am generating the select statement. In this case it happens to be:
"SELECT Name, QueryText, Params, lastoper FROM Query" where Name has the
clustered index.
The connection string is:
"integrated security=SSPI;persist security info=False;initial
catalog=PLEGL;Data Source=(LOCAL)"

The update statement generated is:
"UPDATE Query SET Name = @p1 , QueryText = @p2 , Params = @p3 , lastoper =
@p4 WHERE ( (Name = @p5) AND ((@p6 = 1 AND QueryText IS NULL) OR (QueryText
= @p7)) AND ((@p8 = 1 AND Params IS NULL) OR (Params = @p9)) AND ((@p10 = 1
AND lastoper IS NULL) OR (lastoper = @p11)) )"


but of course this is on my development system where the error does not
occur anyway.

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23NBXoxIVGHA.1728@TK2MSFTNGP11.phx.gbl...
> Hi Boris,
>
> You should really check the code of that adapter - what exactly is its
> CommandText.
>

Jim
Fri Mar 31 10:04:04 CST 2006

Name is a reserved word, wrap it in square brackets [Name]

"Boris Zakharin" <bzakharin@primeassociates.com> wrote in message
news:ucmBDPNVGHA.5900@tk2msftngp13.phx.gbl...
>I am generating the select statement. In this case it happens to be:
> "SELECT Name, QueryText, Params, lastoper FROM Query" where Name has the
> clustered index.
> The connection string is:
> "integrated security=SSPI;persist security info=False;initial
> catalog=PLEGL;Data Source=(LOCAL)"
>
> The update statement generated is:
> "UPDATE Query SET Name = @p1 , QueryText = @p2 , Params = @p3 , lastoper =
> @p4 WHERE ( (Name = @p5) AND ((@p6 = 1 AND QueryText IS NULL) OR
> (QueryText
> = @p7)) AND ((@p8 = 1 AND Params IS NULL) OR (Params = @p9)) AND ((@p10 =
> 1
> AND lastoper IS NULL) OR (lastoper = @p11)) )"
>
>
> but of course this is on my development system where the error does not
> occur anyway.
>
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:%23NBXoxIVGHA.1728@TK2MSFTNGP11.phx.gbl...
>> Hi Boris,
>>
>> You should really check the code of that adapter - what exactly is its
>> CommandText.
>>
>
>

Boris
Fri Mar 31 13:25:44 CST 2006

Here's another example without the reserved word:
"SELECT RequestId, WhereString, Params, lastoper FROM RequestQuery "
"UPDATE RequestQuery SET RequestId = @p1 , WhereString = @p2 , Params = @p3
, lastoper = @p4 WHERE ( (RequestId = @p5) AND ((@p6 = 1 AND WhereString IS
NULL) OR (WhereString = @p7)) AND ((@p8 = 1 AND Params IS NULL) OR (Params =
@p9)) AND ((@p10 = 1 AND lastoper IS NULL) OR (lastoper = @p11)) )"

"Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
news:OoWN3yNVGHA.5044@TK2MSFTNGP09.phx.gbl...
> Name is a reserved word, wrap it in square brackets [Name]
>
> "Boris Zakharin" <bzakharin@primeassociates.com> wrote in message
> news:ucmBDPNVGHA.5900@tk2msftngp13.phx.gbl...
> >I am generating the select statement. In this case it happens to be:
> > "SELECT Name, QueryText, Params, lastoper FROM Query" where Name has the
> > clustered index.
> > The connection string is:
> > "integrated security=SSPI;persist security info=False;initial
> > catalog=PLEGL;Data Source=(LOCAL)"
> >
> > The update statement generated is:
> > "UPDATE Query SET Name = @p1 , QueryText = @p2 , Params = @p3 , lastoper
=
> > @p4 WHERE ( (Name = @p5) AND ((@p6 = 1 AND QueryText IS NULL) OR
> > (QueryText
> > = @p7)) AND ((@p8 = 1 AND Params IS NULL) OR (Params = @p9)) AND ((@p10
=
> > 1
> > AND lastoper IS NULL) OR (lastoper = @p11)) )"
> >
> >
> > but of course this is on my development system where the error does not
> > occur anyway.
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:%23NBXoxIVGHA.1728@TK2MSFTNGP11.phx.gbl...
> >> Hi Boris,
> >>
> >> You should really check the code of that adapter - what exactly is its
> >> CommandText.
> >>
> >
> >
>
>