Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

How To: Do Calculations in GridView

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
13 Jul 2016CPOL3 min read 20.2K   349   6   3
A simple demo that explains how to do calculations in a GridView using JavaScript.

Introduction

I've seen many fresh developers in various forums struggling at implementing a simple calculation in GridView. Usually, they wanted to calculate the total amount while typing the value into a TextBox control and display the grand total value at the footer. As you may already know, this can be easily done using a server-side approach.

Let's take a quick review on how to implement a server-side approach calculation. Consider that we have this GridView markup below.

Server-Side Approach

Suppose we have this GridView markup:

ASP.NET
<asp:GridView ID="GridView1" runat="server" 
AutoGenerateColumns="false" ShowFooter="true">  
  <Columns>  
     <asp:BoundField DataField="ItemDescription" HeaderText="Item"/>  
     <asp:TemplateField HeaderText="Amount">  
       <ItemTemplate>  
         <asp:TextBox ID="TextBox1" runat="server"   
                      AutoPostBack="True"  
                      ontextchanged="TextBox1_TextChanged">    
         </asp:TextBox>  
       </ItemTemplate>  
     </asp:TemplateField>  
  </Columns>  
</asp:GridView>  

The markup above is pretty much simple. A GridView that contains a BoundField column for displaying the item, and a TemplateField for the amount column. Now, let's populate the grid with data from the database using the ADO.NET way. Here's the code block below:

C#
private string GetConnectionString(){  
        //Where MYDBConnection is the connetion string that was set up from the web config file  
        return System.Configuration.ConfigurationManager.ConnectionStrings
        ["MyDBConnection"].ConnectionString;  
}
  
// Method for Binding the GridView Control  
private void BindGridView(){  
    using (SqlConnection connection = new SqlConnection(GetConnectionString())) {  
            string sql = "SELECT * FROM YourTable";  
            using (SqlCommand cmd = new SqlCommand(sql, connection)) {  
                    connection.Open();  
                    using(var adapter = new SqlDataAdapter(cmd)){  
                          adapter.Fill(dt)  
                          if (dt.Rows.Count > 0){  
                               GridView1.DataSource = dt;  
                               GridView1.DataBind();  
                          }  
                    }  
           }  
    }  
}
  
//Bind GridView on initial postabck  
protected void Page_Load(object sender, EventArgs e){  
        if (!Page.IsPostBack)  
            BindGridView();   
}  

And here's the code block for calculating the total:

C#
//Calculate the Totals in the TextBox rows  
protected void TextBox1_TextChanged(object sender, EventArgs e){  
        double total = 0;  
        foreach (GridViewRow gvr in GridView1.Rows)  
        {  
            TextBox tb = (TextBox)gvr.Cells[1].FindControl("TextBox1");  
            double sum;  
            if(double.TryParse(tb.Text.Trim(),out sum))  
            {  
                total += sum;  
            }  
        }  
        //Display  the Totals in the Footer row  
        GridView1.FooterRow.Cells[1].Text = total.ToString();  
}  

That is simple! Running the code will now provide you a grid with enabled total amount calculation. Now there are certain cases where you may be required to implement a client-side calculation for whatever reasons. In this article, we will take a look at how we will implement it. Keep in mind that the server-side approach isn't really ideal to do such operation as the TextChanged event will trigger a server postback everytime to change/type a value in the TextBox.

The Client-Side Approach with JavaScript

To get started, let's setup the form. For simplicity, let's just setup the form like this:

ASP.NET
<asp:gridview ID="GridView1"  runat="server"  
ShowFooter="true" AutoGenerateColumns="false">  
    <Columns>  
        <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />  
        <asp:BoundField DataField="Description" HeaderText="Item Description" />  
        <asp:TemplateField HeaderText="Item Price">  
            <ItemTemplate>  
                <asp:Label ID="LBLPrice" runat="server" 
                Text='<%# Eval("Price","{0:C}") %>'></asp:Label>  
            </ItemTemplate>  
            <FooterTemplate>  
                <b>Total Qty:</b>  
            </FooterTemplate>  
        </asp:TemplateField>  
        <asp:TemplateField HeaderText="Quantity">  
            <ItemTemplate>  
                <asp:TextBox ID="TXTQty" runat="server" 
                onkeyup="CalculateTotals();"></asp:TextBox>  
            </ItemTemplate>  
            <FooterTemplate>  
                <asp:Label ID="LBLQtyTotal" runat="server" 
                Font-Bold="true" ForeColor="Blue" Text="0" ></asp:Label>       
                <b>Total Amount:</b>  
            </FooterTemplate>  
        </asp:TemplateField>  
        <asp:TemplateField HeaderText="Sub-Total">  
            <ItemTemplate>  
                <asp:Label ID="LBLSubTotal" runat="server" 
                ForeColor="Green" Text="0.00"></asp:Label>  
            </ItemTemplate>  
            <FooterTemplate>  
                <asp:Label ID="LBLTotal" runat="server" 
                ForeColor="Green" Font-Bold="true" Text="0.00"></asp:Label>  
            </FooterTemplate>  
        </asp:TemplateField>  
    </Columns>  
</asp:gridview> 

As you can see, there's really nothing fancy about the markup above. It just contains a standard GridView with BoundFields and TemplateFields on it. Just for the purpose of the demo, I will just use a dummy data for populating the GridView. Here's the code block:

C#
public partial class GridCalculation : System.Web.UI.Page  
{  
        private void BindDummyDataToGrid() {  
  
            DataTable dt = new DataTable();  
            DataRow dr = null;  
  
            dt.Columns.Add(new DataColumn("RowNumber", typeof(int)));  
            dt.Columns.Add(new DataColumn("Description", typeof(string)));  
            dt.Columns.Add(new DataColumn("Price", typeof(string)));  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 1;  
            dr["Description"] = "Nike";  
            dr["Price"] = "1000";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 2;  
            dr["Description"] = "Converse";  
            dr["Price"] = "800";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 3;  
            dr["Description"] = "Adidas";  
            dr["Price"] = "500";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 4;  
            dr["Description"] = "Reebok";  
            dr["Price"] = "750";  
            dt.Rows.Add(dr);  
            dr = dt.NewRow();  
            dr["RowNumber"] = 5;  
            dr["Description"] = "Vans";  
            dr["Price"] = "1100";  
            dt.Rows.Add(dr);  
            dr = dt.NewRow();  
            dr["RowNumber"] = 6;  
            dr["Description"] = "Fila";  
            dr["Price"] = "200";  
            dt.Rows.Add(dr);  
           //Bind the GridView  
            GridView1.DataSource = dt;  
            GridView1.DataBind();  
        }  

        protected void Page_Load(object sender, EventArgs e) {  
            if (!IsPostBack)  
                BindDummyDataToGrid();  
        }  
}

Running the page should result in something like this:

Image 1

Keep in mind that the client-side implementation is way different since you need to deal with the DOM elements to extract the controls, and you'll need to understand JavaScript syntax that is a bit complex compared to C# with server-side implementation. Now let's go into the “meat” of this demo, and that is the implementation of the client-side calculation. The main functionality includes the following:

  • Number validation
  • Formatting values into readable money format with separators
  • Calculation for sub-totals and total amount

Here is the JavaScript code (this should be placed inside the <head> section of your WebForm page):

JavaScript
<script type="text/javascript">  
        
        function CalculateTotals() {  
            var gv = document.getElementById("<%= GridView1.ClientID %>");  
            var tb = gv.getElementsByTagName("input");  
            var lb = gv.getElementsByTagName("span");  
  
            var sub = 0;  
            var total = 0;  
            var indexQ = 1;  
            var indexP = 0;  
            var price = 0;  
  
            for (var i = 0; i < tb.length; i++) {  
                if (tb[i].type == "text") {  
                    ValidateNumber(tb[i]);  
  
                    price = lb[indexP].innerHTML.replace
                    ("$", "").replace(",", "");  
                    sub = parseFloat(price) * parseFloat(tb[i].value);  
                    if (isNaN(sub)) {  
                        lb[i + indexQ].innerHTML = "0.00";  
                        sub = 0;  
                    }  
                    else {  
                        lb[i + indexQ].innerHTML = 
                        FormatToMoney(sub, "$", ",", "."); ;  
                    }  
                     
                    indexQ++;  
                    indexP = indexP + 2;  
  
                    total += parseFloat(sub);  
                }  
            }  
  
            lb[lb.length - 1].innerHTML = 
            FormatToMoney(total, "$", ",", ".");  
        }  
  
        function ValidateNumber(o) {  
            if (o.value.length > 0) {  
                o.value = o.value.replace(/[^\d]+/g, ''); //Allow only whole numbers  
            }  
        } 
 
        function isThousands(position) {  
            if (Math.floor(position / 3) * 3 == position) return true;  
            return false;  
        };  
  
        function FormatToMoney(theNumber, theCurrency, theThousands, theDecimal) {  
            var theDecimalDigits = Math.round((theNumber * 100) - (Math.floor(theNumber) * 100));  
            theDecimalDigits = "" + (theDecimalDigits + "0").substring(0, 2);  
            theNumber = "" + Math.floor(theNumber);  
            var theOutput = theCurrency;  
            for (x = 0; x < theNumber.length; x++) {  
                theOutput += theNumber.substring(x, x + 1);  
                if (isThousands(theNumber.length - x - 1) && (theNumber.length - x - 1 != 0)) {  
                    theOutput += theThousands;  
                };  
            };  
            theOutput += theDecimal + theDecimalDigits;  
            return theOutput;  
        }   
</script> 

Let's try to evaluate each JavaScript function above. The FormatToMoney() is a function that would format numeric values to money by passing the numeric value, the currency, thousands and decimal separators. The isThousand() function evaluates the value and returns Boolean. This function is used within the FormatToMoney() function to determine if the value is on thousand. The ValidateNumber() is a function that validates if the value supplied is a valid number. Finally, the CalculateTotals() is the main function that extracts each element from the GridView, calculates the values and sets the calculated values back to the GridView element that in this case is the sub-total and total amount.

Now, call the JavaScript CalculateTotals() function on “onkeyup” or “onkeypress” event like this:

ASP.NET
<ItemTemplate>  
     <asp:TextBox ID="TXTQty" runat="server" onkeyup="CalculateTotals();"></asp:TextBox> 
</ItemTemplate> 

Running the page will provide you with the following output.

On initial load:

Image 2

After entering values into the TextBox:

Image 3

That's it! I hope someone finds this post useful.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
A code monkey who loves to drink beer, play guitar and listen to music.

My Tech Blog: https://vmsdurano.com/
My Youtube Channel: https://www.youtube.com/channel/UCuabaYm8QH4b1MAclaRp-3Q

I currently work as a Solutions Architect and we build "cool things" to help people improve their health.

With over 14 years of professional experience working as a Sr. Software Engineer specializing mainly on Web and Mobile apps using Microsoft technologies. My exploration into programming began at the age of 15;Turbo PASCAL, C, C++, JAVA, VB6, Action Scripts and a variety of other equally obscure acronyms, mainly as a hobby. After several detours, I am here today on the VB.NET to C# channel. I have worked on Web Apps + Client-side technologies + Mobile Apps + Micro-services + REST APIs + Event Communication + Databases + Cloud + Containers , which go together like coffee crumble ice cream.

I have been awarded Microsoft MVP each year since 2009, awarded C# Corner MVP for 2015, 2016,2017 and 2018, CodeProject MVP, MVA, MVE, Microsoft Influencer, Dzone MVB, Microsoft ASP.NET Site Hall of Famer with All-Star level and a regular contributor at various technical community websites such as CSharpCorner, CodeProject, ASP.NET and TechNet.

Books written:
" Book: Understanding Game Application Development with Xamarin.Forms and ASP.NET
" Book (Technical Reviewer): ASP.NET Core and Angular 2
" EBook: Dockerizing ASP.NET Core and Blazor Applications on Mac
" EBook: ASP.NET MVC 5- A Beginner's Guide
" EBook: ASP.NET GridView Control Pocket Guide

Comments and Discussions

 
QuestionGreat Example! Pin
Member 1590233823-Jan-23 1:02
Member 1590233823-Jan-23 1:02 
QuestionGrowth in sales month-wise Pin
Member 1320122015-May-17 1:48
Member 1320122015-May-17 1:48 
AnswerRe: Growth in sales month-wise Pin
Vincent Maverick Durano17-May-17 14:59
professionalVincent Maverick Durano17-May-17 14:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.