Search code examples
vb.netdatatablecheckboxlist

Using two datatables, group by ID and count, then subtract, then update rows

I am using Visual Studio with vb.net, asp.net, and webforms.

I populate two datatables and place some values into two checkboxlists (Populate Available Checkboxlist and Populate Active Checkboxlist). That all works great.

Here is where I am stuck and need help.

First Step: In Available, I have a column called ID. The ID values are unique as 1, 2, 3, 4, etc. In Active, I have ID which correspond to those, but there can be multiples, such as 1, 1, 2, 3, 1, 4, etc. So what I need to do is COUNT how many of each ID is in Active. For example, in 1, 1, 2, 3, 1, 4 there are three 1's, one 2, one 3, and one 4. How can I do that?

Second Step: After I do that, I need to do subtraction. I need to see what MaxAccount is (such as 5) and subtract it from Step 1. So for example, if MaxAccount is 5 and I have three 1's, then 5 - 3 = 2.

Third Step: I need to update AccountTotal with the value from step 2.

You can see my attempt to code this at UpdateAccountTotal() but just get stuck.

Update AccountTotal

Private Function UpdateAccountTotal() As DataTable
    Dim dtDefaultAvailable As New DataTable
    dtDefaultAvailable = ViewState("vsDefaultAvailable")

    Dim dtDefaultActive As New DataTable
    dtDefaultActive = ViewState("vsDefaultActive")

    Dim dtDefaultAmount As New DataTable
    ViewState("vsDefaultAmounts") = dtDefaultAmount

    For Each rowAvailable In dtDefaultAvailable.Rows
        Dim maxAccount = rowAvailable("MaxAccount").ToString()

        'For Each rowActive In dtDefaultActive.Rows
        'Dim ID = rowAvailable("ID").ToString()
        'Dim countID = ID.Count

    Next
End Function
Solution

  • It's probably not how I'd do it, but:

    For Each rowAvailable as DataRow In dtDefaultAvailable.Rows
    Dim maxAccount = DirectCast(rowAvailable("MaxAccount"), Integer) 'it's an int, right?

    'step 2 and 3
    rowAvailable("AccountTotal") = maxAccount - dtDefaultActive.Rows.Cast(Of DataRow).Count(Function(r) r("ID").Equals(rowAvailable("ID")))

Next

    As for how I'd do it:

    • Create a DataSet type file
    • Inside the visual designer create a datatable for Available and a datatable for Active
    • Create a DataRelation where Active is a child of Available (Available ID is PK, Active.AvailableId is FK)
    • Create a column in Available with an Expression of [MaxAccount] - COUNT(Child.Id)

    enter image description here

    Strongly typed datatables designed visually are a lot nicer to work with in code. Were your datatables strongly typed, the code would look like:

    For Each rowAvailable In dtDefaultAvailable
    
    'step 2 and 3
    rowAvailable.AccountTotal = rowAvailable.MaxAccount - dtDefaultActive.Count(Function(r) r.ID = rowAvailable.ID)

Next

    Much less messy when everything isn't an object, accessed by a string and needing casting.. And Intellisense works..