?
Solved

LINQ toSQL SubmitChanges doesn't work

Posted on 2009-02-13
3
Medium Priority
?
583 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:foulkrj
3 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23640364
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
 
LVL 15

Accepted Solution

by:
aibusinesssolutions earned 2000 total points
ID: 23642545
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
 
LVL 1

Author Closing Comment

by:foulkrj
ID: 31546684
Right on the money.  Thanks for helping an asp.net newbie
Jim
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question