Getting total values of a certain column from GridView
13,256
You must use the databinding events to sum the values. See this example and adapt to your needs:
private Decimal OrderTotal;
protected void GridView1_DataBinding(object sender, EventArgs e)
{
OrderTotal = 0.0M;
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//Keep adding the subtotal here
OrderTotal += Subtotal;
}
}
protected void GridView1_DataBound(object sender, EventArgs e)
{
//Set a control with the total sum
LabelOrderTotal.Text = OrderTotal.ToString("C");
}
Basically you keep adding the values in the RowDataBound
event and in the DataBound
event you set a label with the total sum. Alternatively, you can iterate over your grid in the DataBound
event and add everything up.
Related videos on Youtube
Author by
Brian
Updated on June 25, 2022Comments
-
Brian almost 2 years
Him
I am using a ASP.NET/VB.NET with SQL-Server-2012.
I have a a GridView column with 3 fields and 1 template field as shown below:
<asp:GridView ID="grdItems" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource3" Font-Names="Tahoma" ForeColor="#333333" GridLines="None"> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <Columns> <asp:BoundField DataField="item_name" HeaderText="Item" SortExpression="item_name" /> <asp:BoundField DataField="item_cost" HeaderText="Cost (inc. VAT)" SortExpression="item_cost" /> <asp:BoundField DataField="item_quantity" HeaderText="Quantity" SortExpression="item_quantity" /> <asp:TemplateField HeaderText="Sub-Total (inc. VAT)"> <ItemTemplate> <asp:Label ID="TextBox3" runat="server" Text='<%# Convert.ToInt32(Eval("item_quantity")) * Convert.ToDouble(Eval("item_cost"))%>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField> <FooterTemplate> <asp:Label ID="lblTotalPrice" runat="server" /> </FooterTemplate> </asp:TemplateField> </Columns> <EditRowStyle BackColor="#999999" /> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" HorizontalAlign="Center" VerticalAlign="Middle" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" HorizontalAlign="Center" VerticalAlign="Middle" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <SortedAscendingCellStyle BackColor="#E9E7E2" /> <SortedAscendingHeaderStyle BackColor="#506C8C" /> <SortedDescendingCellStyle BackColor="#FFFDF8" /> <SortedDescendingHeaderStyle BackColor="#6F8DAE" /> </asp:GridView>
Code behind:
Imports System.Data.SqlClient Imports System.Data Partial Class ProjectReport Inherits System.Web.UI.Page Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load Dim ProjectID = Session("project_id") Session("ProjectID") = ProjectID End Sub Protected Sub grdItems_RowDataBound(sender As Object, e As GridViewRowEventArgs) Dim totalPrice As Decimal = 0 If e.Row.RowType = DataControlRowType.DataRow Then Dim lblPrice As Label = DirectCast(e.Row.FindControl("lblTotalPrice"), Label) Dim price As Decimal = [Decimal].Parse(lblPrice.Text) totalPrice += price End If If e.Row.RowType = DataControlRowType.Footer Then Dim lblTotalPrice As Label = DirectCast(e.Row.FindControl("lblTotalPrice"), Label) lblTotalPrice.Text = totalPrice.ToString() End If End Sub End Class
DataBound()
Private Sub BindData() Dim conn As New SqlConnection("Data Source=BRIAN-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True") Dim query As New SqlCommand("SELECT Items.item_name, Items.item_cost, project_items.item_quantity FROM Items INNER JOIN project_items ON items.item_id = project_items.item_id WHERE project_items.project_id = @parameter", conn) query.Parameters.AddWithValue("@UserID", Session("ProjectID")) Dim da As New SqlDataAdapter(query, conn) da.SelectCommand = query Dim table As New DataTable() da.Fill(table) grdItems.DataSource = table grdItems.DataBind() End Sub
The last column (the template field) multiplies the quantity field with the cost field.
How do I calculate all the values (by adding) in the template field?
-
Brian about 11 yearsHi, I have follows the tutorial to no avail. I will update new code. Thanks.
-
nmat about 11 years@Brian You're missing the DataBound event. Read the example carefully
-
Brian about 11 yearsPlease see updated code. I'm getting an error in 'query' telling me that an SqlCommand cannot be converted to string