Dear friends I am working on School Management Project in which we have one module fees management system.
I want to add column dynamically in gridview from database with remain other column also.
we have code in which we can add column dynamically but when we add new column in gridview old column has been remove automatically i want to add new columns dynamically with remain previous column.
protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
{
int i;
for (i = 0; i < CheckBoxList1.Items.Count; i++)
{
if (CheckBoxList1.Items[i].Selected)
{
DataTable myDataTable = new DataTable();
DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "name";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "April";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "May";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Jun";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "July";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "August";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Septmember";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "October";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Nobember";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "December";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "January";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "February";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "March";
myDataTable.Columns.Add(myDataColumn);
SqlConnection con = new SqlConnection(@"Server=JITENDRA\SQLEXPRESS;database=school; integrated security=true;");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "readfee_details";
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow row;
row = myDataTable.NewRow();
row[i] = reader.GetValue(i);
myDataTable.Rows.Add(row);
}
GridView1.DataSource = myDataTable;
GridView1.DataBind();
}
}
}
CREATE TABLE fee
(
fee_id INT IDENTITY(1,1) NOT NULL,
name NVARCHAR(50) NOT NULL,
PRIMARY KEY (fee_id)
)
CREATE TABLE fee_writeoff
(
fee_writeoff_id INT IDENTITY(1,1) NOT NULL,
fee_id INT,
date DATE NOT NULL,
PRIMARY KEY (fee_writeoff_id),
)
CREATE TABLE fee_plan
(
fee_plan_id INT IDENTITY(1,1) NOT NULL,
fee_id INT NOT NULL,
amount DECIMAL(10,0) NOT NULL,
)
INSERT INTO
fee(name)
VALUES
('Admission Fee'),
('Prospectus Registration Fee'),
('Maintenance Fee'),
('Computer_Fee'),
('Examination_Fee'),
('Tution_Fee')
INSERT INTO fee_writeoff
(fee_id, date)
VALUES
(1, '2000-04-01'),
(2, '2000-04-01'),
(3, '2000-04-01'),
(4, '2000-04-01'),
(5, '2000-04-01'),
(6, '2000-01-01'),
(6, '2000-02-01'),
(6, '2000-03-01'),
(6, '2000-04-01'),
(6, '2000-05-01'),
(6, '2000-06-01'),
(6, '2000-07-01'),
(6, '2000-08-01'),
(6, '2000-09-01'),
(6, '2000-10-01'),
(6, '2000-11-01'),
(6, '2000-12-01');
SELECT * FROM fee_writeoff
INSERT INTO fee_plan
(fee_id, amount)
VALUES
(1, 1500),
(2, 300.00),
(3, 500.00),
(4, 500.00),
(5, 600.00),
(6, 600.00)
create proc readfee_details
(
@fee_id int
)
as
begin
SELECT
f.name,
SUM(CASE MONTH(date) WHEN 4 THEN amount ELSE 0 END) AS 'April',
SUM(CASE MONTH(date) WHEN 5 THEN amount ELSE 0 END) AS 'May',
SUM(CASE MONTH(date) WHEN 6 THEN amount ELSE 0 END) AS 'June',
SUM(CASE MONTH(date) WHEN 7 THEN amount ELSE 0 END) AS 'July',
SUM(CASE MONTH(date) WHEN 8 THEN amount ELSE 0 END) AS 'August',
SUM(CASE MONTH(date) WHEN 9 THEN amount ELSE 0 END) AS 'September',
SUM(CASE MONTH(date) WHEN 10 THEN amount ELSE 0 END) AS 'October',
SUM(CASE MONTH(date) WHEN 11 THEN amount ELSE 0 END) AS 'November',
SUM(CASE MONTH(date) WHEN 12 THEN amount ELSE 0 END) AS 'December',
SUM(CASE MONTH(date) WHEN 1 THEN amount ELSE 0 END) AS 'January',
SUM(CASE MONTH(date) WHEN 2 THEN amount ELSE 0 END) AS 'February',
SUM(CASE MONTH(date) WHEN 3 THEN amount ELSE 0 END) AS 'March',
SUM(amount) AS 'Total'
FROM fee f
inner join fee_writeoff fw ON f.fee_id=fw.fee_id
inner join fee_plan fp on fw.fee_id= fp.fee_id
GROUP BY name
end
What I have tried:
protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
{
int i;
for (i = 0; i < CheckBoxList1.Items.Count; i++)
{
if (CheckBoxList1.Items[i].Selected)
{
DataTable myDataTable = new DataTable();
DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "name";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "April";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "May";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Jun";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "July";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "August";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Septmember";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "October";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "Nobember";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "December";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "January";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "February";
myDataTable.Columns.Add(myDataColumn);
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "March";
myDataTable.Columns.Add(myDataColumn);
SqlConnection con = new SqlConnection(@"Server=JITENDRA\SQLEXPRESS;database=school; integrated security=true;");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "readfee_details";
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow row;
row = myDataTable.NewRow();
row[i] = reader.GetValue(i);
myDataTable.Rows.Add(row);
}
GridView1.DataSource = myDataTable;
GridView1.DataBind();
}
}
}