nad nad - 1 month ago 11
ASP.NET (C#) Question

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

I have a set of code inside

try..catch
block which is giving me error as


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


try
{
MainEnqMkey = InsertUpdateDelete.InsertUpdateDeleteCls.InsertUpdateDelete_oracle(HidMode.Value, Convert.ToInt32(HidMKey.Value), "XXCUS.XXACL_PN_AGRMNT_MST", "MKEY", "AM", sb.ToString());

if (Request.QueryString["Mode"] != "M")
{
ObjPriCon.Open();
OracleCommand ObjPriCmd_seq2 = new OracleCommand("select XXCUS.XXACL_PN_AGRMNT_MST_SEQ.nextval from dual", ObjPriCon); // generating ref no here.
string strRefNo = Convert.ToString(ObjPriCmd_seq2.ExecuteOracleScalar());

Response.Write("<script language=javascript> alert('Your Document No is : " + strRefNo.ToString() + "')</script>");

OracleCommand cmd2 = new OracleCommand("Update XXCUS.XXACL_PN_AGRMNT_MST set DOC_NO = '" + strRefNo.ToString() + "' where MKEY = '" + MainEnqMkey + "'", ObjPriCon); // Updating ref no here

OracleCommand cmd3 = new OracleCommand("Update xxcus.xxacl_pn_farming_mst set LAST_DOC_NO = '" + txtdocno.Value + "' where project_id = '" + ddlProject.SelectedValue + "' and VILLAGE = '" + ddlVillage.SelectedValue + "' and SURVEY_AREA_7_12 = '" + ddl712.SelectedValue + "' and LEGAL_ENTITY = '" + txtCompName.Value + "' " +
" and TALUKA = '" + ddlTaluka.SelectedValue + "'"); // updating doc as last doc no

cmd2.ExecuteNonQuery();

cmd3.ExecuteNonQuery();
ObjPriCon.Close();
}

ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Saved Successfully');window.location ='Frm_Agreement_Master.aspx?TranType=AM&PView=N&Mode=A&Redirect=oracle&Key=0&Redirect=" + Request.QueryString["Redirect"] + "&userid=" + Request.QueryString["userid"].ToString() + "';", true);
}
catch (Exception ex)
{
ex.Message.ToString();
}


After

ObjPriCon.Open();
it goes to catch block and throws the mentioned error.

Answer

Instead of opening a open connection, you can check it's state like

if (ObjPriCon.State != ConnectionState.Open)
{
    ObjPriCon.Open();
}