Link to home
Start Free TrialLog in
Avatar of foulkrj
foulkrj

asked on

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
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

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

ASKER CERTIFIED SOLUTION
Avatar of aibusinesssolutions
aibusinesssolutions
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of foulkrj
foulkrj

ASKER

Right on the money.  Thanks for helping an asp.net newbie
Jim