LINQ Statement Not Updating Database?

Question

I have the following code:

        Dim dbHousing As New dcHousingDataContext
    Dim pullresidents = From p In dbHousing.webResidents _
                Select p.dorm_building, p.dorm_room, p.occupantnum _
                Order By dorm_building, dorm_room
    Dim j as integer = 1
    Dim previous As String = ""
    For Each row In pullresidents
        If previous = "" Then
            Dim wResident As New webResident
            wResident.occupantnum = j
            dbHousing.SubmitChanges()
            j = j + 1
            previous = row.dorm_building & " " & row.dorm_room
        Else
            If previous = row.dorm_building & " " & row.dorm_room Then
                Dim wResident As New webResident
                wResident.occupantnum = j
                dbHousing.SubmitChanges()
                j = j + 1
            Else
                Dim wResident As New webResident
                j = 1
                wResident.occupantnum = j
                dbHousing.SubmitChanges()
                j = j + 1
                previous = row.dorm_building & " " & row.dorm_room
            End If
        End If

    Next

When I run it, looking at the debugger everything looks okay but when I check the database table it is supposed to be inserting into - the column values (for occupantnum) haven't changed - they are all still null!

Not sure if this is related, but note that I've now updated j to explicitly be an integer. I noticed while debugging that wResident.occupantnum was appearing as Type "Integer?" - but this doesn't seem to have made any difference - when I rerun it it still says Type "Integer?"

Answer 1

Forgive me if this is wrong - but don't you have to associate the newly created "webResident" objects with the context if you're to save them? I can't see how dbHousing.SubmitChanges() could save webResidents that it's not aware of.

Answer 2

You iterate over all fetched objects with For Each row In pullresidents but you never modify row. So there are no changes that could be send back to the database.

Answer 3

Dave, you need to insert you new webResident objects into the table first before you submit changes.

dbHousing.webResidents.InsertOnSubmit(webResident);

Answer 4

I think you are doing too much here.

You just want to change the row, correct?

Try making this:

Dim wResident As New webResident 
wResident.occupantnum = j 
dbHousing.SubmitChanges() 
j = j + 1 

into this:

row.occupantnum = j 
dbHousing.SubmitChanges() 
j = j + 1