Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Calculate Sum of DataTable Columns in Asp.Net using C#, VB.NET

Apr 20, 2015
Introduction

Here I will explain how to calculate sum of datatable column in
asp.net using c#, vb.net or calculate sum of columns in datatable in asp.net using c#, vb.net with example. By using datatable compute property we can calculate sum of columns without having any loops in asp.net using c#, vb.net.  


To calculate sum of columns in datatable we need to write the code like as shown below

C#.NET Code


DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("select * from productinfo", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
// dt is datatable and by using Compute Property we are calculating Sum of Price Column
string sum = dt.Compute("Sum(price)", "").ToString();

VB.NET Code


Dim dt As New DataTable()
Dim cmd As New SqlCommand("select * from productinfo", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
Dim sum= dt.Compute("Sum(price)", "").ToString()
If you want to check it in complete example first design one table priceinfo in your database as shown below

Column Name
Data Type
Allow Nulls
productid
Int(IDENTITY=TRUE)
Yes
productname
varchar(50)
Yes
price
varchar(50)
Yes
Once table created in database enter some dummy data to test application now open your aspx page and write the code like as shown below


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title> Display sum of columns total in gridview footer in asp.net using c#, vb.net </title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" Width="420px" OnPageIndexChanging="gvDetails_PageIndexChanging" ShowFooter="true">
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="productid" HeaderText="Product Id" />
<asp:BoundField DataField="productname" HeaderText="Product Name" />
<asp:BoundField DataField="price" HeaderText="Price" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
After completion of aspx page add following namespaces in codebehind

C# Code


using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

VB.NET Code


Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient

After completion of adding namespaces you need to write the code like as shown below

C# Code


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
}
}
protected void BindGridview()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from productinfo", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.FooterRow.Cells[1].Text = "Total Amount";
gvDetails.FooterRow.Cells[1].HorizontalAlign= HorizontalAlign.Right;
gvDetails.FooterRow.Cells[2].Text = dt.Compute("Sum(price)", "").ToString();
}
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}

VB.NET Code


Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select * from productinfo", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
gvDetails.FooterRow.Cells(1).Text = "Total Amount"
gvDetails.FooterRow.Cells(1).HorizontalAlign = HorizontalAlign.Right
gvDetails.FooterRow.Cells(2).Text = dt.Compute("Sum(price)", "").ToString()
End Using
End Sub
Protected Sub gvDetails_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvDetails.PageIndex = e.NewPageIndex
BindGridview()
End Sub
End Class
Demo




If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Parthi said...

Hi,
It given only total no of price column value but i want to know individual total value using page wise while selecting on page no. How can i do this and give any example?

With Regards,
Parthiban K.

Unknown said...

I got this error.
Invalid usage of aggregate function Sum() and Type: String.

Unknown said...

I got this error.
Invalid usage of aggregate function Sum() and Type: String.

Unknown said...

I got this error.
Invalid usage of aggregate function Sum() and Type: String.

indianentrepreneurpodcast said...

for sloving this error change the data type of price in database table from varchar(50)
to int.Then change this line
gvDetails.FooterRow.Cells(2).Text = CInt(dt.Compute("Sum(price)", ""))
u will get the total

Unknown said...

asp.et mvc5 code need

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.