Gridview Colum Total Like this

Question

Hello

This is my gridview data

I want to column total like this below

How to do so using asp.net c#

Answer

Set logic is easier handled in SQL.

IF OBJECT_ID('tempdb..#SumEx') IS NOT NULL
 DROP TABLE #SumEx

CREATE TABLE #SumEx (
 Field1 VARCHAR(1),
 Field2 INT
)

INSERT INTO #SumEx (Field1, Field2)
VALUES ('A', 10), ('B', -4), ('C', 5), ('D', -18)

SELECT t.Field1, t.Field2
FROM (
 SELECT 1 AS Sort, Field1, Field2 
 FROM #SumEx
 UNION
 SELECT 2 AS Sort, 'Total +', SUM(Field2) 
 FROM #SumEx
 WHERE Field2 >= 0
 UNION
 SELECT 3 AS Sort, 'Total -',SUM(Field2)
 FROM #SumEx
 WHERE Field2 < 0
 UNION
 SELECT 4 AS Sort, 'GRAND TOTAL', SUM(Field2)
 FROM #SumEx) AS t
ORDER BY t.Sort

Using a GridView is possible but it requires iterating/querying the GridView's data source or writing conditional code in the row data bind handler that keeps track of the three running totals. Can you show us what you've tried?

Answer

Well, as noted, I don't think the math, or code to add up the 3 totals is hard.

but, what WILL be a challenge is digging up the code to add some extra footer rows, that will be a challenge.

So, drop in a gridview, turn on the footer.

That will give us ONE footer row. We have to add two more.

So, markup could be say this:

Nothing special. But DO note the setting to turn on the gv footer.

Ok, so now some code. We will get the data, fill the GV, and then add two more rows to the footer.

Out code can thus be this:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
  
        If Not IsPostBack Then  
            LoadData  
        End If  
  
    End Sub  
  
    Sub LoadData()  
  
        Dim rstData As DataTable = MyRst("SELECT Gname, Qty FROM Products")  
  
        ' fill grid  
        GridView1.DataSource = rstData  
        GridView1.DataBind()  
  
        Dim iAddTotal As Integer  
        Dim iMinusTotal As Integer  
        Dim iTotal As Integer  
  
        For Each OneRow As DataRow In rstData.Rows  
            iTotal += OneRow("Qty")  
            If OneRow("Qty") >= 0 Then  
                iAddTotal += OneRow("Qty")  
            Else  
                iMinusTotal -= OneRow("Qty")  
            End If  
        Next  
  
        ' now fill out the footer rows  
  
        Dim MyRow As GridViewRow = GridView1.FooterRow  
        MyRow.Cells(0).Text = "Added"  
        MyRow.Cells(1).Text = iAddTotal  
        MyRow.Cells(1).HorizontalAlign = HorizontalAlign.Right  
  
        ' add two more rows to existing  
        AddOneFooter("Minus", iMinusTotal)  
        AddOneFooter("Grand Total", iTotal)  
  
  
    End Sub  
  
    Sub AddOneFooter(sCol1 As String, sCol2 As String)  
  
        ' add 2nd row  
        Dim MyRow As New GridViewRow(0, 0, DataControlRowType.Footer, DataControlRowState.Normal)  
        Dim MyCell1 As TableCell = New TableCell  
        Dim MyCell2 As TableCell = New TableCell  
        MyCell1.Text = sCol1  
        MyCell2.Text = sCol2  
        MyCell2.HorizontalAlign = HorizontalAlign.Right  
  
        MyRow.Controls.Add(MyCell1)  
        MyRow.Controls.Add(MyCell2)  
        GridView1.FooterRow.Parent.Controls.Add(MyRow)  
  
    End Sub

and now we see/get this:

so, as noted, the simple data part is easy - but that code to add some extra rows? Well, that is the gold stone you need here.

Now, I did use a routine called MyRst to load my data - but there are as many ways to do that as flavors of ice cream, but for the sake of complete, that code was this:

    Public Function MyRst(strSQL As String) As DataTable  
      
        Dim rstData As New DataTable  
        Using conn As New SqlConnection(strCon)  
            Using cmdSQL As New SqlCommand(strSQL, conn)  
                conn.Open()  
                rstData.Load(cmdSQL.ExecuteReader)  
                rstData.TableName = strSQL  
            End Using  
        End Using  
        Return rstData  
    End Function

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer

Hi @RAVI ,
You can try the example below. Do the calculations and add the required rows before binding the GridView.

protected void Page_Load(object sender, EventArgs e)  
        {  
            int sum = 0;  
            int a;  
            int b = 0;  
            int c = 0;  
            using (SqlConnection con = new SqlConnection(@"Data Source=***"))  
            {  
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM Test1", con))  
                {  
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);  
                    DataTable dt;  
                    using (dt = new DataTable())  
                    {  
                        sda.Fill(dt);  
                        foreach (DataRow dr in dt.Rows)  
                        {                              
                            a = Convert.ToInt32(dr["Field2"]);  
                            sum += Convert.ToInt32(dr["Field2"]);  
                            if (a> 0)  
                            {                                
                                b = b + a;  
                            }  
                            if(a < 0)  
                            {                            
                                c = c+a;  
                            }  
                        }  
                        DataRow totalRow1 = dt.NewRow();  
                        totalRow1["Field1"] = "Total+";  
                        totalRow1["Field2"] = b;  
                        dt.Rows.Add(totalRow1);  
                        DataRow totalRow2 = dt.NewRow();  
                        totalRow2["Field1"] = "Total-";  
                        totalRow2["Field2"] = c;  
                        dt.Rows.Add(totalRow2);  
                        DataRow totalRow = dt.NewRow();  
                        totalRow["Field1"] = "Grand Total";  
                        totalRow["Field2"] = sum;  
                        dt.Rows.Add(totalRow);  
                        GridView1.DataSource = dt;  
                        GridView1.DataBind();  
                    }  
  
                }  
  
            }     
        }

Result

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Gridview Colum Total Like this

3 answers