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

Question

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.

Nilesh Shah · 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 ... } }

Nilesh Shah · Answer

then I think you don't have grid view footer. did you specify ShowFooter="true"

Nilesh Shah · Answer

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

Nilesh Shah · Answer

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" );

Kasuni Abegunawardana · Answer

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

Kasuni Abegunawardana · Answer

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]

Kasuni Abegunawardana · Answer

I doesn't use asp:TemplateField to data grid only select data and fill it to the grid.

Kasuni Abegunawardana · Answer

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");

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

Answers (8)