Member Avatar for Jack_58

net.jpg

Please help how to code this on vb.net with SQL server database

  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

For aggregating "same name" totals in a DataGridView there are two practical choices: have SQL Server compute the sums with GROUP BY and bind that result (recommended for larger tables), or compute totals in VB.NET from the bound DataTable/DataGridView (handy when SQL cannot be changed). 's hint about iterating is fine for small grids; the SQL approach avoids transferring all rows and leverages the database engine.

Example SQL (returns one row per name with the total):

SELECT Name, SUM(Quantity) AS TotalQuantity
FROM dbo.MyTable
GROUP BY Name
ORDER BY Name;

Example VB.NET (ADO.NET) to run the query and bind the aggregated result:

Dim sql As String = "SELECT Name, SUM(Quantity) AS TotalQuantity FROM dbo.MyTable GROUP BY Name ORDER BY Name;"
Dim dt As New DataTable()
Using cn As New SqlConnection(connString)
    Using da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
    End Using
End Using
dataGridView1.DataSource = dt

If SQL cannot be changed, aggregate client-side from the bound DataTable (safe for small/medium sets). Dictionary-based accumulator and a LINQ alternative:

' Dictionary accumulator
Dim totals As New Dictionary(Of String, Decimal)
Dim dt As DataTable = CType(dataGridView1.DataSource, DataTable)
For Each r As DataRow In dt.Rows
    Dim n = If(r.IsNull("Name"), String.Empty, r.Field(Of String)("Name"))
    Dim q = If(r.IsNull("Quantity"), 0D, Convert.ToDecimal(r("Quantity")))
    If n = String.Empty Then Continue For
    If totals.ContainsKey(n) Then totals(n) += q Else totals.Add(n, q)
Next

' LINQ summary
Dim summary = From r In dt.AsEnumerable()
              Group r By n = r.Field(Of String)("Name") Into Group
              Select Name = n, Total = Group.Sum(Function(x) Convert.ToDecimal(x("Quantity")))
dataGridViewTotals.DataSource = summary.ToList()

Notes: handle DBNull and mixed numeric types (use Convert.ToDecimal), use Decimal for money, DataGridView has no native footer (use a separate summary grid or status label), and for large datasets prefer the SQL GROUP BY and an index on the Name column.

Member Avatar for rproffitt

Generic answer: Iterate over the table shown and total up what you want then display as you wish.

There are thousands of discussions about picking up what's in that table so that can't be the issue or block.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.