Checkbox select row(s) from one GridView (Table) to another back and forth

Question

I have two gridviews each with their own table.
I'm trying to make it so I can select a row(s) from GridViewA and move it to GridViewB (not copy).
Then be able to move the selected row(s) from GridViewB back to GridViewA.

GridViewA (populated with SqlDataSource1)

<asp:GridView ID="grdA" runat="server" CellPadding="4" AllowPaging="True" AutoGenerateColumns="False" ShowHeaderWhenEmpty="True" DataKeyNames="ID" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="Vertical" Width="75%">
            <AlternatingRowStyle BackColor="white" />
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True"/>
                <asp:BoundField DataField="Data1"HtmlEncode="false"/>
                <asp:BoundField DataField="Data2" HtmlEncode="false"/>
                <asp:BoundField DataField="Data3" HtmlEncode="false"/>
                <asp:TemplateField HeaderText="Select">
                    <ItemTemplate>
                        <asp:CheckBox ID="chkBox" runat="server" />
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
            </Columns>

GridViewB (populated with SqlDataSource2)

<asp:GridView ID="grdB" runat="server" CellPadding="4" AllowPaging="True" AutoGenerateColumns="False" ShowHeaderWhenEmpty="True" DataKeyNames="ID" DataSourceID="SqlDataSource2" ForeColor="#333333" GridLines="Vertical" Width="75%">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True"/>
                <asp:BoundField DataField="Data1"HtmlEncode="false"/>
                <asp:BoundField DataField="Data2" HtmlEncode="false"/>
                <asp:BoundField DataField="Data3" HtmlEncode="false"/>
                <asp:TemplateField HeaderText="Select">
                    <ItemTemplate>
                        <asp:CheckBox ID="chkBox2" runat="server" />
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
            </Columns>

Button to move row(s) from GridViewA to GridViewB. It works but I'm not sure how to delete the row from GridViewA after moving to GridViewB

protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string DataA, DataB, DataC;

            var connectionString = ConfigurationManager.ConnectionStrings["Database1"].ConnectionString;
            var insertStatement = "INSERT INTO SqlTableB (Data1, Data2, Data3) VALUES (@Data1, Data2, Data3)";
            using (var sqlConnection = new SqlConnection(connectionString))

                foreach (GridViewRow gRow in grdA.Rows)
                {
                    CheckBox cb = (gRow.FindControl("chkBox") as CheckBox);

                    if (cb.Checked)
                    {
                        DataA = Convert.ToString(gRow.Cells[1].Text);
                        DataB = Convert.ToString(gRow.Cells[2].Text);
                        DataC = Convert.ToString(gRow.Cells[3].Text);

                        using (var sqlCommand = new SqlCommand(insertStatement, sqlConnection))
                        {
                            sqlConnection.Open();
                            sqlCommand.Parameters.AddWithValue("@Data1", DataA);
                            sqlCommand.Parameters.AddWithValue("@Data2", DataB);
                            sqlCommand.Parameters.AddWithValue("@Data3", DataC);
                            sqlCommand.ExecuteNonQuery();
                            sqlConnection.Close();
                        }

                    }
                }
        }

Please let me know if I can make the issue more clear, thank you

Answer 1

I would approach the problem this way:

First, make sure you set the PK row id for the Grid (that way you dont have to include in the display, or markup).

So USE "datakeys" setting of the grid - this will help a lot.

So, say two grids, like this:

<div style="float:left;width: 40%">
    <asp:GridView ID="GridView1" runat="server"
        AutoGenerateColumns="False" DataKeyNames="ID" cssclass="table">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName" SortExpression="HotelName" />
            <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
            <asp:TemplateField HeaderText="Select" ItemStyle-HorizontalAlign="Center"> 
                <ItemTemplate>
                    <asp:CheckBox ID="chkSel" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>

        </Columns>
    </asp:GridView>
    <asp:Button ID="cmdMoveRight" runat="server" Text="Move->" style="float:right" CssClass="btn" OnClick="cmdMoveRight_Click" />
</div>

<div style="float:left;width: 40%;margin-left:10px">
    <asp:GridView ID="GridView2" runat="server"
        AutoGenerateColumns="False" DataKeyNames="ID" cssclass="table">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName" SortExpression="HotelName" />
            <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
            <asp:TemplateField HeaderText="Select" ItemStyle-HorizontalAlign="Center"> 
                <ItemTemplate>
                    <asp:CheckBox ID="chkSel" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>

        </Columns>
    </asp:GridView>
    <asp:Button ID="cmdMoveMoveLeft" runat="server" Text="<-Move"  CssClass="btn" OnClick="cmdMoveMoveLeft_Click" />
</div>

Code to load:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadGrids();
        }
    }
    void LoadGrids()
    {
        SqlCommand cmdSQL = new SqlCommand("SELECT * from tblHotelsA");
        GridView1.DataSource = MyRstP(cmdSQL);
        GridView1.DataBind();
        cmdSQL.CommandText = "SELECT * from tblHotelsB";
        GridView2.DataSource = MyRstP(cmdSQL);
        GridView2.DataBind();
    }
    public DataTable MyRstP(SqlCommand cmdSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

Ok, so now we have this:

Ok, now a button to move right, and one to move left. Code is:

   protected void cmdMoveRight_Click(object sender, EventArgs e)
    {
        // move records right A to table B
        string strSQL =
            "INSERT INTO tblHotelsB (FirstName, LastName, HotelName, Description) " +
            "SELECT FirstName, LastName, HotelName, Description FROM tblHotelsA " +
            "WHERE tblHotelsA.id = @ID";

        string strSQLDel = "DELETE FROM tblHotelsA WHERE ID = @ID";
        MoveRows(GridView1,strSQL,strSQLDel);
    }
    protected void cmdMoveMoveLeft_Click(object sender, EventArgs e)
    {
        // move records right A to table B
        string strSQL =
            "INSERT INTO tblHotelsA (FirstName, LastName, HotelName, Description) " +
            "SELECT FirstName, LastName, HotelName, Description FROM tblHotelsB " +
            "WHERE tblHotelsB.id = @ID";

        string strSQLDel = "DELETE FROM tblHotelsB WHERE ID = @ID";
        MoveRows(GridView2, strSQL,strSQLDel);
    }


    void MoveRows(GridView gv,string strSQL, string strSQLDel)
    {
        foreach (GridViewRow OneRow in gv.Rows)
        {
            CheckBox ckBox = OneRow.FindControl("cHkSel") as CheckBox;
            if (ckBox.Checked)
            {
                int PKID = (int)gv.DataKeys[OneRow.RowIndex]["ID"];
                SqlCommand cmdSQL = new SqlCommand(strSQL);
                cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = PKID;
                SqlRun(cmdSQL);
                // delte the row
                cmdSQL.CommandText = strSQLDel;
                SqlRun(cmdSQL);
            }
        }
        // now re-load both grids to reflect changes
        LoadGrids();
    }

    public void SqlRun(SqlCommand cmdSQL)
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                conn.Open();
                cmdSQL.ExecuteNonQuery();
            }
        }
    }