3

I've set up a very simple example with LINQ-TO-SQL in WPF.

I can get an object (pageItem) out like this and I can change the property and when I call SubmitChanges() it gives me no error but it doesn't save the change.

MainDataContext db = new MainDataContext();
var pageItem = (from p in db.PageItems
              where p.Id == 1
              select p).SingleOrDefault();

pageItem.Title = "changed";
db.SubmitChanges();

What could be causing SubmitChanges not to submit the changes?

MORE INFO:

This doesn't work either, even the db.ExecuteCommand doesn't work, and strangely when debugging F11 doesn't step into SubmitChanges() or ExecuteCommand(), why can't I step in those?

using (var db = new MainDataContext())
{
    var pageItem = (from p in db.PageItems
                    where p.Id == 1
                    select p).SingleOrDefault();

    pageItem.Title = "changed";
    db.SubmitChanges();

    db.ExecuteCommand("INSERT INTO PageItems (Title) VALUES ('this is the title')");


    if (pageItem != null)
        MainContent.Children.Add(new QuickForm(pageItem));

}

more info:

The db.Log = Console.Out gives me this:

SELECT [t0].[Id], [t0].[IdCode], [t0].[Title], [t0].[Description], [t0].[DisplayOrder]
FROM [dbo].[PageItems] AS [t0]
WHERE [t0].[Id] = @p0
'TestPageManager23434.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\PresentationFramework.resources\3.0.0.0_de_31bf3856ad364e35\PresentationFramework.resources.dll'
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

INSERT INTO PageItems (Title) VALUES ('this is the title')
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

The thread 0x1190 has exited with code 0 (0x0).

ANSWER

The solution was three-fold:

  • I was changing a different database than I was looking at in visual studio, solution:

    var db = new MainDataContext(@"C:\Users\TestUser\Documents\Visual Studio 2008\Projects\TestPageManager23434\TestPageManager23434\Data\Main.mdf"))

  • that made Update work but not SubmitChanges(), solution was to set the primary key.

  • it still wasn't showing all the chagnes, problem was I had a number of "show data" windows open which weren't being updated

Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047

4 Answers4

6

This can happen if you don't have a primary key defined on the tables in SQL Server

ChrisCa
  • 10,876
  • 22
  • 81
  • 118
  • 1
    This bit me once - I'd submit changes, never get an exception, and it would go on its merry way, but never actually make it into the DB. All because I didn't specify, in the table, what my primary key was. – Matt Oct 16 '09 at 16:45
2

Are you using SQL Expression mdf file?

There's an article about how this might cause you to get a copy of the file and not the original, causing the symptoms you're describing.

FTA:

I think the project system or server explorer wizard offers to 'copy' your mdf into your project directory. Maybe you are operating on a copy of the database and viewing the other in server explorer.

Joseph
  • 25,330
  • 8
  • 76
  • 125
2

For some reason the context may not be tracking changes. Try wiring up your db.Log to a writer and inspect what LINQ->SQL is doing when you call SubmitChanges().. 

db.Log = Console.Out;

Then you can watch your output window running in debug and see what is going on.

Quintin Robinson
  • 81,193
  • 14
  • 123
  • 132
0

I had the same problem in which the record in the Database I see in my project was not modified by the SubmitChanges method.

After so many trial and researches I found out that the system put another version of the Database Northwnd.mdf in the project's root directory \Bin\Debug\Northwnd.mdf. That is where the changes, perfectly, occurred.

Diosney
  • 10,520
  • 15
  • 66
  • 111
Adam
  • 1