Nested Gridview redundant values

Question

I have 2 gridviews. Gridview1, Gridview2. Both have a common field "ID" When I Select the first row in Gridview1 (ID=1), the values for ID=1 from the Gridview2 shows up in the nested area, but the values show in EVERY row of Gridview1. When I get remove the "select", nothing shows from Gridview2. When I open the page, I want Gridview1 showing, and then the nested Gridview2 showing the contents of the individual ID from Gridivew1. I don't want a select to see everything, I just want both tables to show their data.

What I did:

• Add Gridview1
• select my columns to show using sql data source
• edit columns (added some boundfields and a template field to represent Gridview2)
• edit templates
• inserted Gridview2 in the template
• select my columns
• Where ID = Gridview1's control value of ID (Gridview1.SelectedValue)
• Set Gridview1's DataKeyNames to ID

Code behind:

public partial class _Webform : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            GridView gv2 = (GridView)e.Row.FindControl("GridView2");

            string connString = @"Data Source=SRV\\\SQLEXPRESS;Initial Catalog=Test_Database;Integrated Security=True";
            string query = "select * from dbo.task where PO_ID =" + e.Row.Cells[0].Text;

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            da.Dispose();

            gv2.DataSource = dt;
            gv2.DataBind();
        }
    }
}

HTML Markup:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PO_ID" DataSourceID="SqlDataSource1" EnableModelValidation="True">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="PO_ID" HeaderText="PO_ID" SortExpression="PO_ID" />
        <asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" />
        <asp:BoundField DataField="UserDate" HeaderText="UserDate" SortExpression="UserDate" />
        <asp:BoundField DataField="PO_Note" HeaderText="PO_Note" SortExpression="PO_Note" />
        <asp:BoundField DataField="Invoice_No" HeaderText="Invoice_No" SortExpression="Invoice_No" />
        <asp:TemplateField HeaderText="Tasks">
            <ItemTemplate>
                <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" EnableModelValidation="True">
                    <Columns>
                        <asp:BoundField DataField="PO_ID" HeaderText="PO_ID" SortExpression="PO_ID" />
                        <asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" />
                        <asp:BoundField DataField="UserDate" HeaderText="UserDate" SortExpression="UserDate" />
                        <asp:BoundField DataField="Cost" HeaderText="Cost" SortExpression="Cost" />
                        <asp:BoundField DataField="column1" HeaderText="column1" SortExpression="column1" />
                        <asp:BoundField DataField="Invoice_Date" HeaderText="Invoice_Date" SortExpression="Invoice_Date" />
                        <asp:BoundField DataField="Invoice_No" HeaderText="Invoice_No" SortExpression="Invoice_No" />
                    </Columns>
                </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Test_DatabaseConnectionString3 %>" SelectCommand="SELECT [PO_ID], [Username], [UserDate], [Cost], [_Status] AS column1, [Invoice_Date], [Invoice_No] FROM [Task] WHERE ([PO_ID] = @PO_ID)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="GridView1" Name="PO_ID" PropertyName="SelectedValue" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Test_DatabaseConnectionString3 %>" SelectCommand="SELECT [PO_ID], [Username], [UserDate], [Cost], [_Status] AS column1, [Invoice_Date], [Task_Note], [Invoice_No] FROM [Task] WHERE ([PO_ID] = @PO_ID)">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="GridView1" Name="PO_ID" PropertyName="SelectedValue" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Test_DatabaseConnectionString3 %>" SelectCommand="SELECT [PO_ID], [Username], [UserDate], [PO_Note], [Invoice_No] FROM [PO_Table]"></asp:SqlDataSource>
<br />

Answer 1

Your problem is that you are using one SqlDataSource for multiple GridViews. Since each GridView2 uses SqlDataSource2, they all end up with the same data - the data of the last GridView2 bound. SqlDataSources automatically bind when they are set in the markup like this: DataSourceID="SqlDataSource2".

MSDN on SqlDataSources:

The Select method is automatically called by controls that are bound to the SqlDataSource when their DataBind method is called. If you set the DataSourceID property of a data-bound control, the control automatically binds to data from the data source, as required.

What this means is that when you change the where clause for SqlDataSource2 in GridView1_RowDataBound, all your GridView2s will bind with that new filter.

To fix this, don't use a SqlDataSource for GridView2. Instead, bind them individually, with their own set of data.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        GridView gv2 = (GridView)e.Row.FindControl("GridView2");

        string connString = @"your connection string here";
        string query = "select * from dbo.task where PO_ID = " + e.Row.Cells[0].Text;

        SqlConnection conn = new SqlConnection(connString);        
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        conn.Close();
        da.Dispose();

        gv2.DataSource = dt;
        gv2.DataBind();
    }
}

Again, I need to stress caution for SQL Injection when doing it this way.