ASP.NET: Adding a total to gridview

15 Feb

One difference between learning the framework and using the framework is rarely anything ever done the way you learned it. The GridView control is a perfect example; it is powerful and slick enough to get any developer switched to ASP.NET 2.0 by itself. For those unaware, GridView generates an html table based on a datasource (xml, sql, etc). In a few simple lines of code you can configure paging, editing, and sorting – lifting the CRUD off your back. Two things GridView does not do out of the box is inserting (which, in all fairness is better handled in other places) and totaling a column.

If you read Microsoft’s guide for adding a total field in the footer, it would sound straight forward. Microsoft gets close, but if you follow their method you will find if the user refresh the page the value of your total column reverts to zero. The reason is the event RowCreated is called on a GridView every time, but in a refresh RowDataBound is not called. To make this version work you would need to add the orderTotal variable to the session. For reference, here is Microsoft’s code example (note, if the layout of the book section is in your way, view the comments page):


private Decimal orderTotal = 0.0M;

void OrderGridView_RowCreated(Object sender,
GridViewRowEventArgs e) {

if (e.Row.RowType == DataControlRowType.Footer) {

Label total = (Label)e.Row.FindControl("OrderTotalLabel");
if (total != null) {
total.Text = orderTotal.ToString("c");
}
}
}

void OrderGridView_RowDataBound(Object sender,
GridViewRowEventArgs e) {

if (e.Row.RowType == DataControlRowType.DataRow) {
TableCell cell = e.Row.Cells[2];
DataBoundLiteralControl boundControl = (DataBoundLiteralControl)cell.Controls[0];
String itemTotal = boundControl.Text.Replace("$", "");
orderTotal += Convert.ToDecimal(itemTotal);
}
}

We can do better. The RowCreated is not needed, we can use RowType = Header and RowType = Footer. Further, if we stick to just RowDataBound, we won’t have to worry about a page refresh. Here is what I came up with:


protected decimal totalQTY, totalAMT;

protected void ProductList_RowDataBound(object sender,
GridViewRowEventArgs e) {

DataRowView tableData = e.Row.DataItem as DataRowView;

if (e.Row.RowType == DataControlRowType.Header) {
totalQTY = 0;
totalAMT = 0;
}
else if (e.Row.RowType == DataControlRowType.DataRow) {
totalQTY += (decimal)tableData["QTY"];
totalAMT += (decimal)tableData["AMOUNT"];
}
else if (e.Row.RowType == DataControlRowType.Footer) {
Label tQTY = e.Row.FindControl("TotalQTY") as Label;
Label tAMT = e.Row.FindControl("TotalAMT") as Label;
tQTY.Text = totalQTY.ToString();
tAMT.Text = totalAMT.ToString("C");
}
}

A few notes. First, I find it better to index the DataRowView by the column label over the index. The index is based on the DataSource, not the order of the columns in the GridView, so it can be confusing. Other thing of note, all objects have a ToString, so take advantage of it; in many cases like this one) you can pass in special formatting.

Last part is the same for both methods, adding in the label to the GridView footer. To do this, make the column you wish to display the total in (it doesn’t have to be the one being calculated) as a TemplateField (VS can convert any bound column to a TemplateField for you). Then add a asp:label tag, setting the ID to the value used in FindControl above.

  • Anonymous

    I believe this code gives you the total of the values in a column on the display page, i.e. if you have 10 pages, you see 10 different totals based upon what page you are on (shows the total of values on that particular page). I was wondering if there is anyway I can show the final total only (total from all pages), and not for each page. Thanks.

  • Stavros

    Very nice example Mike, very nice code ty.
    Just want to add that when you make column into TemplateField and column is a hyperlink with more than one dataItem from the db contributing to the navigateUrl field you might get a runtime error have a look at the following Microsoft url for fix: http://support.microsoft.com/kb/916443

  • Thanks for the warning. I’ve never run into that yet, probably because I tend to use a
    quick method in the code behind to generate the URL over using a Hyperlink field.

  • Carla

    Mike – I believe this code is going to do exactly what I need it to, however, I’m a relative newby to c# and coding, and for the life of me I can’t figure out what to put in the <footertemplate> area to display the totals once this has calculated them. I have two field names (PurchCost) and (RecdCost) and I want to display the totals for both of them. I’ve copied your code and replaced my field names, etc., but now I’m stuck. I want the values of totalPurch and totalRecd to display in the footer. What do I put for the text values?
    <FooterTemplate><asp:Label ID="Purch" runat="server" Text='<% totalPurch %>’ />
    <FooterTemplate><asp:Label ID="Recd" runat="server" Text='<% totalRecd %>’ />

  • Carla, in the footer template, leave the text value blank. In the section of my code behind for the footer (e.Row.RowType == DataControlRowType.Footer) replace the TotalQTY/TotalAMT strings with your label IDs Purch and Recd.

    I have another method you can look at as well, under the Code link at the top look for "Totaling a GridView part 2, the SQL"

  • Hansraj

    Can i get some sample of Auto generating Gridviews any now on same page
    through Code behind

  • Hansraj, I’m not sure what you mean – drop me an email with more detail (contact link at the bottom of the page).

  • jason

    Excellent tutorial so quick and easy to implement into a single GridView on the fly!

    Thanks

  • Linda

    Mike, thanks so much. Your code is so helpful.
    Your code shows the total of each page if AllowPaging is true . How to make a change if I want the total displayed only on the last page (not for each page).
    Thanks again.

  • hi Linda

    You can use the PageCount and PageIndex properties of a GridView to determine if you are on the last page (if PageCount – 1 == PageIndex).

  • Linda

    Thanks a lot for your response, Mike.
    I used PageCount and PageIndex as you said, the total now is displayed on the last page. However, the total is the sum of rows on the last page only. I’d like to have a total of every rows on the gridview.

  • Hi Linda

    A GridView (and Details View) only hold the rows they display – to access all the rows you’ll need to work with the dataSource. If you are working with an ObjectDataSource and TableAdapters I have another post that may help: ASP.NET: Totaling a GridView part 2, the SQL

    (other code posts are under the "Code" tab at the top)

  • Linda

    Thanks so much, Mike.

  • Praveen

    Hey Mike, Your code is exactly what i am looking for, but i keep getting this error. i am posting my code for , can please help me out. i am a newbie in C# and still in the learning process.heres the error:

    Specified cast is not valid.
    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.InvalidCastException: Specified cast is not valid.

    Source Error:

    Line 106:
    Line 107: // Add the total for an item (row) to the order total.
    Line 108: subto = (decimal)tableData["SubTotal"];
    Line 109:
    Line 110: }

    and my entire code is here:
    public void OrderGrid_RowDataBound(Object sender, GridViewRowEventArgs e)
    {
    DataRowView tableData = e.Row.DataItem as DataRowView;
    if (e.Row.RowType == DataControlRowType.Header)
    {
    subto = 0;
    }

    else if (e.Row.RowType == DataControlRowType.DataRow)
    {

    subto = (decimal)tableData["SubTotal"];

    }
    else if (e.Row.RowType == DataControlRowType.Footer)
    {
    Label tsub = (Label)e.Row.FindControl("SubTotalLabel");
    if (tsub != null)
    {
    tsub.Text = subto.ToString("c");
    }
    }

    }

    Thanks a lot, i appreciate it.

  • System.InvalidCastException: Specified cast is not valid means you are trying to convert an object to a type that doesn’t work – i.e. converting a string "abc" to a decimal. Whatever the value in tableData["SubTotal"] is, it cannot be casted to a decimal.

  • nikk

    Hey Mike
    Its only with the help of people like you, that I am still surviving in DotNet 🙂
    Thanks a lot!
    What if i need to get a Sum of BoundField Columns. Can I add a label control in the footer row of Bound Field.
    This may be a easy job but i’m very new in dis field. Tried searching on google found this link somewhat helpful "http://www.codeproject.com/KB/aspnet/How_to_create_custom_boun.aspx&quot;
    I would appreciate your help in this!

  • Hi nikk,

    I don’t think using the asp:BoundField you can set a FooterTemplate (could be wrong), but it’s east enough to turn a BoundField into a TemplateField bound to the same data column. In the GridView Columns menu, look for the "convert to template field" link on the bottom right to auto generate a template field from an exisitng boundfield.

  • sannia

    Hello!we are facing a problem in using grid view control in C# asp.net(developing web application). We want to add values in a grid i.e. we want that when the page is loaded an empty grid should be displayed and we can enter values or text(in rows and columns of grid)in it. and when we click the "save all" button all these records(contained in rows and columns of grid)should be saved in databases.Plz,kindly give us the C# code for this,not the asp code. We will be really thankful to you.

  • Hi Sannia,

    That’s pretty far beyond what can be explained in a comment – please checkout the tutorials on http://asp.net to help you learn more about creating custom user controls (which is what you’ll need to do for a multi-row edit grid).

  • Sandip

    i got the answer of my que .
    Thanks

  • David

    I am a complete beginner at .NET but I’m trying to use this piece of code and am having a hard time. I don’t understand what a ‘template field’ is. Let me say I have 2 Wrox books on ASP.NET and C#, both over 1000 pages and nether mentions template fields! I did convert the column I want to total to a ‘template’ but don’t see how to ‘assign a label to it’. I’m getting this runtime error:

    Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

    Source Error:

    Line 43: {
    Line 44: Label tSubsidy = e.Row.FindControl("TotalSubsidy") as Label;
    Line 45: tSubsidy.Text = TotalSubsidy.ToString("C");
    Line 46: }
    Line 47:

  • Hi David,

    The link in the article to Microsoft’s solution is to the TemplateField.FooterTemplate documentation, and includes examples that should help you see where the problem is.

  • NITIN ARORA

    excellent

  • likitha

    hi mike
    i used ur code but am facing an error tht says

    System.NullReferenceException: Object reference not set to an instance of an object.

    Source Error:

    Line 105: Label totsal = e.Row.FindControl("Totalsalary") as Label;
    Line 106: Label totrent = e.Row.FindControl("Totalrent") as Label;
    Line 107: totsal.Text = totalsalary.ToString();
    Line 108: totrent.Text = totalrent.ToString();

  • Bruce

    Hi Mike,

    Is there an elegant and simple way to do subtotals per date in a gridview?

    Thanks.

  • @Bruce – nothing I’d call elegant and simple. One method would be to alter the datasource before databinding, and insert additional rows that are the subtotals – this might be a challange to get the UI to look as you want.

    Depending on your app, you might look into the ReportViewer control – this doesn’t required SQL Server Reporting services, and can be embedded on an ASPX page but the support for non-IE browsers is horrendous.

    I’d also consider using nested controls, like nesting Repeaters. You will need to alter your data structure (basically, you’ll need to have a list of rows for the date groups, each with a list of the records).

    Last, third party controls like Telerik controls, can help here – I have no experience with using them but I think they can help you here.

  • Bruce

    Hi Mike,

    Thanks for the last comment on subtotals. I’ll try to do via code.

    Another question. Can you see why this doesn’t work? I get the following error:

    Compiler Error Message: CS1502: The best overloaded method match for ‘ASP.admin_custom_wmg_aspx.DaySpan(string, string)’ has some invalid arguments

    gridview in asp section:

    <asp:TemplateField HeaderText="Price">
    <ItemTemplate>
    <asp:Label ID="lblDays" runat="server" Text='<%# DaySpan( Eval("ShipDate"), Eval("DueDate") ) %>’ ></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>

    Script section:

    protected string DaySpan(string ShipDate, string DueDate)
    {
    DateTime Ship = DateTime.Parse(ShipDate);
    DateTime Due = DateTime.Parse(DueDate);

    TimeSpan span = Ship.Subtract(Due);
    return Convert.ToInt32(span).ToString();
    }

    Eval of Shipdate and DueDate by themselves are fine as dates.

    Thanks,
    Bruce.

  • Eval() returns an Object, not a String – you can change the above to Eval("ShipDate").ToString() or (String)Eval("ShipDate") or even Convert.ToString(Eval("ShipDate")) which would throw the least number of errors.

    If ShipDate is really a .Net DateTime object, Convert.ToDateTime(Eval("ShipDate")) and changing the method signature to f(DateTime, DateTime) will let you get rid of the DateTime.Parse calls.

  • Bruce

    Hi Mike,

    Thank you! The conversion of the datetime object to datetime again was key, eventhough it is already datetime object. Now everything works perfectly! Even threw in an exclude weekend function that works great.

    Final coding:

    protected string DaySpan(DateTime ShipDate, DateTime DueDate)
    {
    int days;

    TimeSpan span = DueDate.Date.Subtract(ShipDate.Date);
    days = span.Days;
    days = ExcludeWeekends(ShipDate, days );
    return Convert.ToString(days);
    }

    private int ExcludeWeekends(DateTime date, int days)
    {
    int daysWithSkip = days;
    for (int i = 0; i < daysWithSkip; i++)
    {
    if ((date.AddDays(i).DayOfWeek == DayOfWeek.Saturday) || (date.AddDays(i).DayOfWeek == DayOfWeek.Sunday ||
    date.AddDays(i).ToString("MM/dd/yyyy") == "12/25/2008" ||
    date.AddDays(i).ToString("MM/dd/yyyy") == "12/26/2008" ||
    date.AddDays(i).ToString("MM/dd/yyyy") == "01/01/2009" ||
    date.AddDays(i).ToString("MM/dd/yyyy") == "01/02/2009" ))
    {
    days -= 1;
    }
    }

    return days;
    }

    ———

    <asp:TemplateField HeaderText="Days Early">
    <ITEMSTYLE WRAP="False" width="100px"/>
    <ItemTemplate>
    <asp:Label ID="lblDays" runat="server" Text='<%# DaySpan( Convert.ToDateTime(Eval("ShipDate")) , Convert.ToDateTime(Eval("DueDate")) ) %>’ ></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>

  • amit

    hi
    amit here actually i have created gridview with database in this gridview i have taken INR and doller amount and convert INR in the other column with condition after that when i count that column and show added vale in footer but that give error how can add that value

  • Kanchan

    i want to show report in grid with some grouping option and also show TOTAL of groping.just like this

    CountryName Type Jan Feb Mar Apr…..
    India Teliphoni 45 0 10 25
    India Referecne 30 10 20 05

    TOTAL 75 10 30 30

    PLZ help me

  • Michael Potter

    Great, thanks.

  • James

    Hi, great tutorial, I have a template view that takes the subtotal of each item (price*quantity of each row), id ideally like to calculate my total based off that rather than access the database again and caluculate it from that. Theres no way to set an ID to a template field so how can i go about calculating from a template field rather than a databound field easily?

  • The code above isn’t hitting the database, it’s capturing the data as it’s bound to the view. The controls also have ID’s which are pulled from FindControl.

  • star

    Is there any simple way if I want to add groupwise totals in gridview ? i.e. on change of group,the total will be calculated & displayed on a row; after that the next group will be displayed & its total .. so on …
    Right now to achieve this I am inserting an empty row in Dataset on every group change & then on Databound event calculating the groupwise total.

    Thnx & Regards

  • Now, we can take advantage of Linq without using template field…May be one line code to do it. See following:
    http://goo.gl/FKgHs