LINQ toSQL SubmitChanges doesn't work

I am tryint to write a column of data from one table to a currently empty column in another table.
The first table contains one row for every food with columns for all the nutrients food the foods.  The second table has one row for each nutrient in each food.

This part works (at least it appears to in debugger)
My sub UpdateABBREVButton_Click  loops though my ABBREV table and selects the table's primary index field (NBD_No) and the Vit_D field which is currently null in every row.  
Then for each of those rows it looks for a row in the NUT_DATA table that has both the same index (again NBD_No)  and a Nutr_No value = '324'.  It then sets the ABBREV Vit_D = Nutr_Val for that row.

db.SubmitChanges() doesn't work.

I've attached an image of visual studio in debug showing the values going into the food

I'm a newbie to LINQ to SQL and have been chasing this for two days.  Any help would be appreciated.
JIm

Public Partial Class ADDVit_DToABBREVForm
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
    End Sub
 
 
    Private Sub UpdateABBREVButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateABBREVButton.Click
        Dim db = New NutriCentsDataContext()
        Dim foodrows = From f In db.ABBREVs _
                        Select New With {f.NDB_No, f.Vit_D}
 
        For Each food In foodrows
            Dim NutriData = From d In db.NUT_DATAs _
                            Where d.NUTR_DEF.Nutr_No = "324" And d.NDB_No = food.NDB_No _
                            Select d.NDB_No, d.Nutr_Val
 
            For Each nutr In NutriData
                food.Vit_D = nutr.Nutr_Val 
            Next
        Next
        db.SubmitChanges()
 
    End Sub
End Class

Open in new window

Untitled-1.gif
LVL 1
foulkrjAsked:
Who is Participating?
 
aibusinesssolutionsCommented:
The problem is you are using SELECT NEW from db.ABBREVs, so it is creating a new anonymous type instead of returning the full ABBREV object which you can update.

Just remove the select new and try it out.


 Dim db = New NutriCentsDataContext()
        Dim foodrows = From f In db.ABBREVs
 
 
        For Each food In foodrows
            Dim NutriData = From d In db.NUT_DATAs _
                            Where d.NUTR_DEF.Nutr_No = "324" And d.NDB_No = food.NDB_No _
                            Select d.NDB_No, d.Nutr_Val
 
            For Each nutr In NutriData
                food.Vit_D = nutr.Nutr_Val 
            Next
        Next
        db.SubmitChanges()

Open in new window

0
 
Bob LearnedCommented:
That is usually a problem with the fact that there are:  (1) no changes to write, or (2) you are writing to one place, and checking another.

I would be curious what the change set looks like?

How to: Display a ChangeSet (LINQ to SQL)
http://msdn.microsoft.com/en-us/library/bb882650.aspx

You can view changes tracked by a DataContext by using GetChangeSet.
 
Example
The following example retrieves customers whose city is London, changes the city to Paris, and submits the changes back to the database.
 
Visual Basic
Dim db As New Northwnd("c:\northwnd.mdf")
 
Dim custQuery = _
    From cust In db.Customers _
    Where (cust.City = "London") _
    Select cust
 
For Each custObj As Customer In custQuery
    Console.WriteLine("CustomerID: {0}", custObj.CustomerID)
    Console.WriteLine(vbTab & "Original value: {0}", custObj.City)
    custObj.City = "Paris"
    Console.WriteLine(vbTab & "Updated value: {0}", custObj.City)
Next
 
Dim cs As ChangeSet = db.GetChangeSet()
Console.Write("Total changes: {0}", cs)
' Freeze the console window.
Console.ReadLine()
 
db.SubmitChanges()

Open in new window

0
 
foulkrjAuthor Commented:
Right on the money.  Thanks for helping an asp.net newbie
Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.