Problem in nested gridview

[cmrhema]

Hi,

I have a gridview problem while binding more than two gridviews.

I am giving a sample scenario of what I have done so far

I have two tables

First table: Department , having columns DeptId and DeptName

10 Accounts
20 Software

Second Table: Emp having columns EmpId, EmpName,Salary,
DeptID.

101 aaa 100 10
101 aaa 200 10
101 aaa 300 10
202 bb 101 20
202 bb 102 20
102 cc 100 10
201 dd 103 20


I am supposed to show first all the employees ordered by their deptname.

Also I am supposed to show the sum of each employees salary and sum of the entire department's salary

So the above values should appear as below

101 aaa 100 10
101 aaa 200 10
101 aaa 300 10
sum of emp 101 is 600
102 cc 100 10
sum of emp 102 is 100
SUM OF DEPT 10 IS 700


202 bb 101 20
202 bb 102 20
sum of emp 202 is 203
201 dd 103 20
sum of emp 202 is 103
SUM OF DEPT 20 IS 306


I am able to fetch sum of dept salary but not of employees

I have tried as below

My Design Page
.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
                <Columns>
                <asp:BoundField DataField="DeptID" />
                    <asp:TemplateField>
                        <ItemTemplate>
 
                                <asp:GridView ID="GridView2"  AutoGenerateColumns="false" runat="server">
                                    <Columns>
                                        <asp:BoundField DataField="EmpId" />
                                        <asp:BoundField DataField="EmpName" />
                                        <asp:BoundField DataField="Salary" />
                                    </Columns>
                                </asp:GridView>
                                <asp:GridView ID="GridView3"  AutoGenerateColumns="false" runat="server">
                                    <Columns>
                                        <asp:BoundField DataField="SumSalary" />
                                    </Columns>
                                </asp:GridView>
 
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

My Codebehind page is as below

 SqlConnection MyCon=new SqlConnection("server=ABC-415D0247602\\SQLEXPRESS; database=Employee ;integrated security=true;connection timeout=600");
 
    protected void Page_Load(object sender, EventArgs e)
    {
        MyCon.Open();
        SqlDataAdapter MyDa = new SqlDataAdapter("select * from department", MyCon);
        DataSet MyDs = new DataSet();
        MyDa.Fill(MyDs);
        DataTable MyDt = new DataTable();
        GridView1.DataSource = MyDs.Tables[0];
        GridView1.DataBind();
 
 
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string j = e.Row.Cells[0].Text;
 
            GridView GridView2 = (GridView)e.Row.FindControl("GridView2");
            SqlDataAdapter MyDa1 = new SqlDataAdapter("select * from Emp where deptid =" +Convert.ToInt16(j), MyCon);
            DataSet MyDs1 = new DataSet();
            MyDa1.Fill(MyDs1);
            DataTable MyDt1 = new DataTable();
            GridView2.DataSource = MyDs1.Tables[0];
            GridView2.DataBind();
 
            GridView GridView3 = (GridView)e.Row.FindControl("GridView3");
            SqlDataAdapter MyDa2 = new SqlDataAdapter("select sum(salary) as SumSalary from Emp where deptid =" + Convert.ToInt16(j), MyCon);
            DataSet MyDs2 = new DataSet();
            MyDa2.Fill(MyDs2);
            DataTable MyDt2 = new DataTable();
            GridView3.DataSource = MyDs2.Tables[0];
            GridView3.DataBind();
        }
    }

I do not know where to place the sum of the employee's salary


Thanks in advance



Regards

cmrhema