How to sum a field in the data table

Question

SqlConnection cn = new SqlConnection(Data Source=.;Initial Catalog=Demo_eLogsVA4;Integrated Security=True); //Write a select Query cn.Open(); string q = Select Invoice_CodeAmountTaxNet Where Invoice_Code='INV991'; DataSet dset = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(q cn); DataTable dtable = new DataTable(); da.Fill(dtable); //Bind grid. GridView1.DataSource = dtable; DataRow[] dr = dtable.Select(Sum(Net)); GridView1.FooterRow.Cells[0].Text = Convert.ToString(dr[0]); GridView1.DataBind(); cn.Close(); the above is my gridbind method. My table name is dtable from that dtable i want to sum the amount and net field in the datatable(dtable) and where i want to include the sum code Help please

Jean Paul · Answer

There is no event for that. You can use the code soon after you fill the DataTable. (I believe your Net column is a numeric type - Also the table name is missing in the query) SqlConnection cn = new SqlConnection(Data Source=.;Initial Catalog=Demo_eLogsVA4;Integrated Security=True); //Write a select Query cn.Open(); string q = Select Invoice_CodeAmountTaxNet Where Invoice_Code='INV991'; DataSet dset = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(q cn); DataTable dtable = new DataTable(); da.Fill(dtable); object obj = dtable.Compute(SUM(Net) string.Empty); double value = (double)obj; cn.Close();

Suthish Nair · Answer

Rajkumar Why you asking different questions in a single thread? Also you already created duplicate threads of same questions.

Prabhu Raja · Answer

You can use PageSize property in GridView Properties. Set it to some value like 510 etc. And don't forget to handle PageIndex_Changing Event from Code Behind. protected void gridView_PageIndexChanging(object sender GridViewPageEventArgs e) { gridView.PageIndex = e.NewPageIndex; gridView.DataBind(); }

Rajkumar R · Answer

it working thaks a lot and one more question How to set a fixed paging size for grid view

Prabhu Raja · Answer

Can you try with this one dtable.Compute(Sum(Net) );

Rajkumar R · Answer

Now i m getting the following error Cannot perform '>' operation on System.String and System.Int32. 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.Data.EvaluateException: Cannot perform '>' operation on System.String and System.Int32. Source Error: Line 35: //Bind grid. Line 36: GridView1.DataSource = dtable; Line 37: object obj = dtable.Compute(Sum(Net) Net>0); Line 38: decimal value; Line 39: if (obj != null)

Prabhu Raja · Answer

Hi You may Try this object obj = dtable.Compute(Sum(Net) Net > 0); Decimal value; if ( obj != null) { value = (Decimal) obj; } { // obj is null. }

Rajkumar R · Answer

here i m getting error help me

Rajkumar R · Answer

once i added the above code i m getting the following error Invalid usage of aggregate function Sum() and Type: String. 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.Data.DataException: Invalid usage of aggregate function Sum() and Type: String. Source Error: Line 34: //Bind grid. Line 35: GridView1.DataSource = dtable; Line 36: object obj = dtable.Compute(SUM(Net) string.Empty); Line 37: double value = (double)obj; Line 38: //object sumobject = Convert.ToInt32(dtable.Compute(SUM(Net) Srate=144)); Source File: d:\WebSite1\Default10.aspx.cs Line: 36 This is my full code private void BindGrid() { // Set your connection String here SqlConnection cn = new SqlConnection(Data Source=.;Initial Catalog=Demo_eLogsVA4;Integrated Security=True); //Write a select Query cn.Open(); string q = Select Convert(nVarcharA.Rec_No) CRecA.Acc_Name DescrA.Quantity QtyConvert(nVarcharA.Sell_Rate) SRateSell_CName Sname  Sell_CCode SCCode Sell_CValue SCValue Convert(nVarcharA.Quantity*A.Sell_Rate*Sell_CValue) SAmount A.Service_Tax STaxConvert(nVarchar(A.Quantity*A.Sell_Rate*Sell_CValue)* A.Service_Tax/100)Tax_AmountConvert(nVarchar((A.Quantity*A.Sell_Rate*Sell_CValue)+(A.Quantity*A.Sell_Rate*Sell_CValue)* A.Service_Tax/100)) Net From eLogs_Invoice_Charges_Details A Where A.Invoice_Code='INV991'; DataSet dset = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(q cn); DataTable dtable = new DataTable(); da.Fill(dtable); //Bind grid. GridView1.DataSource = dtable; object obj = dtable.Compute(SUM(Net) string.Empty); double value = (double)obj; GridView1.DataBind(); GridView1.FooterRow.Cells[10].Text = value.ToString(); cn.Close(); }

Rajkumar R · Answer

sorry table name is eLogs_Invoice_Charges_Details and i need to show that total in the footer row in the gridview Here U missing the bind function thanks in advance

Rajkumar R · Answer

in which event i should add this code for getting total. When i use this code in bind function then it return the Invalid usage of aggregate function Sum() and Type: String.

Jean Paul · Answer

If you wanted the SUM of column in selected records you may use the Compute() method of datatable. object obj = dtable.Compute(SUM(Net) string.Empty); double value = (double)obj;