Subtotal row in Gridview

In the following lesson, you can learn how to implement a grouping and calculate subtotal for each group in ASP.Net GridView.

subtotal in gridview


In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.


Subtotal in GridView

To obtain the desired result of calculating the quantities supplied from each store, the program retrieves data from the STOR table in the PUBS database. To achieve this, the program executes SQL statements specifically designed to retrieve store-wise data.

By formulating appropriate SQL queries, developers can extract the necessary information from the STOR table. These queries are tailored to retrieve the relevant data, such as quantities supplied, from each individual store.

The program processes these SQL statements and retrieves the required data, allowing for further calculations to determine the quantities supplied by each store. This calculation can be performed using programming logic, such as iterating through the retrieved data and aggregating the quantities accordingly.

By employing SQL statements and implementing suitable programming logic, developers can successfully retrieve store-wise data from the STOR table and calculate the quantities supplied by each store. This information provides valuable insights into the distribution and supply chain aspects of the PUBS database, facilitating informed decision-making and analysis.

subtotal row in gridview
select distinct stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty

From the result of the above sql, the program find the subtotal of each store and display in the GridView. After calculating the subtotal, we are forced to insert a new row after each store data and display the subtotal.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" onrowdatabound="GridView1_RowDataBound" onrowcreated="GridView1_RowCreated"> <Columns> <asp:BoundField DataField="stor_id" HeaderText="stor_id" /> <asp:BoundField DataField="ord_num" HeaderText="ord_num" /> <asp:BoundField DataField="title_id" HeaderText="title_id" /> <asp:BoundField DataField="qty" HeaderText="qty" ItemStyle-HorizontalAlign="Right"/> </Columns> </asp:GridView> </div> </form> </body> </html>
C# Source Code
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; public partial class _Default : System.Web.UI.Page { int qtyTotal = 0; int storid = 0; int rowIndex = 1; protected void Page_Load(object sender, EventArgs e) { SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string sql = null; string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"; sql = "select distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty"; SqlConnection connection = new SqlConnection(connetionString); connection.Open(); SqlCommand command = new SqlCommand(sql, connection); adapter.SelectCommand = command; adapter.Fill(ds); adapter.Dispose(); command.Dispose(); connection.Close(); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()); int tmpTotal = Convert.ToInt32 (DataBinder.Eval(e.Row.DataItem, "qty").ToString()); qtyTotal += tmpTotal; } } protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e) { bool newRow = false; if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") != null)) { if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString())) newRow = true; } if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") == null)) { newRow = true; rowIndex = 0; } if (newRow) { GridView GridView1 = (GridView)sender; GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); NewTotalRow.Font.Bold = true; NewTotalRow.BackColor = System.Drawing.Color.Gray; NewTotalRow.ForeColor = System.Drawing.Color.White; TableCell HeaderCell = new TableCell(); HeaderCell.Text = "Sub Total"; HeaderCell.HorizontalAlign = HorizontalAlign.Left ; HeaderCell.ColumnSpan = 3; NewTotalRow.Cells.Add(HeaderCell); HeaderCell = new TableCell(); HeaderCell.HorizontalAlign = HorizontalAlign.Right; HeaderCell.Text = qtyTotal.ToString(); NewTotalRow.Cells.Add(HeaderCell); GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow); rowIndex++; qtyTotal = 0; } } }
VB.Net Source Code
Imports System.Drawing Imports System.Data.SqlClient Imports System.Data Partial Class _Default Inherits System.Web.UI.Page Dim qtyTotal As Integer = 0 Dim storid As Integer = 0 Dim rowIndex As Integer = 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim adapter As New SqlDataAdapter() Dim ds As New DataSet() Dim i As Integer = 0 Dim sql As String = Nothing Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****" sql = "select distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty" Dim connection As New SqlConnection(connetionString) connection.Open() Dim command As New SqlCommand(sql, connection) adapter.SelectCommand = command adapter.Fill(ds) adapter.Dispose() command.Dispose() connection.Close() GridView1.DataSource = ds.Tables(0) GridView1.DataBind() End Sub Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()) Dim tmpTotal As Integer = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "qty").ToString()) qtyTotal += tmpTotal End If End Sub Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Dim newRow As Boolean = False If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") IsNot Nothing) Then If storid <> Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()) Then newRow = True End If End If If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") Is Nothing) Then newRow = True rowIndex = 0 End If If newRow Then Dim GridView1 As GridView = DirectCast(sender, GridView) Dim NewTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert) NewTotalRow.Font.Bold = True NewTotalRow.BackColor = System.Drawing.Color.Gray NewTotalRow.ForeColor = System.Drawing.Color.White Dim HeaderCell As New TableCell() HeaderCell.Text = "Sub Total" HeaderCell.HorizontalAlign = HorizontalAlign.Left HeaderCell.ColumnSpan = 3 NewTotalRow.Cells.Add(HeaderCell) HeaderCell = New TableCell() HeaderCell.HorizontalAlign = HorizontalAlign.Right HeaderCell.Text = qtyTotal.ToString() NewTotalRow.Cells.Add(HeaderCell) GridView1.Controls(0).Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow) rowIndex += 1 qtyTotal = 0 End If End Sub End Class