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

Question

I have grid-view that loops every row but i am getting this error:

The variable name '@UserId' has already been declared. Variable names must be unique within a query batch or stored procedure

I have fixed the connection issue but now i see new issue. Please help.

    protected void btn_App_Click(object sender, EventArgs e)
    {

        using (SqlConnection myCon = new SqlConnection(strConnString))
        {
            using (SqlCommand myCmd = new SqlCommand())
            {
                myCmd.Connection = myCon;
                myCon.Open();

                foreach (GridViewRow row in myGV.Rows)
                {

                    if (row.RowType == DataControlRowType.DataRow)
                    {
                        Label UserId = row.FindControl("lblUsrID") as Label;
                        TextBox Start_Date = row.FindControl("txtStartDate") as TextBox;

                        TextBox End_Date = row.FindControl("txtEndDate") as TextBox;
                        CheckBox Reg_Appr = ((CheckBox)row.FindControl("txtchkUsrApp")) as CheckBox;

                        string myUserID = UserId.Text;


                             myCmd.CommandType = CommandType.Text;

                             myCmd.CommandText = "update myTable set Start_Date = @Start_Date, End_Date = @End_Date, Reg_Appr = @Reg_Appr where UserId = @UserId  ";
                             myCmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId.Text;
                             myCmd.Parameters.Add("@Start_Date", SqlDbType.VarChar).Value = Start_Date.Text;
                             myCmd.Parameters.Add("@End_Date", SqlDbType.VarChar).Value = End_Date.Text;

                             myCmd.Parameters.Add("@Reg_Appr", SqlDbType.Bit).Value = Reg_Appr.Checked;   
                             myCmd.ExecuteNonQuery();                             
                             SendActivationEmail(myUserID);
                    }
               }

                myCon.Close();


            }

       }


    }


    //send email
    private void SendActivationEmail(string myUserID)
    {
        // send email
    }

Answer 1

Move these statments

 myCmd.Connection = myCon;
 myCmd.CommandType = CommandType.Text;
 myCon.Open();

outside the loop.

Then you will only open the connection once, not once for each row.

As per your comments, you also have to do the following:

Below the open statement add the following:

var uParm = myCmd.Parameters.Add("@UserId", SqlDbType.VarChar);
var sdParm = myCmd.Parameters.Add("@Start_Date", SqlDbType.VarChar);
var edParm = myCmd.Parameters.Add("@End_Date", SqlDbType.VarChar);
var rParm = myCmd.Parameters.Add("@Reg_Appr", SqlDbType.Bit);

Then where you had the parameter add statement prior replace with this.

uParm.Value = UserId.Text;
sdParm.Value = Start_Date.Text;
edParm.Value = End_Date.Text;
rParm.Value = Reg_Appr.Checked; 

Answer 2

You're opening the connection during each loop. You need to close it before opening it again.

You will likely get an error in the other calls to Close(), though, so you need to check the state first to verify the connection is open.

if (row.RowType == DataControlRowType.DataRow)
                    {
                        Label UserId = row.FindControl("lblUsrID") as Label;
                        TextBox Start_Date = row.FindControl("txtStartDate") as TextBox;

                        TextBox End_Date = row.FindControl("txtEndDate") as TextBox;
                        CheckBox Reg_Appr = ((CheckBox)row.FindControl("txtchkUsrApp")) as CheckBox;

                        string myUserID = UserId.Text;

                             myCmd.Connection = myCon;
                             myCmd.CommandType = CommandType.Text;
                             myCon.Open();
                             myCmd.CommandText = "update myTable set Start_Date = @Start_Date, End_Date = @End_Date, Reg_Appr = @Reg_Appr where UserId = @UserId  ";
                             myCmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId.Text;
                             myCmd.Parameters.Add("@Start_Date", SqlDbType.VarChar).Value = Start_Date.Text;
                             myCmd.Parameters.Add("@End_Date", SqlDbType.VarChar).Value = End_Date.Text;

                             myCmd.Parameters.Add("@Reg_Appr", SqlDbType.Bit).Value = Reg_Appr.Checked;                                
                             SendActivationEmail(myUserID);
                             // added this line
                             myCon.Close();
                    }