SubmitChanges not updating, but inserts new record. LINQ to SQL

Question

I am having difficulties UPDATING the databes via LINQ to SQL, inserting a new record works fine.

The code correctly inserts a new row and adds a primary key, the issue I am having is when I go to update (chnage a value that is already in the database) that same row the database is not updating, it is the else part of the code that does not work correctly. This is strange b/c the DB is properly connected and functioning through the fact that the DataContext inserts a new row with no issues. Checking the database confirms this.

This is the code,

using System;
using System.Collections.Generic;
using System.Linq;

using Cost = Invoices.Tenant_Cost_TBL;

namespace Invoices
{   
    class CollectionGridEvents
    {
        static string conn = Settings.Default.Invoice_DbConnectionString;

        public static void CostDataGridCellEditing(DataGridRowEditEndingEventArgs e)
       {
        using (DatabaseDataContext DataContext = new DatabaseDataContext(conn))
           {
                var sDselectedRow = e.Row.Item as Cost;                
                if (sDselectedRow == null) return;
                if (sDselectedRow.ID == 0)
                {
                    sDselectedRow.ID = DateTime.UtcNow.Ticks;
                    DataContext.Tenant_Cost_TBLs.InsertOnSubmit(sDselectedRow);
                }
                else
                {
                    // these two lines are just for debuging
                    long lineToUpdateID = 636154619329526649; // this is the line to be updated primary key
                    long id = sDselectedRow.ID;     // this is to check the primary key on selected line is same

                    // these 3 lines are to ensure I am entering actual data into the DB
                    int? amount = sDselectedRow.Cost_Amount;
                    string name = sDselectedRow.Cost_Name;
                    int? quantity = sDselectedRow.Cost_Quantity;

                    sDselectedRow.Cost_Amount = amount;
                    sDselectedRow.Cost_Name = name;
                    sDselectedRow.Cost_Quantity = quantity;
                }
                try
                {                   
                    DataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    Alert.Error("Did not save", "Error", ex);
                }
            }            
        }
    } 
}

And I am calling the method from this,

private void CostDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
    {
        CollectionGridEvents.CostDataGridCellEditing(e);
    }

The lineToUpdateID is copied dirrectly from the database and is just there to check against the currently selected rows primary key is the same, so I know I am trying to update the same row.

I have looked through as many of the same type of issues here on SO , such as this one Linq-to-Sql SubmitChanges not updating fields … why?. But still no closer to finding out what is going wrong.

Any ideas would be much appreciated.

EDIT: Cost is just short hand of this using Cost = Invoices.Tenant_Cost_TBL;

CodingYoshi · Accepted Answer · 2016-11-23T03:39:05.450

1

You cannot do that. You need to get the record out of the database and then update that record. Then save it back. Like this:

else
{
    // first get it
    var query =
        from ord in DataContext.Tenant_Cost_TBLs
        where ord.lineToUpdateID = 636154619329526649
        select ord;

    // then update it
    // Most likely you will have one record here
    foreach (Tenant_Cost_TBLs ord in query)
    {
        ord.Cost_Amount = sDselectedRow.Cost_Amount;
        // ... and the rest
        // Insert any additional changes to column values.
    }

}
try
{
    DataContext.SubmitChanges();
}
catch (Exception ex)
{
    Alert.Error("Did not save", "Error", ex);
}

Here is an example you can follow.

Or you can use a direct query if you do not want to select first.

DataContext.ExecuteCommand("update Tenant_Cost_TBLs set Cost_Amount =0 where ...", null);

edited Nov 23 '16 at 03:39

answered Nov 23 '16 at 02:33

CodingYoshi

25,467
4
62
64

Sorry, I thought it was clear that the data `Cost` was part of the record that I was getting from the DB, through the fact that inserting the row actually works. Or am I misunderstanding you? Please see updated question for edit. – KyloRen Nov 23 '16 at 03:00
Linq to SQL does not support update like that. You need to get that record from the database firstly, then update that record and then save it. Right now you are not getting it out of the database when you create your DatabaseDataContext. – CodingYoshi Nov 23 '16 at 03:04
Ok I see what you are saying, but I don't understand why it is correctly inserting the record if it is not connected to the database? – KyloRen Nov 23 '16 at 03:22
Think about it like this, your DataContext is an object in memory you just created. That object does not have your item "Cost" so you need to get it from the database and then it will become part of the DataContext object. Then you update it and save it. When you got it out of the database last time, that DataContext was a different object If you just add it to the DataContext, it treats as a newly added object. Plus in your else block you were not really doing nothing except assigning a to b then b to a...you know what I mean – CodingYoshi Nov 23 '16 at 03:27
OK, this is not working, for one `Ord.ID` in `var query = from ord in DataContext.Tenant_Cost_TBLs where ord.ID = 636154619329526649 select ord;` is seen as a `bool` when it is a `long` in the database. Something funky is happening. Why is something so simple when not using a `using` statement so difficult? – KyloRen Nov 23 '16 at 09:19
Nevermind, I just neede to throw a `first()` in the LINQ statement and now it is working. Thanks. – KyloRen Nov 23 '16 at 09:35

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

Your object (Cost) is not attached to DB context. You should attach it then save changes. Check solution here

edited May 23 '17 at 10:29

Community

1
1

answered Nov 23 '16 at 02:40

Maksim Eroshkin

31
1
4

Sorry, I thought it was clear that the data `Cost` was part of the record that I was getting from the DB, through the fact that inserting the row actually works. Or am I misunderstanding you? Please see updated question for edit. – KyloRen Nov 23 '16 at 03:00
You are trying to save changes to just created DataContext of the object obtained from another context (that doesn't exist at the moment). So you should attach the object to DataContext before you save changes. – Maksim Eroshkin Nov 23 '16 at 03:34
Thanks for the help, I up voted for the help. It helped me a lot. – KyloRen Nov 23 '16 at 11:42

SubmitChanges not updating, but inserts new record. LINQ to SQL

2 Answers2