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

Question

I get this error when saving 2 or more values from the gridview:

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

But the process goes through and it saves and updates the data. How can I remove this error?

Here is my code:

for(int i = 0; i < gvModal.Rows.Count; i++)
{ 
    string dateA = DateTime.Now.ToString("yyyy-MM-dd");

    Utility u = new Utility();
    string conn = u.connect();

    Label type = (Label)gvModal.Rows[i].Cells[1].FindControl("lbltype");
    Label model = (Label)gvModal.Rows[i].Cells[2].FindControl("lblModel");
    Label quantity = (Label)gvModal.Rows[i].Cells[3].FindControl("lblQuan");
    Label unit = (Label)gvModal.Rows[i].Cells[4].FindControl("lblUnit");
    int bal = Convert.ToInt32(gvModal.Rows[i].Cells[4].Text);
    int forIssue = 0;
    int forPO = 0;

    if (bal != 0)
    {
        forIssue = 1;
        forPO = 0;
    }
    else
    {
        forIssue = 0;
        forPO = 1;
    }

    SqlConnection connUser = new SqlConnection(conn);
    SqlCommand read = connUser.CreateCommand();

    string query = "INSERT INTO Mosef_Alert values (@Mosef_No, @Branch, @BU, @Dept, @Section, @Requisitioner, @Accepted, @Date_Accepted, @Reason, @MOSEF_Date, @type, @model, @quantity, @unit)";

    connUser.Open();
    read.CommandText = query;

    read.Parameters.Add(new SqlParameter("@Mosef_No", transIDs));
    read.Parameters.Add(new SqlParameter("@Branch", branch));
    read.Parameters.Add(new SqlParameter("@BU", bu));
    read.Parameters.Add(new SqlParameter("@Dept", dept));
    read.Parameters.Add(new SqlParameter("@Section", sec));
    read.Parameters.Add(new SqlParameter("@Requisitioner", requisitioner));
    read.Parameters.Add(new SqlParameter("@Accepted", accept));
    read.Parameters.Add(new SqlParameter("@Date_Accepted", dateA));
    read.Parameters.Add(new SqlParameter("@Reason", reason));
    read.Parameters.Add(new SqlParameter("@MOSEF_Date", lblDateFiled.Text));
    read.Parameters.Add(new SqlParameter("@type", type.Text));
    read.Parameters.Add(new SqlParameter("@model", model.Text));
    read.Parameters.Add(new SqlParameter("@quantity", quantity.Text));
    read.Parameters.Add(new SqlParameter("@unit", unit.Text));
    read.Parameters.Add(new SqlParameter("@For_PO", forPO));
    read.Parameters.Add(new SqlParameter("@For_Issuance", forIssue));

    read.ExecuteNonQuery();

    read.Parameters.Clear();
}

ExecuteUpdate(accept);
UpdateStatus();

System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type ='text/javascript'>");
sb.Append("alert('Records Updated');");
sb.Append("$('#editModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditHideModalScript", sb.ToString(), false);
}

public void UpdateStatus()
{
        Utility u = new Utility();
        string conn = u.connect();
        SqlConnection connUser = new SqlConnection(conn);
        SqlCommand read = connUser.CreateCommand();

        for(int i = 0; i < gvModal.Rows.Count; i++)
        {
            Label ItemID = (Label)gvModal.Rows[i].Cells[1].FindControl("lblID");
            Label stat = (Label)gvModal.Rows[i].Cells[8].FindControl("ItemStatus");
            int balance = Convert.ToInt32(gvModal.Rows[i].Cells[4].Text);
            string status;

            if(balance != 0)
            {
                status = "For Issuance";
            }
            else
            {
                status = "For PO";
            }

            string upd = "UPDATE ItemTransaction SET ItemStatus = '" + status +"' WHERE ID = '"+ ItemID.Text +"'";

            connUser.Open();
            read.CommandText = upd;

            read.Parameters.Clear();
            read.ExecuteNonQuery();
        }
    }
    public void ExecuteUpdate(int stat)
    {
        string upStat = null;
        if (stat == 1)
        {
            upStat = "Accepted";
        }
        else
        {
            upStat = "Denied";
        }
        string id = transID.Text;
        Utility u = new Utility();
        string conn = u.connect();
        SqlConnection connUser = new SqlConnection(conn);
        string up = "UPDATE MosefTransaction SET TransStatus = '"+ upStat +"' WHERE TransactionID = '"+ id +"'";
        connUser.Open();
        SqlCommand cm = new SqlCommand(up, connUser);
        //cm.Parameters.AddWithValue("@ID", id);
        //cm.Parameters.AddWithValue("@TransStatus", upStat);
        cm.Parameters.Clear();
        cm.ExecuteNonQuery();

        connUser.Close();
    }

Answer 1

First thing you have to notice that, your plain text query opens a wide door for SqlInjection. So use parameterized queries. Now let me come to your code, The problem is with the UpdateStatus method, In which you opened the connection while iteration and leave it without closing, so when you are trying to open the connection again in next iteration it throws the error. You can avoid this in many ways:

1. Close the connection in each iteration using connUser.Close()
2. You can use ConnectionState Enumeration to Check The connection state before opening a new connection. and open it only when the status is not open.

This can be done by using the following code:

if (connUser.State != ConnectionState.Open)
    connUser.Open();

3. Open the Connection outside the Loop and use the same through-out the loop. Clear the parameters in each iteration after executing the query.

For example consider the code:

using (SqlConnection connUser = new SqlConnection(conn))
{
    string upd = "UPDATE ItemTransaction SET ItemStatus = @status WHERE ID = @id";
    connUser.Open();
    SqlCommand commandSQL = connUser.CreateCommand();
    for (int i = 0; i < gvModal.Rows.Count; i++)
    {
        // Get values here using your code
        commandSQL.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
        commandSQL.Parameters.Add("@id", SqlDbType.VarChar).Value = ItemID.Text;
        commandSQL.ExecuteNonQuery();
        commandSQL.Parameters.Clear();                   
    }
}

Note: The best option is the third one, I prefer you to follow that, The remaining notes to your knowledge, which will help you in other situations;