Concurrency violation trying to update a table in SQL Server

  • Thread starter Thread starter Boris Zakharin
  • Start date Start date
B

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
 
Hi Boris,

You should really check the code of that adapter - what exactly is its
CommandText.
 
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.
 
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 said:
Name is a reserved word, wrap it in square brackets [Name]

Boris Zakharin said:
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.
Click to expand...
Click to expand...
 
Back
Top