I must need to open two connections to execute two different queries?

Question

Currently I am executing two queries against two different tables and getting this exception,

The connection was not closed. The connection's current state is open.

This is what I am trying to do,

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
        string deleteStatement = "Delete from Table1 where userID=@userID";
        string deleteStatement2 = "Delete from Table2 where userID=@userID";

        using (SqlConnection connection = new SqlConnection(CS()))
        using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
        {
            connection.Open();
            cmd.Parameters.Add(new SqlParameter("@userID", userID));
            cmd.ExecuteNonQuery();

            using (SqlCommand cmd2 = new SqlCommand(deleteStatement2, connection))
            {
                connection.Open();
                cmd2.Parameters.Add(new SqlParameter("@userID", userID));
                int result2 = cmd2.ExecuteNonQuery();

                if (result2 == 1)
                {
                    BindData();
                }
            }
        }
    }

I am doing this because Table2 has userID as foreign key and must be deleted before deleting user actually

Answer 1

you are calling Open() twice. You can remove the second call Open().

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
    string deleteStatement = "Delete from Table1 where userID=@userID";
    string deleteStatement2 = "Delete from Table2 where userID=@userID";

    using (SqlConnection connection = new SqlConnection(CS()))
    using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
    {
        connection.Open();
        cmd.Parameters.Add(new SqlParameter("@userID", userID));
        cmd.ExecuteNonQuery();

        using (SqlCommand cmd2 = new SqlCommand(deleteStatement2, connection))
        {
            // connection.Open(); // remove this line
            cmd2.Parameters.Add(new SqlParameter("@userID", userID));
            int result2 = cmd2.ExecuteNonQuery();

            if (result2 == 1)
            {
                BindData();
            }
        }
    }
}