Concurrency violation trying to update a table in SQL Server

[Boris Zakharin]

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 Malik [MVP C#]]

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
__________________________________________________________

 

[Miha Markic [MVP C#]]

Hi Boris,

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

 

[Boris Zakharin]

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.

 

[Jim Hughes]

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

 

[Boris Zakharin]

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)) )"

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

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.