Submitchanges after a loop - only last record saved

Question

The following code works, but it only saves the last record in the loop, and I can't figure out why. I think the Submitchanges() is in the right place, at the end of the loop. Can someone please show me what's wrong? Thanks.

Sub POPULATE_CHAIN()

    Dim newChain As New CHAIN
    Dim dpSTRIKE As Integer

    'get list of Options Contracts
    Dim lstOPT = From Z In DATA.OPTIONs, X In DATA.UDLies
                 Where X.UDLY_SYM = Z.UDLY_SYM
                 Select Z.CONTRACT, Z.STRIKE_GAP, X.UDLY_LAST

    Dim dctOPT = lstOPT.ToDictionary(Function(Z) Z.CONTRACT)

    For Each key In dctOPT.Keys

        For COUNT = 1 To 5
            dpSTRIKE = 1850 + 5 * COUNT
            Dim lkup = From Z In DATA.CHAINs
                       Select Z
            Dim RCD_EXISTS As Boolean = lkup.Any(Function(Z) Z.CONTRACT = dctOPT(key).CONTRACT And Z.P_C = "C" And Z.STRIKE = dpSTRIKE)

            If RCD_EXISTS = False Then
                newChain.CONTRACT = dctOPT(key).CONTRACT
                newChain.P_C = "C"
                newChain.STRIKE = dpSTRIKE
                DATA.CHAINs.InsertOnSubmit(newChain)
            Else
                newChain.CONTRACT = dctOPT(key).CONTRACT
                newChain.P_C = "C"
                newChain.STRIKE = dpSTRIKE
            End If

        Next
    Next
    DATA.SubmitChanges()
End Sub

Should you not be pointing newChain to an existing record if there is a match. At the moment your code uses Any to determine if there is a match, but does not do anything with the matching record. — sgmoore, Feb 10 '16 at 09:25
Tks but I thought the ``else`` part of the ``if..else..end if`` part dealt with matching records. Have I misunderstood the linq update method? I know the db is empty so the problem is adding the 5 new records rather than updating an existing one. — Zeus, Feb 10 '16 at 09:29

score 1 · Accepted Answer · answered Feb 10 '16 at 09:34

1

Dim newChain As New CHAIN

should be inside Fore Each, exactly inside second for. Since it is declared outside the loop, it will be detached from table and attached again to table. So it will be inserted only in last row.

answered Feb 10 '16 at 09:34

Shukri Gashi

535
2
10

Thanks, problem solved! I really appreciate the help :-) – Zeus Feb 10 '16 at 10:25
This solves your problem for the case when you are always inserting records, but it won't work if you are trying to update existing records. – sgmoore Feb 10 '16 at 12:27

Submitchanges after a loop - only last record saved

1 Answers1