Question 213181 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Adding Sum of columns in GridView

Not able to add sum of columns in a GridView Footer.
GridView Cell number is 9 where I want to display the sum in the footer.
I multiplied the Price with Quantity and displayed it in ‘ItemTotal’ column which is working.

<ItemTemplate>
         <asp:Label ID="Label1" runat="server" Text='<%# ((Convert.ToInt32(Eval("Price")))*(Convert.ToInt32(Eval("Quantity"))))%>'>></asp:Label>
 </ItemTemplate>

My Code is given below

Method 1

I have created a method to add still column values are not getting calculated.

private void calculateSum()
        {
            int grandtotal = 0;
            foreach (GridViewRow row in GridView1.Rows)
            {
                string cellValue = row.Cells[9].Text;
                int parsedValue;
                if (int.TryParse(cellValue, out parsedValue))
                {
                    grandtotal += parsedValue;
                }
            }

            GridView1.FooterRow.Cells[9].Text = grandtotal.ToString();
        }

protected void Page_Load(object sender, EventArgs e)
        {
            calculateSum();
        }

enter image description here

I tried method 2 as well
But receiving error message

private void calculateSum()
        {
            Int32 grandtotal = 0;
            foreach (GridViewRow row in GridView1.Rows)
            {
                grandtotal = grandtotal + Convert.ToInt32(row.Cells[9].Text); 
            }
            GridView1.FooterRow.Cells[9].Text = grandtotal.ToString();
        }

Receiving error message: Input string was not in a correct format.
enter image description here

Conclusion: In Method 1 I am not getting any error, but in Method 2.
I need to add sum of columns in GridView Column 9 Footer

Which method should I go with and what to fix here.

I’ll be thankful if you help me with the correct code

Tags:

2

Answers


  1. 0

    It needs to ensure what 9 is the correct index value. Please check your code with this:

    private void calculateSum()
        {
            string grandtotal = "";
            foreach (GridViewRow row in GridView1.Rows)
            {
                grandtotal += row.Cells[9].Text + ";"; 
            }
            GridView1.FooterRow.Cells[9].Text = grandtotal;
        }

    Provide feedback in comments.

    Login or Signup to reply.
  2. 0

    Ok, the issue is that templated columns do NOT exist as a ".text" value in the GridView cells collection.

    So, while the cells collection will exist, for any templated control, you can’t use cells collection (.text) to get that value.

    so, the typical design pattern (assuming some label in a templated column) in the grid view?

    You use find control like this:

            decimal MyTotal = 0;
        foreach (GridViewRow gRow in GridView1.Rows)
        {
            Label lblTotalT = (Label)gRow.FindControl("Label1");
            MyTotal += Convert.ToDecimal(lblTotalT.Text);
        }

    So, use cells[] for datafield columns, and use above "find control" for templated columns.

    However, often I find it better to total the data SOURCE and not try and use the UI display of the data as some database. One "big" reason for this is that you often have formatting for the numbers such as currency, or whatever. And that means the display of the data with say a $ or currency symbol results in messy conversion issues.

    So, try and generate the total against the DATA for the GridView, and not use the GridView "UI" display.

    So, this example Grid:

    <asp:GridView ID="GridView1" runat="server"
    AutoGenerateColumns="False" DataKeyNames="ID" CssClass="table"
    ShowFooter="true">
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName" />
        <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
        <asp:BoundField DataField="Description" HeaderText="Description" />
        <asp:BoundField DataField="Nights" HeaderText="Nights" ItemStyle-HorizontalAlign="Center" />
        <asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:c2}"
            ItemStyle-HorizontalAlign="Right" />

        <asp:TemplateField HeaderText="Total" ItemStyle-HorizontalAlign="Right">
            <ItemTemplate>
                <asp:Label ID="lblTotal" runat="server"
                    Text='<%# string.Format("{0:c2}", Eval("MyTotal")) %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:Label ID="lblMyTotal" runat="server" Text="x">
                </asp:Label>
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

    And our code to load is this:

        void LoadGrid()
    {
        DataTable rstData;
        string strSQL = 
            @"SELECT * FROM tblHotels WHERE Nights > 0
            ORDER BY HotelName";

        rstData = General.MyRst(strSQL);

        DataColumn MyCol = new DataColumn("MyTotal", typeof(decimal));
        MyCol.Expression = "[Nights] * [Price]";
        rstData.Columns.Add(MyCol);

        GridView1.DataSource = rstData;
        GridView1.DataBind();

        // set up the footing total.
        decimal gTotal = 0;
        foreach (DataRow dr in rstData.Rows)
            gTotal += Convert.ToDecimal(dr["MyTotal"]);

        Label lblTotal = (Label)GridView1.FooterRow.FindControl("lblMyTotal");
        lblTotal.Text = String.Format("{0:C2}", gTotal);

    }

    And result is this:

    enter image description here

    Now, even if you don’t adopt the above "concept" of totaling the data, which results in me freely displaying the column data as formatted currency values? (Which don’t total well at all!!).

    You STILL use the .findcontrol of each row, since the value to set or get of course is not a cells[] value, but a templated column, and that requires one to use the .FindControl method of that given row.

    Note also the 2nd trick used in above. Few realize that you can after loading a data table that you can add additional columns, and those columns can have a calculated value expression. Thus, I did not have to run code to multiply out the nights x night rate for each row total.

    So, when possible, try to process the data you are feeding to the GridView, and not process the "UI" rendering of the GridView, since often things like a "&nsp;" or things like currency formatting for such rendered HTML markup makes totaling that HTML data rather difficult.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search