How to calculate databound values in gridview in ASP.NET?

Question

0.00/5 (No votes)

See more:

I have created a Databound Gridview with 9 columns. The most of the columns contain money values. I have set 'ShowFooter' to 'True' and have styled my footer however there is currently no data in the footer. What I'm looking to do is calculate the total of all the money columns and put the value within the footer at the bottom of their column - I'm not sure how to go about this so any assistance would be much appreciated.

Thank you

Here is the HTML Code:

ASP.NET

<pre><head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style1 {
            width: 100%;
        }
        .auto-style2 {
            font-size: large;
        }
        .auto-style4 {
            width: 391px;
        }
        .auto-style5 {
            width: 247px;
        }
        .auto-style7 {
            width: 727px;
        }
        .auto-style9 {
            width: 727px;
            text-align: left;
        }
        .auto-style10 {
            text-align: center;
        }
    </style>
</head>
<body background="Images/mixed-marble_dArc2008.jpg" style="height: 68px">
    <form id="form1" runat="server">
        <div>
            <table class="auto-style1">
                <tr>
                    <td class="auto-style7">
                        <asp:Label ID="lblPBO" runat="server" CssClass="auto-style2" Text="Percentile Break Out"></asp:Label>
                        
                        <asp:TextBox ID="TextBoxUSERID" runat="server"></asp:TextBox>
                        <asp:TextBox ID="TextBoxSCENARIO" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSourceDEP" DataTextField="DEPARTMENT" DataValueField="DEPARTMENT" Width="220px" AutoPostBack="True">
                        </asp:DropDownList>
                    </td>
                    <td> </td>
                </tr>
                <tr>
                    <td class="auto-style7"> </td>
                    <td>
                        <asp:SqlDataSource ID="SqlDataSourceDEP" runat="server" ConnectionString="<%$ ConnectionStrings:PERCENTAGE_CALC_DBConnectionString %>" SelectCommand="SELECT DISTINCT [DEPARTMENT] FROM [EMPLOYEE_BASE_DATA]"></asp:SqlDataSource>
                    </td>
                    <td> </td>
                </tr>
                <tr>
                    <td class="auto-style9">
                        <asp:Label ID="lblEmploy" runat="server" Text="Number of Employees"></asp:Label>
                       
                        <asp:Label ID="lblENC" runat="server"></asp:Label>
                        </td>
                    <td>
                         </td>
                    <td> </td>
                </tr>
            </table>
            <br />
            <table class="auto-style1">
                <tr>
                    <td>
                        <div class="auto-style10">
                        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" ShowFooter="True" GridLines="None" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSourceBreakDown" Width="1612px">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <Columns>
                                <asp:TemplateField><HeaderTemplate></HeaderTemplate><itemtemplate><%#(Container.DataItemIndex+1)%></itemtemplate></asp:TemplateField>
                                <asp:TemplateField><FooterTemplate>Total</FooterTemplate></asp:TemplateField>
                                <asp:BoundField DataField="BASE_SALARY" DataFormatString="{0:C}" HeaderText="CURRENT BASE" SortExpression="BASE_SALARY" />
                                <asp:BoundField DataField="SUPPLIMENT" DataFormatString="{0:C}" HeaderText="CURRENT SUPPLIMENT" SortExpression="SUPPLIMENT" />
                                <asp:BoundField DataField="TOTAL_SALARY" DataFormatString="{0:C}" HeaderText="CURRENT TOTAL" SortExpression="TOTAL_SALARY" />
                                <asp:BoundField DataField="CURRENT_BASE_PCT" DataFormatString="{0:P0}" HeaderText="CURRENT %" SortExpression="CURRENT_BASE_PCT" />
                                <asp:BoundField DataField="PROPOSED_X" DataFormatString="{0:C}" HeaderText="PROPOSED BASE" SortExpression="PROPOSED_X" />
                                <asp:BoundField DataField="Y_SUPPLEMENT" DataFormatString="{0:C}" HeaderText="PROPOSED SUPPLEMENT" SortExpression="Y_SUPPLEMENT" />
                                <asp:BoundField DataField="TOTAL_COMPENSATION" DataFormatString="{0:C}" HeaderText="PROPOSED TOTAL" SortExpression="TOTAL_COMPENSATION" />
                                <asp:BoundField DataField="NEW_TOTAL_PCT" DataFormatString="{0:P0}" HeaderText="PROPOSED %" SortExpression="NEW_TOTAL_PCT" />
                                <asp:BoundField DataField="DEPARTMENT" HeaderText="DEPARTMENT" SortExpression="DEPARTMENT" />
                                
                            </Columns>
                            <EditRowStyle BackColor="#999999" />
                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
                            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                            <SortedAscendingCellStyle BackColor="#E9E7E2" />
                            <SortedAscendingHeaderStyle BackColor="#506C8C" />
                            <SortedDescendingCellStyle BackColor="#FFFDF8" />
                            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                        </asp:GridView>
                        </div>
                        <asp:SqlDataSource ID="SqlDataSourceBreakDown" runat="server" ConnectionString="<%$ ConnectionStrings:PERCENTAGE_CALC_DBConnectionString %>" SelectCommand="SELECT [BASE_SALARY], [SUPPLIMENT], [TOTAL_SALARY], [CURRENT_BASE_PCT], [PROPOSED_X], [Y_SUPPLEMENT], [TOTAL_COMPENSATION], [NEW_TOTAL_PCT], [DEPARTMENT] FROM [EMPLOYEE_BASE_DATA] WHERE (([DEPARTMENT] = @DEPARTMENT) AND ([SCENARIO] = @SCENARIO) AND ([MATCHED] = @MATCHED))">
                            <SelectParameters>
                                <asp:ControlParameter ControlID="DropDownList1" Name="DEPARTMENT" PropertyName="SelectedValue" Type="String" />
                                <asp:ControlParameter ControlID="TextBoxSCENARIO" Name="SCENARIO" PropertyName="Text" Type="String" />
                                <asp:Parameter DefaultValue="1" Name="MATCHED" Type="String" />
                            </SelectParameters>
                        </asp:SqlDataSource>
                    </td>
                    <td> </td>
                </tr>
            </table>
            <br />
            <br />
            <table class="auto-style1">
                <tr>
                    <td class="auto-style4"> </td>
                    <td class="auto-style5"> </td>
                    <td> </td>
                </tr>
                <tr>
                    <td class="auto-style4">
                         </td>
                    <td class="auto-style5">
                         </td>
                    <td> </td>
                </tr>
            </table>
        </div>
    </form>
</body background>
</html>

Here is the Code Behind:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;

public partial class PercentileBreakOut : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {
            Bind();
        }


        SqlConnection con99 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PERCENTAGE_CALC_DBConnectionString"].ConnectionString);
        con99.Open();

        TextBoxUSERID.Text = Session["USER_ID"].ToString();


        SqlCommand scmd = new SqlCommand("Select SCENARIO from EMPLOYEE_BASE_DATA where SCENARIO = '" + TextBoxUSERID.Text + "'", con99);
        SqlDataReader dr = scmd.ExecuteReader();

        if (dr.Read())
        {
            TextBoxSCENARIO.Text = dr["SCENARIO"].ToString();
        }

        dr.Close();
        con99.Close();




        SqlConnection con11 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PERCENTAGE_CALC_DBConnectionString"].ConnectionString);
        con11.Open();

        SqlCommand scmd11 = new SqlCommand("Select count(MATCHED) from EMPLOYEE_BASE_DATA where MATCHED = '1' AND SCENARIO = '" + TextBoxSCENARIO.Text + "'", con11);

        object count = scmd11.ExecuteScalar();
        lblENC.Text = count.ToString();

        con11.Close();
    }

    private void Bind()
    {
        this.GridView1.DataBind();
    }


    //int total = 0;
    protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
    {
        //int sum = Convert.ToInt32("SUM(BASE_SALARY)");


        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            total += Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "CURRENT BASE"));
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {

            Label lblamount = (Label)e.Row.FindControl("lblTotal");

            //lblamount.Text = "Total Value is : " + total.ToString();
        }







    }
}

What I have tried:

I have tried other codes but that are adding a data table and nothing works.

Posted 2-Mar-20 4:06am

Computer Wiz99

Updated 2-Mar-20 10:00am

Add a Solution

Comments

Richard Deeming 2-Mar-20 10:50am

SqlCommand scmd = new SqlCommand("Select SCENARIO from EMPLOYEE_BASE_DATA where SCENARIO = '" + TextBoxUSERID.Text + "'", con99);
SqlCommand scmd11 = new SqlCommand("Select count(MATCHED) from EMPLOYEE_BASE_DATA where MATCHED = '1' AND SCENARIO = '" + TextBoxSCENARIO.Text + "'", con11);

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Computer Wiz99 2-Mar-20 10:53am

Thanks for the SQL Injection part. Do you know how to help me with my issue at hand?

Maciej Los 2-Mar-20 12:13pm

My virtual 5!

Computer Wiz99 2-Mar-20 12:14pm

I'm sorry, What?

Maciej Los 2-Mar-20 12:20pm

This means that Richard's comment is very good and contains lots of valuable remarks.

Computer Wiz99 2-Mar-20 13:10pm

Okay, thanks. I understand the way you showed me but will this work for a Gridview that is populated from a database?

Maciej Los 2-Mar-20 13:14pm

Yes, it does. You need to make some changes to the code i've showed you.

2 solutions

Solution 1

First of all, please comply to the Richard's instructions.

Here you've got an example how to achieve that:

C#

DataTable dt = new DataTable();
string commandtext = "Select SCENARIO from EMPLOYEE_BASE_DATA where SCENARIO =@scenario";

using(SqlConnection connection = new SqlConnection("connection_string_here"))
{
    connection.Open();
    using(SqlCommand command = new SqlCommand(commandtext, connection)
    {
        command.Parameters.Add("@scenario", SqlDbType.NVarChar){Value = TextBoxUSERID.Text};
        using(SqlDataReader reader = command.ExecuteReader())
            dt.Load(reader);
    }
    connection.Close();
}

If you would like to get sum column, take a look at below code:

C#

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
    new DataColumn("Money1", typeof(double)),
    new DataColumn("Money2", typeof(double)),
    new DataColumn("Money3", typeof(double)),
    new DataColumn("Money4", typeof(double))
});

dt.Rows.Add(new object[]{5, 12, 3.3, 8.8});
dt.Rows.Add(new object[]{8.2, 10.4, 3.3, 8.8});
dt.Rows.Add(new object[]{9.5, 8.2, 5.3, 8.6});
dt.Rows.Add(new object[]{6.4, 7.6, 4.3, 3.8});

var sumOfColumn1 = dt.AsEnumerable()
    .Sum(x=> x.Field<double>("Money1"));
//now, you can use sumOfColumn1 variable

Posted 2-Mar-20 6:44am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Computer Wiz99 · Accepted Answer · 2020-03-02T10:01:00

Here is what I did. I got it working.

C#

protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
   {
      if(e.Row.RowType == DataControlRowType.DataRow)
       {
           totalsalary += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "BASE_SALARY"));
       }
      else if(e.Row.RowType == DataControlRowType.Footer)
       {
           e.Row.Cells[2].Text = String.Format("{0:C}", totalsalary);
       }

   }

I also had to add

HTML

OnRowDataBound="GridView1_RowDataBound1"

Property to the Gridview.