|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Concurrency violation trying to update a table in SQL ServerConcurrency 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 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 __________________________________________________________ Show quote "Boris Zakharin" <bzakha***@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 > > Hi Boris,
You should really check the code of that adapter - what exactly is its CommandText. -- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Boris Zakharin" <bzakha***@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 > > 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. Show quote "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. > Name is a reserved word, wrap it in square brackets [Name]
Show quote "Boris Zakharin" <bzakha***@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. >> > > 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)) )" Show quote "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" <bzakha***@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. > >> > > > > > >  |
|||||||||||||||||||||||
Other interesting topics