Wednesday, 16 January 2013

Display Sum of Columns total in GridView Footer asp.net Vb.NET

Display Sum of Columns total in GridView Footer asp.net Vb.NET













Step 1) add gridview to aspx page

<style type="text/css">
.footerstyle
{
color:white;
background-color:teal;
font-family:'Comic Sans MS';
font-size:1.8em;

}
</style>
</head>
<body>
<form id="form1" runat="server">
<h1>Total Sum in the GridView asp.net C#</h1>
<asp:GridView ID="GridView1" runat="server"
ShowHeaderWhenEmpty="true"
EmptyDataText="No Data Found"
AutoGenerateColumns="false"
ShowFooter="true"
FooterStyle-BackColor="Red"
FooterStyle-CssClass="footerstyle">
<Columns>
<asp:TemplateField HeaderText="[ProductID]">
<InsertItemTemplate>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label runat="server"
Text='<%# Eval("ProductID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="[[ProductName]]">
<InsertItemTemplate>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server"
Text='<%# Eval("ProductName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="UnitPrice">
<InsertItemTemplate>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# Eval("UnitPrice") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="UnitsOnOrder">
<InsertItemTemplate>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# Eval("UnitsOnOrder") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label runat="server" Text="Sum"></asp:Label>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Total">
<InsertItemTemplate>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# CalTotal( Decimal.Parse(Eval("UnitsOnOrder").ToString())*Decimal.Parse(Eval("UnitPrice").ToString())) %>'>

</asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="Label3" runat="server"
Text='<%# GetSum() %>'></asp:Label>
</FooterTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>
</form>
</body>



Step 2) Calling Northwind SQL View and bind it to GridView

Store in the Datatable and then Bind Datatable to gridView
Private Sub Calling_SQL_VIew_using_adonet1()
Try
Dim conn As SqlConnection = New SqlConnection("Server = .\sqlexpress2012
database=northwindtrusted_connection=yes")


'SqlCommand sel = new SqlCommand(stroredProc);
Dim sel As SqlCommand = New SqlCommand()
sel.CommandType = CommandType.Text
sel.CommandText = "select * from [Northwind].[dbo].[Alphabetical list of products] where unitsonOrder >30"
sel.Connection = conn
Dim ad As SqlDataAdapter = New SqlDataAdapter(sel)

conn.Open()
ad.Fill(dt)

GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As SqlException
Response.Write(ex.Message)
End Try
End Sub
Step 3) Calculate Sum
Add two methods
1.Iterating columns and store it temp variable
2. Footer is called at the end, step 1 has total sum of the rows.

Public Function CalTotal(ByVal total As Decimal) As String
productsum = total
Return total.ToString()
End Function
Public Function GetSum() As String
Return productsum.ToString("C")
End Function


Step 5) Page Level Variables


Dim dt As DataTable = New DataTable()
Dim productsum As Decimal = 0


Run the Page


Tags
:Display Sum of Columns total in GridView Footer asp.net vb.net,asp:gridView,ado.net,sql views,datatable vb.net,total sum of the columns in Footer of GridView vb.net asp.net