Dear Friends, we create an collage based erp in which we want to display my student fee slip in gridview control so that we can print that slip.
I have table in which we store all class wise fee details
CREATE TABLE [dbo].[feemaster](
[feeId] [int] IDENTITY(1,1) NOT NULL,
[feetype] [nvarchar](50) NULL,
[feename] [nvarchar](50) NULL,
[classname] [nvarchar](50) NULL,
[duemonth] [nvarchar](50) NULL,
[feeamount] [decimal](15, 2) NULL,
[feeactive] [bit] NULL,
[feecatename] [nvarchar](50) NULL
)
below my data
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (1, N'One Time', N'Prospectus & Registration Fee', N'PRE NURSURY', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (13, N'One Time', N'Prospectus & Registration Fee', N'NURSURY', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (2, N'One Time', N'Prospectus & Registration Fee', N'KG', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (14, N'Annual', N'Maintenance Fee', N'PRE NURSURY', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (15, N'Annual', N'Maintenance Fee', N'PRE NURSURY', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (16, N'Annual', N'Maintenance Fee', N'NURSURY', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (17, N'Annual', N'Maintenance Fee', N'NURSURY', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (18, N'Annual', N'Maintenance Fee', N'KG', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (29, N'Annual', N'Maintenance Fee', N'KG', N'Apr-16', CAST(500.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (40, N'One Time', N'Prospectus & Registration Fee', N'PRE NURSURY', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (41, N'One Time', N'Prospectus & Registration Fee', N'NURSURY', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (42, N'One Time', N'Prospectus & Registration Fee', N'KG', N'Apr-16', CAST(300.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (53, N'One Time', N'Admission Fee', N'PRE NURSURY', N'Apr-16', CAST(1500.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (54, N'One Time', N'Admission Fee', N'NURSURY', N'Apr-16', CAST(1500.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (55, N'One Time', N'Admission Fee', N'KG', N'Apr-16', CAST(1500.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (68, N'One Time', N'Admission Fee', N'KG', N'Apr-16', CAST(1500.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (69, N'One Time', N'Admission
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (79, N'Annual', N'Computer Fee', N'KG', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (90, N'Annual', N'Computer Fee', N'PRE NURSURY', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (91, N'Annual', N'Computer Fee', N'NURSURY', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (92, N'Annual', N'Computer Fee', N'PRE NURSURY', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (93, N'Annual', N'Computer Fee', N'NURSURY', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (94, N'Annual', N'Computer Fee', N'KG', N'Apr-16', CAST(0.00 AS Decimal(15, 2)), 0, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (105, N'Annual', N'Examination Fee', N'KG', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (116, N'Annual', N'Examination Fee', N'PRE NURSURY', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (117, N'Annual', N'Examination Fee', N'NURSURY', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (118, N'Annual', N'Examination Fee', N'PRE NURSURY', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (119, N'Annual', N'Examination Fee', N'NURSURY', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (120, N'Annual', N'Examination Fee', N'KG', N'Apr-16', CAST(600.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (131, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Apr-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (132, N'Monthly', N'Tution Fee', N'NURSURY', N'Apr-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (133, N'Monthly', N'Tution Fee', N'KG', N'Apr-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (157, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (158, N'Monthly', N'Tution Fee', N'NURSURY', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (159, N'Monthly', N'Tution Fee', N'KG', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (170, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (171, N'Monthly', N'Tution Fee', N'NURSURY', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (172, N'Monthly', N'Tution Fee', N'KG', N'Mar-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (183, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (184, N'Monthly', N'Tution Fee', N'NURSURY', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (185, N'Monthly', N'Tution Fee', N'KG', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (196, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (197, N'Monthly', N'Tution Fee', N'NURSURY', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (198, N'Monthly', N'Tution Fee', N'KG', N'May-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (209, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (210, N'Monthly', N'Tution Fee', N'NURSURY', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (211, N'Monthly', N'Tution Fee', N'KG', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (222, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (223, N'Monthly', N'Tution Fee', N'NURSURY', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (224, N'Monthly', N'Tution Fee', N'KG', N'Jun-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (235, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (236, N'Monthly', N'Tution Fee', N'NURSURY', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (237, N'Monthly', N'Tution Fee', N'KG', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (248, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (249, N'Monthly', N'Tution Fee', N'NURSURY', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (250, N'Monthly', N'Tution Fee', N'KG', N'Jul-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (261, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (262, N'Monthly', N'Tution Fee', N'NURSURY', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (263, N'Monthly', N'Tution Fee', N'KG', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (274, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (275, N'Monthly', N'Tution Fee', N'NURSURY', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (276, N'Monthly', N'Tution Fee', N'KG', N'Aug-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (287, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (288, N'Monthly', N'Tution Fee', N'NURSURY', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (289, N'Monthly', N'Tution Fee', N'KG', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'NEW')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (300, N'Monthly', N'Tution Fee', N'PRE NURSURY', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (301, N'Monthly', N'Tution Fee', N'NURSURY', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
INSERT [dbo].[feemaster] ([feeId], [feetype], [feename], [classname], [duemonth], [feeamount], [feeactive], [feecatename]) VALUES (302, N'Monthly', N'Tution Fee', N'KG', N'Sep-16', CAST(550.00 AS Decimal(15, 2)), 1, N'OLD')
here we give some data for example purpose but we we have large amount data continue.. Oct-16,Nov-16,Dec-16,Jan-16,Feb-16,Mar-16
we have one chekbox list
like as follows
Apr-16
May-16
Jun-16
Jul-16
Aug-16
Sep-16
Oct-16
Nov-16
Dec-16
Jan-16
Feb-16
Mar-16
and one gridview now i want if a sutednt pay fee only aprl month then show apr month fee only in gridview if student pay more than one month like apr-16,may-16,Jun-16 etc. then this fee show in gridview i have code below which show all month fee at a time
public void bindgvduedate()
{
con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "readfeedetails";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@classname", Convert.ToString(ddlclassname.SelectedItem));
cmd.Parameters.AddWithValue("@feecatename", Convert.ToString(ddlstcategory.SelectedItem));
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table );
GridView1.DataSource= table;
GridView1.DataBind();
con.Close();
}
my stored procedure as follows
CREATE proc [dbo].[readfeedetails]
(
@classname nvarchar(50),
@feecatename nvarchar(50)
)
AS
BEGIN
select
*
from
(
select f.feename, f.feeamount,dd.duemonth
from feemaster as f
left outer join duedate as dd on dd.duemonth =f.duemonth where classname=@classname and feeactive=1 and feecatename=@feecatename
) as f
pivot
(
min(f.feeamount)
for f.duemonth in ([Apr-16],[May-16],[Jun-16],[Jul-16],[Aug-16],[Sep-16],[Oct-16],[Nov-16],[Dec-16],[Jan-16],[Feb-16],[Mar-16])
) as PIV
END
GO
What I have tried:
protected void chbxlstduedate_SelectedIndexChanged(object sender, EventArgs e)
{
GridView1.Columns[0].Visible = false;
GridView1.Columns[1].Visible = false;
GridView1.Columns[2].Visible = false;
GridView1.Columns[3].Visible = false;
GridView1.Columns[4].Visible = false;
GridView1.Columns[5].Visible = false;
GridView1.Columns[6].Visible = false;
GridView1.Columns[7].Visible = false;
GridView1.Columns[8].Visible = false;
GridView1.Columns[9].Visible = false;
GridView1.Columns[10].Visible = false;
GridView1.Columns[11].Visible = false;
for (i = 0; i < chbxlstduedate.Items.Count; i++)
{
if (chbxlstduedate.Items[i].Selected)
{
GridView1.Columns[i].Visible = true;
duemonth = Convert.ToString(chbxlstduedate.Items[i]);
bindgvfeehead();
}
}
}
but it will show only visible column value not sum of column value and row wise Total sum