How to calculate( sum) of the data grid column.

Kasuni Abegunawardana

8y1.1k.NET

Hi all,

I have retreive data to a data grid without sqldatasource.

private void BindGrid2()

{

string constr = (ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString);

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("SELECT * FROM OnlineDiary_tb where U_EmailAddress='" + Session["U_Email"].ToString() + "'and Date='"+ DateTime.Today.ToString()+"' ", con))

{

using (SqlDataAdapter sda = new SqlDataAdapter())

{

cmd.Connection = con;

sda.SelectCommand = cmd;

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

GridView2.DataSource = dt;

GridView2.DataBind();

}

Now want to calculate colunm data.

Thank you in advance.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah8y ago
Accepted answer
then you have to use grid row data bound event and do sum there and display in footer
// Class-level, running total variables...
decimal _totalUnitPrice = 0m;
int _totalNonNullUnitPriceCount = 0;
int _totalUnitsInStock = 0;
int _totalUnitsOnOrder = 0;

//grid row data bound event
protected void ProductsInCategory_RowDataBound(object sender,
  GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Reference the ProductsRow via the e.Row.DataItem property
        Northwind.ProductsRow product =
          (Northwind.ProductsRow)
          ((System.Data.DataRowView)e.Row.DataItem).Row;
        // Increment the running totals (if they are not NULL!)
        if (!product.IsUnitPriceNull())
        {
            _totalUnitPrice += product.UnitPrice;
            _totalNonNullUnitPriceCount++;
        }
        if (!product.IsUnitsInStockNull())
            _totalUnitsInStock += product.UnitsInStock;
        if (!product.IsUnitsOnOrderNull())
            _totalUnitsOnOrder += product.UnitsOnOrder;
    }
//then display in footer
else if (e.Row.RowType == DataControlRowType.Footer)
{
... Display the summary data in the footer ...
}
}
Kasuni Abegunawardana8y ago
I have already addeed footer. i didn't use a sqldatasource to fill the grid. I wrote a select and fill the gird i want show the calculate SUM of the each columns i don't care it should be footer or lable or anything
Nilesh Shah8y ago
then I think you don't have grid view footer.

did you specify ShowFooter="true"
Kasuni Abegunawardana8y ago
Yeah it has 12 columns. column 1-7 are string values.
[ID]
,[U_EmailAddress]
,[Food_Name]
,[Date]
,[Meal_time]
,[Serving_Size]
,[ServingQuantity]
,[TotalCalories]
,[TotalFat]
,[TotalProtien]
,[TotalCarbohydrates]
,[TotalCholesterol]
FROM [FoodCalorieCalculator_db].[dbo].[OnlineDiary_tb]
Nilesh Shah8y ago
Sum is just the text for lable, you can use total or anything you like

N2 is formatting option, you can remove it if you want

your error seems to be regarding the cell number you have used, do you have that many no. of columns/cells in your gridview?

first for testing if it works fine, assign the cell no. as 1 or 2 then see
Kasuni Abegunawardana8y ago
I doesn't use asp:TemplateField to data grid only select data and fill it to the grid.
Kasuni Abegunawardana8y ago
what is this sum and n2

Server Error in '/' Application.
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 136: decimal total = dt.AsEnumerable().Sum(row => row.Field("TotalCalories")); Line 137: Line 138: GridView2.FooterRow.Cells[8].Text = "Sum"; Line 139: Line 140: GridView2.FooterRow.Cells[9].Text = total.ToString("N2");
Nilesh Shah8y ago
which column you want to have sum calculation?

following is a sample, use as per your column name, dont forget to specify ShowFooter="true" in your gridview in your aspx

after the line where you have written GridView2.DataBind() write below code:
decimal total = dt.AsEnumerable().Sum(row => row.Field<decimal>("Price"));

                    GridView1.FooterRow.Cells[1].Text = "Sum";

                    GridView1.FooterRow.Cells[2].Text = total.ToString("N2");