Total Sum Values in Footer of GridView in VB.NET

In this article we will know how to find total sum of values present in database into a Gridview.The total sum will be appeared in the footer of the gridview. Remember in gridview addShowFooter="True".Add a TemplateField within this field add FooterTemplate.

Table structure

Table-in-SQL-Server.gif
Default.aspx code

 

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title>Total Count</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="GridView1" runat="server" DataKeyNames="empname" AllowPaging="true"

            PageSize="10" AutoGenerateColumns="False" CellPadding="4" GridLines="None" ShowFooter="True">

            <Columns>

                <asp:BoundField DataField="empname" HeaderText="Name" />

                <asp:BoundField DataField="salary" HeaderText="Salary" />

                <asp:TemplateField>

                    <FooterTemplate>

                        Total salary:Rs.

                        <%#totalsal()%>

                    </FooterTemplate>

                </asp:TemplateField>

            </Columns>

            <RowStyle BackColor="#ffffcc" ForeColor="Red" />

            <FooterStyle BackColor="#ccffcc" Font-Bold="True" ForeColor="#cc3300" />

            <HeaderStyle BackColor="Aqua" Font-Bold="True" ForeColor="#993300" />

        </asp:GridView>

    </div>

    </form>

</body>

</html>

 

Default.aspx.vb code

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class _Default

    Inherits System.Web.UI.Page

    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()

    Dim str As String

    Dim com As SqlCommand

    Dim sqlda As SqlDataAdapter

    Dim ds As DataSet

    Protected Sub Page_Load(ByVal sender As ObjectByVal e As System.EventArgs) HandlesMe.Load

        bind()

    End Sub

    Sub bind()

        Dim con As New SqlConnection(strConnString)

        con.Open()

        str = "select * from employee"

        com = New SqlCommand(str, con)

        sqlda = New SqlDataAdapter(str, con)

        ds = New DataSet

        sqlda.Fill(ds, "employee")

        GridView1.DataSource = ds

        GridView1.DataMember = "employee"

        GridView1.DataBind()

        con.Close()

    End Sub

    Function totalsal() As String

        Dim con As New SqlConnection(strConnString)

        str = "SELECT SUM(salary) from employee"

        com = New SqlCommand(str, con)

        con.Open()

        Dim Totalsalary As Decimal = Convert.ToDecimal(com.ExecuteScalar())

        con.Close()

        con.Dispose()

        com.Dispose()

        Return String.Format(Totalsalary)

    End Function

    Protected Sub GridView1_PageIndexChanging(ByVal sender As ObjectByVal e AsSystem.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging

        GridView1.PageIndex = e.NewPageIndex

        bind()

    End Sub

End Class

Output

Footer-in-GridView-in-VB.NET.gif


Similar Articles