Click here to Skip to main content
14,740,998 members
Please Sign up or sign in to vote.
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:

<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:

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
Updated 2-Mar-20 11:00am
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.

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

Here you've got an example how to achieve that:
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:
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
   
Here is what I did. I got it working.

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
OnRowDataBound="GridView1_RowDataBound1"
Property to the Gridview.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900