Friday, 5 September 2014

How to display SubTotal and Grand Total in ASP.Net GridView using C#/VB.NET

Hi friends,in this article i will explain about How to display SubTotal and Grand Total in ASP.Net GridView using c#/VB.NET.

 I already explained in the previous articles about How to Edit GridView using BoundField Column in ASP.Net using C#/VB.NET, Code First Approach using Entity Framework 4.0 Sample example and How to create PDF document in ASP.NET with C#/VB.NET using iTextSharp

The records are divided into Groups and then SubTotal is calculated for each Group and then displayed using a dynamic Row in GridView.

 The following code is the structure for Products table.
CREATE TABLE [dbo].[Products](
     [ProductID] [int] NULL,
     [ProductName] [varchar](100) NULL,
     [CategoryID] [int] NULL,
     [UnitPrice] [decimal](18, 0) NULL,
     [QuantityPerUnit] [varchar](100) NULL
) ON [PRIMARY]

GO

First create one new web application and open your GridViewSubTotalTotal.aspx and write the following code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>How to display SubTotal and Grand Total in ASP.Net GridView using c#/VB.NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <h3 style="color:Green">Display SubTotal and Grand Total in ASP.Net GridView</h3>
    <div>
        <asp:GridView ID="gvData" runat="server" BackColor="White" BorderColor="#CC9966"
            AutoGenerateColumns="false" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"
            Font-Names="Tahoma" Font-Size="Small"  Width="475px" OnRowCreated="gvData_RowCreated" OnDataBound="gvData_OnDataBound">
            <Columns>
                <asp:BoundField DataField="ProductID" HeaderText="ProductID"  />

                <asp:BoundField DataField="CategoryID" HeaderText="Category ID" />
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" />
                <asp:BoundField DataField="Price" HeaderText="Price"  DataFormatString="{0:N2}"/>

            </Columns>
            <FooterStyle BackColor="Tan" />
            <AlternatingRowStyle BackColor="#E6E6E1" />
            <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Now in code behind file write the code like as shown below.
GridViewSubTotalTotal.aspx.cs:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;

public partial class GridViewSubTotalTotal : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridData();
        }
    }
    protected void BindGridData()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
        string sqlQuery = "SELECT ProductID,ProductName,CategoryID,(UnitPrice*QuantityPerUnit) AS Price FROM Products";
        sqlQuery = sqlQuery + " WHERE CategoryID in(1,2,3) ORDER BY ProductID ASC";
        SqlCommand cmd = new SqlCommand(sqlQuery, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        gvData.DataSource = ds;
        gvData.DataBind();
    }

    int currentId = 0;
    decimal subTotal = 0;
    decimal total = 0;
    int subTotalRowIndex = 0;
    protected void gvData_RowCreated(object sender, GridViewRowEventArgs e)
    {
        subTotal = 0;
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
            int ProductID = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["ProductID"]);
            total += Convert.ToDecimal(dt.Rows[e.Row.RowIndex]["Price"]);
            if (ProductID != currentId)
            {
                if (e.Row.RowIndex > 0)
                {
                    for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
                    {
                        subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[3].Text);
                    }
                    this.AddTotalRow("Sub Total", subTotal.ToString("N2"));
                    subTotalRowIndex = e.Row.RowIndex;
                }
                currentId = ProductID;
            }
        }
    }

    private void AddTotalRow(string labelText, string value)
    {
        GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
        row.BackColor = ColorTranslator.FromHtml("#FFA500");
        row.Cells.AddRange(new TableCell[4] {new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Right},
                                                            new TableCell (),
                                                            new TableCell(), //Empty Cell,
                                                            new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Right } });
        row.Cells[0].BorderColor = System.Drawing.Color.Orange;
        row.Cells[1].BorderColor = System.Drawing.Color.Orange;
        row.Cells[2].BorderColor = System.Drawing.Color.Orange;
        row.Cells[3].BorderColor = System.Drawing.Color.Orange;
        gvData.Controls[0].Controls.Add(row);
    }
    protected void gvData_OnDataBound(object sender, EventArgs e)
    {
        for (int i = subTotalRowIndex; i < gvData.Rows.Count; i++)
        {
            subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[3].Text);
        }
        this.AddTotalRow("Sub Total", subTotal.ToString("N2"));
        this.AddTotalRow("Total", total.ToString("N2"));
    }
}

GridViewSubTotalTotal.aspx.vb:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Drawing

Partial Public Class GridViewSubTotalTotal
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            BindGridData()
        End If
    End Sub
    Protected Sub BindGridData()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
        Dim sqlQuery As String = "SELECT ProductID,ProductName,CategoryID,(UnitPrice*QuantityPerUnit) AS Price FROM Products"
        sqlQuery = sqlQuery & Convert.ToString(" WHERE CategoryID in(1,2,3) ORDER BY ProductID ASC")
        Dim cmd As New SqlCommand(sqlQuery, con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        gvData.DataSource = ds
        gvData.DataBind()
    End Sub

    Private currentId As Integer = 0
    Private subTotal As Decimal = 0
    Private total As Decimal = 0
    Private subTotalRowIndex As Integer = 0
    Protected Sub gvData_RowCreated(sender As Object, e As GridViewRowEventArgs)
        subTotal = 0
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim dt As DataTable = TryCast(e.Row.DataItem, DataRowView).DataView.Table
            Dim ProductID As Integer = Convert.ToInt32(dt.Rows(e.Row.RowIndex)("ProductID"))
            total += Convert.ToDecimal(dt.Rows(e.Row.RowIndex)("Price"))
            If ProductID <> currentId Then
                If e.Row.RowIndex > 0 Then
                    For i As Integer = subTotalRowIndex To e.Row.RowIndex - 1
                        subTotal += Convert.ToDecimal(gvData.Rows(i).Cells(3).Text)
                    Next
                    Me.AddTotalRow("Sub Total", subTotal.ToString("N2"))
                    subTotalRowIndex = e.Row.RowIndex
                End If
                currentId = ProductID
            End If
        End If
    End Sub

    Private Sub AddTotalRow(labelText As String, value As String)
        Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal)
        row.BackColor = ColorTranslator.FromHtml("#FFA500")
        'Empty Cell,
                   row.Cells.AddRange(New TableCell(3) {New TableCell() With { _
                             Key .Text = labelText, _
                             Key .HorizontalAlign = HorizontalAlign.Right _
                   }, New TableCell(), New TableCell(), New TableCell() With { _
                             Key .Text = value, _
                             Key .HorizontalAlign = HorizontalAlign.Right _
                   }})
        row.Cells(0).BorderColor = System.Drawing.Color.Orange
        row.Cells(1).BorderColor = System.Drawing.Color.Orange
        row.Cells(2).BorderColor = System.Drawing.Color.Orange
        row.Cells(3).BorderColor = System.Drawing.Color.Orange
        gvData.Controls(0).Controls.Add(row)
    End Sub
    Protected Sub gvData_OnDataBound(sender As Object, e As EventArgs)
        For i As Integer = subTotalRowIndex To gvData.Rows.Count - 1
            subTotal += Convert.ToDecimal(gvData.Rows(i).Cells(3).Text)
        Next
        Me.AddTotalRow("Sub Total", subTotal.ToString("N2"))
        Me.AddTotalRow("Total", total.ToString("N2"))
    End Sub
End Class


The output of the above code as shown in the below figure.

You can download the code by clicking on the below Download image. 
Posted by
Labels: , , ,

7 comments:

  1. Anonymous8 September 2014 at 02:58

    Error !!!

    ReplyDelete
  2. Anonymous8 September 2014 at 03:00

    Hello,
    I have tried this but am getting an error i.e "Input string was not in a correct format."

    Input string was not in a correct format.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.FormatException: Input string was not in a correct format.

    Source Error:

    Line 37: con.Close();
    Line 38: gvData.DataSource = ds;
    Line 39: gvData.DataBind();
    Line 40: }

    Error occurs on Line 39: gvData.DataBind();

    ReplyDelete
    Replies
    1. Kishore8 September 2014 at 04:06


      Remove con.Close(); and try

      Delete
    2. Anonymous8 September 2014 at 06:30

      Tried... Still same error !!!

      Delete
    3. Kishore8 September 2014 at 07:49

      Once you post your code then I will check

      Delete
    4. Anonymous8 September 2014 at 11:25

      Here is my 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;
      using System.Data.SqlClient;
      using System.Configuration;
      using System.Drawing;

      namespace SubAndGrandTotalInGridview
      {
      public partial class GridViewSubTotalTotal : System.Web.UI.Page
      {
      int currentId = 0;
      decimal subTotal = 0;
      decimal total = 0;
      int subTotalRowIndex = 0;
      protected void Page_Load(object sender, EventArgs e)
      {
      if (!IsPostBack)
      {
      BindGridData();
      }
      }


      protected void BindGridData()
      {
      SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
      string sqlQuery = "SELECT ProductID,ProductName,CategoryID,(UnitPrice*QuantityPerUnit) AS Price FROM ProductDetails";
      sqlQuery = sqlQuery + " WHERE CategoryID in(1,2,3) ORDER BY ProductID ASC";
      string sqlQuery = "SELECT ProductId,ProductName,CategoryId,(UnitPrice*QuantityPerUnit) AS Price FROM ProductDetails";
      sqlQuery = sqlQuery + " WHERE CategoryId IN (1, 2, 3) ORDER BY ProductId ASC";
      SqlCommand cmd = new SqlCommand(sqlQuery, con);
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds);
      gvData.DataSource = ds;
      gvData.DataBind();
      con.Close();
      }


      protected void gvData_RowCreated(object sender, GridViewRowEventArgs e)
      {
      subTotal = 0;
      if (e.Row.RowType == DataControlRowType.DataRow)
      {
      DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
      int ProductID = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["ProductId"]);
      total += Convert.ToDecimal(dt.Rows[e.Row.RowIndex]["Price"]);
      if (ProductID != currentId)
      {
      if (e.Row.RowIndex > 0)
      {
      for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
      {
      subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[3].Text);
      }
      this.AddTotalRow("Sub Total",subTotal.ToString("N2"));
      subTotalRowIndex = e.Row.RowIndex;
      }
      currentId = ProductID;
      }
      }
      }


      private void AddTotalRow(string labelText, string value)
      {
      GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
      row.BackColor = ColorTranslator.FromHtml("FFA500");
      row.Cells.AddRange(new TableCell[4] { new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Right },
      new TableCell(),
      new TableCell(),
      new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Right } });
      row.Cells[0].BorderColor = System.Drawing.Color.Orange;
      row.Cells[1].BorderColor = System.Drawing.Color.Orange;
      row.Cells[2].BorderColor = System.Drawing.Color.Orange;
      row.Cells[3].BorderColor = System.Drawing.Color.Orange;
      gvData.Controls[0].Controls.Add(row);
      }

      protected void gvData_DataBound(object sender, EventArgs e)
      {
      for (int i = subTotalRowIndex; i < gvData.Rows.Count; i++)
      {
      subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[3].Text);
      }
      this.AddTotalRow("Sub Total", subTotal.ToString("N2"));
      this.AddTotalRow("Total", total.ToString("N2"));
      }
      }
      }

      Delete
    5. Kishore19 October 2014 at 03:32

      Please download the zip file

      Delete

Subscribe to: Post Comments (Atom)
© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.