How to work with multiple updates by linq to sql?

Question

Hi everyone!

First of all, I would like to ask if it is possible to do a multiple records editing by linqtosql method in one click event? What I've been trying to do is to edit all the names in the table which are having the same account number. I was able to edit but only one name has been edited and the rest are not,if it is possible,I would really appreciate if you would take your time with me to resolve this issue.Let me give you the codes I used.

Private Sub Button2_Click(------------) Handles Button2.Click

   Dim accnt As String
   accnt = Textbox1.Text
   Dim db As New memrecDataContext()

   Dim editrecord = From mem In db.Table1s _
                    Where mem.Account = accnt _
                    Select mem

   For Each mem In editrecord
        mem.Name = Textbox2.Text
        db.SubmitChanges()
   Next
   MsgBox("Update is Successfully Completed",MsgBoxStyle.Information)

End Sub

Why is it these codes could only edit records in one datarow? Can you debug the codes to edit all records which are having the same account number after it is being query?if that is possible for you.

Answer 1

You're calling SubmitChanges in a loop. You don't need to do that - just make all the edits you want and then call SubmitChanges. Doing it in the loop should have worked as well, I believe, but it's less efficient. Here's your code with a few wrinkles ironed out:

Private Sub Button2_Click(------------) Handles Button2.Click

   Dim accnt As String = Textbox1.Text
   Using db As New memrecDataContext()
       Dim editrecord = From mem In db.Table1s _
                        Where mem.Account = accnt _

       For Each mem In editrecord
            mem.Name = Textbox2.Text
       Next
       db.SubmitChanges()
   End Using

   MsgBox("Update is Successfully Completed", MsgBoxStyle.Information)    
End Sub