Net-informations.com
SiteMap  | About    

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

Database

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

Download

Subtotal in GridView

The programs retrieved data from the STOR table in PUBS database and the program calculate how many quantities supplied from each stores. In order to get the result, we issued an Sql statments for retrieving the Store wise data.

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.

Default.aspx

  

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.