locked
Gridview Show Data On Two Condition RRS feed

  • 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