Written by: Steve Gray
8/24/2009 9:16 AM 

In a recent project, I needed to sum data in a web page grid and process it on postback.

I could get it into at datatable easily enough, but getting it grouped and summed was stumping me.

Our columns are ITEMNMBR, QUANTITY, LOTNUMBER, DEX_ROW_ID. We’ll group on ITEMNMBR and DEX_ROW_ID. We’ll sum on QUANTITY

So, the data might be:
ITEM1, 5, LOT A, 1001
ITEM1, 6, LOT B, 1001
ITEM2, 1, <NULL>, 1002
ITEM3, 2, <NULL>,1003

Lines 1 and 2 above have the same item number and row id, but 2 different lot numbers.

The ‘groupRow’ item in the code below holds a set of rows of the underlying group. In the second section of code we loop through that object. Note that there are two quantity results, the group quantity (11) and the line quantity (5,6)

Here’s the solution. It uses LINQ, so add a reference to SYSTEM.LINQ at the top of the page. Note the use of a system function to do the summing; it’s declared in the second line

Dim oDT As DataTable = gridToDataTable(Me.Grid1)

Dim aggregateSum As System.Func(Of DataRow, Integer) = AddressOf GetSum

Dim data = From c In oDT.AsEnumerable() _

           Group c By itemGroup = c.Field(Of String)("itemnmbr"), _

                dexGroup = c.Field(Of Int32)("dex_row_id") Into Group _

           Select groupRow = Group, _

                itemGroup = itemGroup, _

                quantity = Group.Sum(aggregateSum), _

                dexGroup = dexGroup

For Each g In data

    strItemnmbr = g.itemGroup

    dblItemSellingQtyReceived = g.quantity

    intDex_row_id = g.dexGroup

    For Each n In g.groupRow

        strSerltnum = n("serltnum")

        dblLotSellingQtyReceived = n("quantity")

    Next

Next

The function:

Public Function GetSum(ByVal datarow As DataRow) As Integer

    Return Integer.Parse(datarow("quantity").ToString())

End Function

Trackback Print

Tags:

Categories:

Location: Blogs DEVSHED Blogs LINQ