Remove From My Forums

Gridview Show Data On Two Condition

Question

User-807418713 posted

Hello

This is sql table data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Item_Table](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Item_Name] [varchar](250) NULL,
	[Unit] [varchar](50) NULL,
	[Qty] [float] NULL,
	[Rate] [float] NULL,
 CONSTRAINT [PK_Item_Table] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Item_Table] ON
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (1, N'Item1', N'KG', 1, 10)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (2, N'Item1', N'Ltr', -3, 10)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (3, N'Item1', N'KG', 5, 15)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (4, N'Item2', N'KG', 8, 15)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (5, N'Item2', N'Ltr', 6, 15)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (7, N'Item2', N'KG', -2, 20)
INSERT [dbo].[Item_Table] ([ID], [Item_Name], [Unit], [Qty], [Rate]) VALUES (8, N'Item2', N'KG', -1, 20)
SET IDENTITY_INSERT [dbo].[Item_Table] OFF

This is my aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Test.aspx.cs" Inherits="Test" %>

<!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>Untitled Page</title>
    
    <style type="text/css">
        .grid-sltrow {
            background: #ddd;
            font-weight: bold;
        }

        .SubTotalRowStyle {
            border: solid 1px Black;
            background-color: #D8D8D8;
            font-weight: bold;
        }

        .GrandTotalRowStyle {
            border: solid 1px Gray;
            background-color: #000000;
            color: #ffffff;
            font-weight: bold;
        }

        .GroupHeaderStyle {
            border: solid 1px Black;
            background-color: #4682B4;
            color: #ffffff;
            font-weight: bold;
        }

        .serh-grid {
            width: 85%;
            border: 1px solid #6AB5FF;
            background: #fff;
            line-height: 14px;
            font-size: 11px;
            font-family: Verdana;
        }
    </style>

    
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
                OnRowDataBound="GridView1_RowDataBound"
                OnRowCreated="GridView1_RowCreated"
                EnableModelValidation="True">
                <Columns>
                    <asp:BoundField DataField="Item_Name" HeaderText="ItemName" SortExpression="Item_Name" />
                    <asp:BoundField DataField="Unit" HeaderText="Unit" SortExpression="Unit" />
                    <asp:BoundField DataField="Qty" HeaderText="Qty" SortExpression="Qty" >
                        <ItemStyle HorizontalAlign="Right" />
                    </asp:BoundField>
                     <asp:BoundField DataField="Rate" HeaderText="Rate" SortExpression="Rate" >
                         <ItemStyle HorizontalAlign="Right" />
                     </asp:BoundField>
                </Columns>
            </asp:GridView>
    </div>
    </form>
</body>
</html>

This is code behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;


public partial class Test : System.Web.UI.Page
{

    public string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridView();
        }
    }

    protected void BindGridView()
    {
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection(constr))
        {
            string cmdtext = "select ID, Item_Name, Unit, Qty, Rate from Item_Table";
            using (SqlCommand cmd = new SqlCommand(cmdtext, con))
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }

    // To keep track of the previous row Group Identifier    
    string strPreviousRowName = string.Empty;
    // To keep track the Index of Group Total    
    int intSubTotalIndex = 1;
    // To temporarily store Sub Total    
    double dblSubTotalUnitPrice = 0;
    double dblSubTotalQuantity = 0;
    // To temporarily store Grand Total    
    double dblGrandTotalUnitPrice = 0;
    double dblGrandTotalQuantity = 0;
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool IsSubTotalRowNeedToAdd = false;
        bool IsGrandTotalRowNeedtoAdd = false;
        if ((strPreviousRowName != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null))
            if (strPreviousRowName != DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString())
                IsSubTotalRowNeedToAdd = true;
        if ((strPreviousRowName != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") == null))
        {
            IsSubTotalRowNeedToAdd = true;
            IsGrandTotalRowNeedtoAdd = true;
            intSubTotalIndex = 0;
        }

        if ((strPreviousRowName == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null))
        {
            GridView grdViewOrders = (GridView)sender;
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
            TableCell cell = new TableCell();
            cell.Text = "Item_Name : " + DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
            cell.ColumnSpan = 4;
            cell.CssClass = "GroupHeaderStyle";
            row.Cells.Add(cell);
            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
            intSubTotalIndex++;
        }

        if (IsSubTotalRowNeedToAdd)
        {
            #region Adding Sub Total Row
            GridView grdViewOrders = (GridView)sender;
            // Creating a Row          
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
            //Adding Total Cell          
            TableCell cell = new TableCell();
            cell.Text = "Sub Total";
            cell.HorizontalAlign = HorizontalAlign.Left;
            cell.ColumnSpan = 2;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);
            //Adding Quantity Column            
            cell = new TableCell();
            cell.Text = dblSubTotalQuantity.ToString();
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);
            //Adding Unit Price Column          
            cell = new TableCell();
            cell.Text = dblSubTotalUnitPrice.ToString();
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);
            //Adding the Row at the RowIndex position in the Grid      
            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
            intSubTotalIndex++;
            #endregion
            #region Adding Next Group Header Details
            if (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null)
            {
                row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
                cell = new TableCell();
                cell.Text = "Item Name : " + DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
                cell.ColumnSpan = 4;
                cell.CssClass = "GroupHeaderStyle";
                row.Cells.Add(cell);
                grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
                intSubTotalIndex++;
            }
            #endregion
            #region Reseting the Sub Total Variables
            dblSubTotalUnitPrice = 0;
            dblSubTotalQuantity = 0;
            #endregion
        }
        if (IsGrandTotalRowNeedtoAdd)
        {
            #region Grand Total Row
            GridView grdViewOrders = (GridView)sender;
            // Creating a Row      
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
            //Adding Total Cell           
            TableCell cell = new TableCell();
            cell.Text = "Grand Total";
            cell.HorizontalAlign = HorizontalAlign.Left;
            cell.ColumnSpan = 2;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);
            //Adding Quantity Column           
            cell = new TableCell();
            cell.Text = dblGrandTotalQuantity.ToString();
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);
            //Adding Unit Price Column          
            cell = new TableCell();
            cell.Text = dblGrandTotalUnitPrice.ToString();
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);
            //Adding the Row at the RowIndex position in the Grid     
            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex, row);
            #endregion
        }
    }


    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        // This is for cumulating the values       
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            strPreviousRowName = DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
            double dblUnitPrice = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Rate").ToString());
            double dblQuantity = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Qty").ToString());
            // Cumulating Sub Total            
            dblSubTotalUnitPrice += dblUnitPrice;
            dblSubTotalQuantity += dblQuantity;
            // Cumulating Grand Total           
            dblGrandTotalUnitPrice += dblUnitPrice;
            dblGrandTotalQuantity += dblQuantity;
        }
    }


}

Result coming like this

But I want to Eliminate - (Minu) qty then do sum for Qty Column and For Rate Column I want to show separate total for KGS And Ltr Like This Below

Sunday, September 2, 2018 5:16 PM

Answers

User-893317190 posted

Hi Gopi.MCA,

You could record the data of kg and ltr separately.

Below is my code.

 // To keep track of the previous row Group Identifier    
        string strPreviousRowName = string.Empty;
        // To keep track the Index of Group Total    
        int intSubTotalIndex = 1;
        // To temporarily store Sub Total  of kg and ltr separately   
        //double dblSubTotalUnitPrice = 0;
        double dblSubTotalQuantity = 0;

        double kgSubTotalRate = 0;
        double LtrSubToatalRate = 0;
    
       
        // To temporarily store total data of kg and ltr separately   
        //double dblGrandTotalUnitPrice = 0;
        double dblGrandTotalQuantity = 0;
        double kgTotalRate = 0;
       
        double ltrTotalRate = 0;
   
        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            bool IsSubTotalRowNeedToAdd = false;
            bool IsGrandTotalRowNeedtoAdd = false;
            if ((strPreviousRowName != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null))
                if (strPreviousRowName != DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString())
                    IsSubTotalRowNeedToAdd = true;
            if ((strPreviousRowName != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") == null))
            {
                IsSubTotalRowNeedToAdd = true;
                IsGrandTotalRowNeedtoAdd = true;
                intSubTotalIndex = 0;
            }

            if ((strPreviousRowName == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null))
            {
                //insert a new row
                GridView grdViewOrders = (GridView)sender;
                GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
                TableCell cell = new TableCell();
                cell.Text = "Item_Name : " + DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
                cell.ColumnSpan = 4;
                cell.CssClass = "GroupHeaderStyle";
                row.Cells.Add(cell);
                grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
                intSubTotalIndex++;
            }

            if (IsSubTotalRowNeedToAdd)
            {
                #region Adding Sub Total Row
                GridView grdViewOrders = (GridView)sender;
                // Creating a Row          
                GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
                //Adding Total Cell   
           
                TableCell cell = new TableCell();
                //chang the color of the cell
                cell.Style.Add("background-color", "yellow");
                cell.Text = "Sub Total";
                cell.HorizontalAlign = HorizontalAlign.Left;
                cell.ColumnSpan = 2;
                cell.CssClass = "SubTotalRowStyle";
                row.Cells.Add(cell);
                //Adding Quantity Column            
                cell = new TableCell();
                cell.Style.Add("background-color", "yellow");
                // text the subtotal of kg and ltr
        
                cell.Text = dblSubTotalQuantity.ToString();
                cell.HorizontalAlign = HorizontalAlign.Right;
                cell.CssClass = "SubTotalRowStyle";
                row.Cells.Add(cell);
                //Adding Unit Price Column          
                cell = new TableCell();
                cell.Style.Add("background-color", "yellow");
                //text the subtotal of kg and ltr
                cell.Text = "kg="+kgSubTotalRate+"|"+"ltr="+LtrSubToatalRate;
                cell.HorizontalAlign = HorizontalAlign.Right;
                cell.CssClass = "SubTotalRowStyle";
                row.Cells.Add(cell);
                //Adding the Row at the RowIndex position in the Grid      
                grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
                intSubTotalIndex++;
                #endregion
                #region Adding Next Group Header Details
                if (DataBinder.Eval(e.Row.DataItem, "Item_Name") != null)
                {
                    row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
                    cell = new TableCell();
                    cell.Text = "Item Name : " + DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
                    cell.ColumnSpan = 4;
                    cell.CssClass = "GroupHeaderStyle";
                    row.Cells.Add(cell);
                    grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
                    intSubTotalIndex++;
                }
                #endregion
                #region Reseting the Sub Total Variables
                //dblSubTotalUnitPrice = 0;
                //dblSubTotalQuantity = 0;
              //  kgSubTotalQty = 0;
                kgSubTotalRate = 0;
                LtrSubToatalRate = 0;
                dblSubTotalQuantity = 0;
                //LtrSubTotalQty = 0;
                #endregion
            }
            if (IsGrandTotalRowNeedtoAdd)
            {
                #region Grand Total Row
                GridView grdViewOrders = (GridView)sender;
                // Creating a Row      
                GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
                //Adding Total Cell           
                TableCell cell = new TableCell();
                cell.Text = "Grand Total";
                cell.HorizontalAlign = HorizontalAlign.Left;
                cell.ColumnSpan = 2;
                cell.CssClass = "GrandTotalRowStyle";
                cell.Style.Add("background-color", "green");
                row.Cells.Add(cell);
                //Adding Quantity Column           
                cell = new TableCell();
                //text the total data of kg and ltr
           
                cell.Text = dblGrandTotalQuantity.ToString();
                cell.HorizontalAlign = HorizontalAlign.Right;
                cell.CssClass = "GrandTotalRowStyle";
                cell.Style.Add("background-color", "green");
                row.Cells.Add(cell);
                //Adding Unit Price Column          
                cell = new TableCell();
                cell.Text ="kg="+kgTotalRate+"|"+"Ltr="+ltrTotalRate;
                cell.HorizontalAlign = HorizontalAlign.Right;
                cell.CssClass = "GrandTotalRowStyle";
                cell.Style.Add("background-color", "green");
                row.Cells.Add(cell);
                //Adding the Row at the RowIndex position in the Grid     
                grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex, row);
                #endregion
            }
        }


        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // This is for cumulating the values       
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                strPreviousRowName = DataBinder.Eval(e.Row.DataItem, "Item_Name").ToString();
                double dblUnitPrice = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Rate").ToString());
                double dblQuantity = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Qty").ToString());
                // Cumulating Sub Total            
                //  dblSubTotalUnitPrice += dblUnitPrice;
                if (dblQuantity > 0)
                {
                    dblSubTotalQuantity += dblQuantity;
                    dblGrandTotalQuantity += dblQuantity;
                }
                if (DataBinder.Eval(e.Row.DataItem, "Unit").ToString() == "KG")
                {
                    //if current row is the row of kg , record its data
                    kgSubTotalRate += dblUnitPrice;
                   // kgSubTotalQty += dblQuantity;
                    kgTotalRate += dblUnitPrice;
                   // kgTotalQty += dblQuantity;
                }
                if (DataBinder.Eval(e.Row.DataItem, "Unit").ToString() == "Ltr")
                {
                    //if current row is the row of ltr , record its data
                    LtrSubToatalRate += dblUnitPrice;
                   // LtrSubTotalQty += dblQuantity;
                    ltrTotalRate += dblUnitPrice;
                   // ltrTotalQty += dblQuantity;
                }

                //dblSubTotalQuantity += dblQuantity;
                //// Cumulating Grand Total           
                //dblGrandTotalUnitPrice += dblUnitPrice;
                //dblGrandTotalQuantity += dblQuantity;
            }
        }

Best regards,

Ackerly Xu

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Monday, September 3, 2018 4:17 AM

Answered by:

Gridview Show Data On Two Condition

Question

Answers