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.
Description:
In previous articles I explained lock user after 3 attempts in asp.net, bind dropdownlist in asp.net gridview using dataset, validate textbox controls inside gridview in asp.net, cascading dropdownlist in gridview using asp.net, change gridview header dynamically in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to calculate sum of columns in datatable using asp.net in c#, vb.net.
In previous articles I explained lock user after 3 attempts in asp.net, bind dropdownlist in asp.net gridview using dataset, validate textbox controls inside gridview in asp.net, cascading dropdownlist in gridview using asp.net, change gridview header dynamically in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to calculate sum of columns in datatable using asp.net in 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 Email
|
|||
6 comments :
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.
I got this error.
Invalid usage of aggregate function Sum() and Type: String.
I got this error.
Invalid usage of aggregate function Sum() and Type: String.
I got this error.
Invalid usage of aggregate function Sum() and Type: String.
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
asp.et mvc5 code need
Note: Only a member of this blog may post a comment.