ASP DOT NET

Friday, June 22, 2018

Q. How calculate sub total n grid view in asp.net?

Create table and insert some record

 CREATE TABLE [dbo].[sales](
[stor_id] [char](4) NOT NULL,
[ord_num] [varchar](20) NOT NULL,
[ord_date] [datetime] NOT NULL,
[qty] [smallint] NOT NULL,
[payterms] [varchar](12) NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
 CONSTRAINT [UPKCL_sales] PRIMARY KEY CLUSTERED 
(
[stor_id] ASC,
[ord_num] ASC,
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'6380', N'6871', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 5, N'Net 60', N'BU1032')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'6380', N'722a', CAST(N'1994-09-13 00:00:00.000' AS DateTime), 3, N'Net 60', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7066', N'A2976', CAST(N'1993-05-24 00:00:00.000' AS DateTime), 50, N'Net 30', N'PC8888')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7066', N'QA7442.3', CAST(N'1994-09-13 00:00:00.000' AS DateTime), 75, N'ON invoice', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'D4482', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 10, N'Net 60', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(N'1992-06-15 00:00:00.000' AS DateTime), 40, N'Net 30', N'TC3218')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(N'1992-06-15 00:00:00.000' AS DateTime), 20, N'Net 30', N'TC4203')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(N'1992-06-15 00:00:00.000' AS DateTime), 20, N'Net 30', N'TC7777')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'N914008', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 20, N'Net 30', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'N914014', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 25, N'Net 30', N'MC3021')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(N'1993-05-29 00:00:00.000' AS DateTime), 20, N'Net 60', N'PS1372')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(N'1993-05-29 00:00:00.000' AS DateTime), 25, N'Net 60', N'PS2106')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(N'1993-05-29 00:00:00.000' AS DateTime), 15, N'Net 60', N'PS3333')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(N'1993-05-29 00:00:00.000' AS DateTime), 25, N'Net 60', N'PS7777')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'QQ2299', CAST(N'1993-10-28 00:00:00.000' AS DateTime), 15, N'Net 60', N'BU7832')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'TQ456', CAST(N'1993-12-12 00:00:00.000' AS DateTime), 10, N'Net 60', N'MC2222')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'X999', CAST(N'1993-02-21 00:00:00.000' AS DateTime), 35, N'ON invoice', N'BU2075')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'423LL922', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 15, N'ON invoice', N'MC3021')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'423LL930', CAST(N'1994-09-14 00:00:00.000' AS DateTime), 10, N'ON invoice', N'BU1032')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'P723', CAST(N'1993-03-11 00:00:00.000' AS DateTime), 25, N'Net 30', N'BU1111')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'QA879.1', CAST(N'1993-05-22 00:00:00.000' AS DateTime), 30, N'Net 30', N'PC1035')
ALTER TABLE [dbo].[sales]  WITH CHECK ADD FOREIGN KEY([stor_id])
REFERENCES [dbo].[stores] ([stor_id])
GO
ALTER TABLE [dbo].[sales]  WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])
GO

 now write your aspx page

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true"
onrowdatabound="GridView1_RowDataBound"  onrowcreated="GridView1_RowCreated">
<Columns>
  <asp:BoundField DataField="stor_id" HeaderText="stor_id" />
  <asp:BoundField DataField="ord_num" HeaderText="ord_num" />
  <asp:BoundField DataField="title_id" HeaderText="title_id" />
  <asp:TemplateField HeaderText="Quantity" ItemStyle-HorizontalAlign="Right">
<ItemTemplate>
<asp:Label ID="lblqty" runat="server" Text='<%# Eval("qty") %>' />
</ItemTemplate>
<FooterTemplate>
<div style="text-align: right;">
<asp:Label ID="lblTotalqty" runat="server"  Font-Bold=true  />
</div>
</FooterTemplate>
  </asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>

</html>


now write your source code

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

public partial class Default4 : System.Web.UI.Page
{
    int qtyTotal = 0;
    int grQtyTotal = 0;
    int storid = 0;
    int rowIndex = 1;
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        int i = 0;
        string sql = null;
        string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=123";
        sql = "select  distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty";
        SqlConnection connection = new SqlConnection(connetionString);
        connection.Open();
        SqlCommand command = new SqlCommand(sql, connection);
        adapter.SelectCommand = command;
        adapter.Fill(ds);
        adapter.Dispose();
        command.Dispose();
        connection.Close();
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString());
            int tmpTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "qty").ToString());
            qtyTotal += tmpTotal;
            grQtyTotal += tmpTotal;
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label lblTotalqty = (Label)e.Row.FindControl("lblTotalqty");
            lblTotalqty.Text = grQtyTotal.ToString();
        }
    }
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool newRow = false;
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") != null))
        {
            if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()))
                newRow = true;
        }
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") == null))
        {
            newRow = true;
            rowIndex = 0;
        }
        if (newRow)
        {
            GridView GridView1 = (GridView)sender;
            GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
            NewTotalRow.Font.Bold = true;
            NewTotalRow.BackColor = System.Drawing.Color.Gray;
            NewTotalRow.ForeColor = System.Drawing.Color.White;
            TableCell HeaderCell = new TableCell();
            HeaderCell.Text = "Sub Total";
            HeaderCell.HorizontalAlign = HorizontalAlign.Left;
            HeaderCell.ColumnSpan = 3;
            NewTotalRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.Text = qtyTotal.ToString();
            NewTotalRow.Cells.Add(HeaderCell);
            GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow);
            rowIndex++;
            qtyTotal = 0;
        }
    }

}



at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to to select duplicate rows from sql server?

 SELECT * FROM Recruitment WHERE Email IN (SELECT Email FROM Recruitment GROUP BY Email HAVING COUNT(*) > 1); WITH CTE AS (     SELECT   ...