1. I want to see if ID from datatable dtOriginalAvailable exists as an ID in datatable dtCalculateCurrent.

2. If the ID does not exist in datatable dtCalculateCurrent, then I want to add a row in datatable dtCalculateCurrent with that ID.

My attempt below does not work. How can I accomplish this?

 For Each dr As DataRow In dtCalculateCurrent.Rows
            Dim ma = Convert.ToInt32(dr("MaxAccount"))
            Dim cn = Convert.ToInt32(dr("ActiveServiceCount"))
            Dim calc = ma - cn
            dr("CurrentNumber") = calc

            'if service not in calculated because of 0 accounts, add as row in calculated
            For Each origRow In dtOriginalAvailable.Rows

                Dim findService As DataRow() = dtOriginalAvailable.[Select]("ID = '" & dr("ID") & "'") 
                If findService.Length = 0 Then
                    Dim R As DataRow = dtCalculateCurrent.NewRow
                    R("ID") = origRow("ID")
                    R("MaxAccount") = origRow("MaxAccount")
                    R("ActiveServiceCount") = 0
                    R("CurrentNumber") = 0
                    dtCalculateCurrent.Rows.Add(R)
                    dtCalculateCurrent.AcceptChanges()
                End If

            Next
        Next

CodePudding user response：

I do not understand why your test for rows in dtOriginalAvailable that are missing in dtCalculateCurrent is included within a loop on dtCalculateCurrent. This means that the same test is carried out multiple times (once for each row in dtCalculateCurrent). This is clearly unnecessary.

Secondly you are selecting from the wrong DataTable.

I am not sure if AcceptChanges is necessary here. If I remember correctly, it is normally used in connection with a DbDataAdapter.

What I think you need is this:

For Each dr As DataRow In dtCalculateCurrent.Rows
    Dim ma = Convert.ToInt32(dr("MaxAccount"))
    Dim cn = Convert.ToInt32(dr("ActiveServiceCount"))
    Dim calc = ma - cn
    dr("CurrentNumber") = calc
    dtCalculateCurrent.AcceptChanges()
Next

'if service not in calculated because of 0 accounts, add as row in calculated
For Each origRow In dtOriginalAvailable.Rows

    Dim findService As DataRow() = dtCalculateCurrent.[Select]("ID = '" & origRow("ID") & "'")
    If findService.Length = 0 Then
        Dim R As DataRow = dtCalculateCurrent.NewRow
        R("ID") = origRow("ID")
        R("MaxAccount") = origRow("MaxAccount")
        R("ActiveServiceCount") = 0
        R("CurrentNumber") = 0
        dtCalculateCurrent.Rows.Add(R)
        dtCalculateCurrent.AcceptChanges()
    End If
Next