SubmitChanges() Method

Question

 

Dear NG,

 

I'm using the following code, to change the Name and ModifiedDate fields...

 

After calling the first SubmitChanges(), the Database is updated with correct Name and ModifiedDate values. But, after calling the second SubmitChanges() Methode my db (DataContext) is firing an exception "Row not found or changed".

 

Can anyone help me please? Is it not possible to update the same record two times?

Best,

Ozgur aytekin

 

Code Snippet

DataClasses1DataContext db = new DataClasses1DataContext();

IQueryable<ProductCategory> productCategories = db.ProductCategories;

ProductCategory pc = productCategories.Single(p => p.ProductCategoryID == 1);

pc.Name = "Test1";

pc.ModifiedDate = DateTime.Now;

db.SubmitChanges();

 

pc = productCategories.Single(p => p.ProductCategoryID == 1);

pc.Name = "Test2";

pc.ModifiedDate = DateTime.Now;

db.SubmitChanges();

 

 

 

System.Data.Linq.ChangeConflictException was unhandled
  Message="Row not found or changed."
  Source="System.Data.Linq"
  StackTrace:
       at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges()
       at WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\oaytekin\Desktop\Data\WorkingFolder\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs:line 33
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.ButtonBase.OnKeyUp(KeyEventArgs kevent)
       at System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
       at System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
       at System.Windows.Forms.Control.WmKeyChar(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at WindowsFormsApplication1.Program.Main() in C:\Documents and Settings\oaytekin\Desktop\Data\WorkingFolder\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

 

 

Answer 1

Sounds like your having problems with the optimiste concurrency.

 

I've just tried this post and it worked OK.

Code Snippet

DataClasses1DataContext db = new DataClasses1DataContext();

db.Log = Console.Out;

Order or = db.Orders.Single(o => o.OrderID == 10248);

or.ShipName = "Test";

or.ShipCountry = new Random().Next(100).ToString();

db.SubmitChanges();

 

or = db.Orders.Single(o => o.OrderID == 10248);

or.ShipName = "Test2";

or.ShipCountry = new Random().Next(100).ToString();

db.SubmitChanges();

 

 

So its something to do with it being a date.   From this post (http://www.damieng.com/blog/archive/2007/05/16/linq-to-sql-details-issues-and-patterns.aspx) it sounds like you are getting these errors because "If the data range/precision of the .NET type can't exactly hold the value in the SQL table (e.g. DateTime"

 

Does modified date hold DateTime or just the Date.  If its just the date then this is why you might be having problems as its checking to find the row to updated based on the time aswell.  When it calls the update, it includes all the columns in the WHERE clause to make sure nothing has been updated, it might be trying to match too much information and the database doesn't hold it extractly, hence why its causing the error.

 

Also, you might want to check that the DateTime is in the same format as what is being stored in the Database.

10/08/2007 != 08/10/2007.

 

Does this happen if you remove the update to DateTime?

 

If this isn't it, could you provide us with the query being executed on the server and the table schema?

 

Hope this helps.

 

Ben

Answer 2

Dear Ben,

 

Thank you for your answer...

 

I'm using the AdventureWorks Database in my samples...

 

In the definition of ProductCategory Table ModifiedDate is a DateTime field.

 

Now... I've tried to get some debug information about this case... Here is some information about my problem... The generated update command looks checking wrong modifieddate...

 

After updating the record with "Test1" I'm getting ModifiedDate 2007-08-10 21:31:27.317.

 

And it's checking the datetime value 2007-08-10 21:31:20:477. My question is, why this datetime value is different?

 

Can you check the sample code in my first post with AdventureWorks database (ProductCategory Table) please? I hope you can reproduce the error.

 

Thank you again for your help.

Ozgur Aytekin

 

 

DataClasses1DataContext db = new DataClasses1DataContext();
IQueryable<ProductCategory> productCategories = db.ProductCategories;
ProductCategory pc = productCategories.Single(p => p.ProductCategoryID == 1);

ProductCategoryID Name   rowguid                               ModifiedDate
1                 Test1  CFBDA25C-DF71-47A7-B81B-64EE161AA37C  2007-08-10 21:24:36.853
-----------------------------------------------------------------------------------------------

 

pc.Name = "Test1";
pc.ModifiedDate = DateTime.Now;
db.SubmitChanges();

ProductCategoryID Name   rowguid                               ModifiedDate
1                 Test1  CFBDA25C-DF71-47A7-B81B-64EE161AA37C  2007-08-10 21:31:27.317
-----------------------------------------------------------------------------------------------

 

productCategories = db.ProductCategories;
pc = productCategories.Single(p => p.ProductCategoryID == 1);
pc.Name = "Test2";
pc.ModifiedDate = DateTime.Now;
db.SubmitChanges();

 

exec sp_executesql N'UPDATE [Production].[ProductCategory]
SET [Name] = @p4, [ModifiedDate] = @p5
WHERE ([ProductCategoryID] = @p0)
  AND ([Name] = @p1) AND ([rowguid] = @p2)
  AND ([ModifiedDate] = @p3)',
  N'@p0 int,
    @p1 nvarchar(5),
    @p2 uniqueidentifier,
    @p3 datetime,
    @p4 nvarchar(5),
    @p5 datetime',
    @p0=1,
    @p1=N'Test1',
    @p2='CFBDA25C-DF71-47A7-B81B-64EE161AA37C',
    @p3=''2007-08-10 21:31:20:477'',
    @p4=N'Test2',
    @p5=''2007-08-10 21:37:39:220''

 

System.Data.Linq.ChangeConflictException was unhandled
  Message="Row not found or changed."
  Source="System.Data.Linq"
  StackTrace:
       at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges()
       at WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\oaytekin\Desktop\Data\WorkingFolder\WindowsFormsApplication1\WindowsFormsApplication1\Form1.cs:line 33
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.ButtonBase.OnKeyUp(KeyEventArgs kevent)
       at System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
       at System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
       at System.Windows.Forms.Control.WmKeyChar(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at WindowsFormsApplication1.Program.Main() in C:\Documents and Settings\oaytekin\Desktop\Data\WorkingFolder\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Answer 3

Had to install AdventureWorks, installing Northwind is a lot easier.....

 

I tried your code and had SQL Trace running at the same time. I can confirm this is a problem.

 

These two queries are being executed:

Code Snippet

 

exec sp_executesql N'UPDATE [Production].[ProductCategory]
SET [ModifiedDate] = @p4
WHERE ([ProductCategoryID] = @p0) AND ([Name] = @p1) AND ([rowguid] = @p2) AND ([ModifiedDate] = @p3)',N'@p0 int,@p1 nvarchar(5),@p2 uniqueidentifier,@p3 datetime,@p4
datetime',@p0=1,@p1=N'Test1',@p2='CFBDA25C-DF71-47A7-B81B-64EE161AA37C',@p3=''2007-08-10 21:37:40:550'',@p4=''2007-08-10 21:39:23:640''

 

 

Code Snippet

exec sp_executesql N'UPDATE [Production].[ProductCategory]
SET [Name] = @p4, [ModifiedDate] = @p5
WHERE ([ProductCategoryID] = @p0) AND ([Name] = @p1) AND ([rowguid] = @p2) AND ([ModifiedDate] = @p3)',N'@p0 int,@p1 nvarchar(5),@p2 uniqueidentifier,@p3 datetime,@p4
nvarchar(5),@p5 datetime',@p0=1,@p1=N'Test1',@p2='CFBDA25C-DF71-47A7-B81B-64EE161AA37C',@p3=''2007-08-10 21:39:23:640'',@p4=N'Test2',@p5=''2007-08-10 21:39:23:670''

 

 

The time according to the query should be set to: 2007-08-10 21:39:23:640

 

Upon querying the table, the time is infact 2007-08-10 21:39:23.667.  SQL Server has a very short delay which is causing the problem.

 

If we run this code after the first update:

Code Snippet

string dmtfDateTime =

ManagementDateTimeConverter.ToDmtfDateTime(pc.ModifiedDate);

Console.WriteLine(dmtfDateTime);

 

 

Then "20070810213923.640369+060" is printed out. So it looks like its SQL Server having a very very short delay on the update which is causing eveything to become out of sync.

 

A solution is to call Refresh on the table after the first submitchanges() which will get the actual time inserted.

Code Snippet

 

db.Refresh(RefreshMode.OverwriteCurrentValues, db.ProductCategories);

 

 

Sorry I can't be of more help, hopefully someone on the team will be able to provide some insight.

 

Ben

 

Answer 4

I found the error...

 

After setting the Auto-sync property to Always for ModifiedDate field I don't get the exception...

 

But... I found an error on the designer (Properties Window)... When I'm changing the Auto-sync property it's not sync every time the code...

 

Answer 5

Still i am facing...Same Problem...any One Please Help Me.....

---

Chandan Sharma

Answer 6

I thing if you are Update perticular Records....

you should follow this way.................................

 

 

public bool UpdateMember(Member

 

DataClasses1DataContext db = new DataClasses1DataContext

 

true

 

RefreshMode

 

return true

;

}



I hope ..It will Help....

.KeepCurrentValues, objMember); --------------(This is Refresh Records and Update)

 

db.SubmitChanges();

 

 

);

db.Refresh(

 

();

db.Members.Attach(objMember,

 

objMember)

 

{

 

---

Chandan Sharma