none
error :: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. RRS feed

  • Question

  • ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

    Line 98: objCmd.ExecuteNonQuery();

    <script runat="server"> SqlConnection objConn = new SqlConnection(); SqlCommand objCmd = new SqlCommand(); SqlDataAdapter dtAdapter = new SqlDataAdapter(); DataTable dt = new DataTable(); String strConnString, strSQL; void Page_Load(object sender,EventArgs e) { strConnString = "Server=localhost;UID=sa;PASSWORD=12345;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"; if (Convert.ToString(Session["strUser"]) == "") { Response.Redirect("index.aspx"); Response.End(); } if(!Page.IsPostBack) { BindData(); } } void BindData() { strSQL = "SELECT * FROM AddRoom"; objConn.ConnectionString = strConnString; objCmd.Connection = objConn; objCmd.CommandText = strSQL; objCmd.CommandType = CommandType.Text; objConn.Open(); dtAdapter.SelectCommand = objCmd; dtAdapter.Fill(dt); myGridView.DataSource = dt; myGridView.DataBind(); dtAdapter = null; objConn.Close(); objConn = null; } void modDeleteCommand(Object sender, GridViewDeleteEventArgs e) { Label lblRoomID = (Label)myGridView.Rows[e.RowIndex].FindControl("lblRoomID"); strSQL = "DELETE FROM AddRoom WHERE RoomID = '" + lblRoomID.Text + "'"; objCmd.Connection = objConn; objCmd.CommandText = strSQL; objCmd.CommandType = CommandType.Text; ; objCmd.CommandText = strSQL; objCmd.ExecuteNonQuery(); myGridView.EditIndex = -1; BindData(); } void myGridView_RowDataBound(Object s, GridViewRowEventArgs e) { Label lblRoomID = (Label)(e.Row.FindControl("lblRoomID")); if (lblRoomID != null) { lblRoomID.Text = (string)DataBinder.Eval(e.Row.DataItem, "RoomID", "{0}"); } Label lblRoom = (Label)(e.Row.FindControl("lblRoom")); if (lblRoom != null) { lblRoom.Text = (string)DataBinder.Eval(e.Row.DataItem, "RoomName","{0}"); } Label lblSize = (Label)(e.Row.FindControl("lblSize")); if (lblSize != null) { lblSize.Text = (string)DataBinder.Eval(e.Row.DataItem, "Size", "{0}"); } Label lblLocation = (Label)(e.Row.FindControl("lblLocation")); if (lblLocation != null) { lblLocation.Text = (string)DataBinder.Eval(e.Row.DataItem, "Location", "{0}"); } HyperLink hplDetail = (HyperLink)e.Row.FindControl("hplDetail"); if ((hplDetail != null)) { hplDetail.NavigateUrl = "JavaScript:void(0);"; hplDetail.Attributes.Add("OnClick", "JavaScript:OpenPopup('popupDetailSchedule.aspx?RoomID=" + e.Row.DataItemIndex.ToString("RoomID").ToString() + "');"); } HyperLink hplEdit = (HyperLink)(e.Row.FindControl("hplEdit")); if (hplEdit != null) { hplEdit.NavigateUrl = "editRoomAdmin.aspx?RoomMeetID=" + (string)DataBinder.Eval(e.Row.DataItem, "RoomMeetID", "{0}"); } } void ShowPageCommand(Object s, GridViewPageEventArgs e) { myGridView.PageIndex = e.NewPageIndex; BindData(); } </script>


    Thursday, August 8, 2013 12:32 PM

Answers

  • Hi AINAN12,

    Thanks for your post!

    Base on the error message, we can find the reason of the error is Connection is closed when executing “objCmd.ExecuteNonQuery()”;

    The error in your code:

    void modDeleteCommand(Object sender, GridViewDeleteEventArgs e)
            {
                Label lblRoomID = (Label)myGridView.Rows[e.RowIndex].FindControl("lblRoomID");
                strSQL = "DELETE FROM AddRoom WHERE RoomID = '" + lblRoomID.Text + "'";
                objCmd.Connection = objConn;
                objCmd.CommandText = strSQL;
                objCmd.CommandType = CommandType.Text; ;
                objCmd.CommandText = strSQL;
                objCmd.ExecuteNonQuery();
    
                myGridView.EditIndex = -1;
                BindData();
            }
    

    We can find you didn’t open the Connection before “objCmd.ExecuteNonQuery()”.

    So, please modify your code as follows:

    void modDeleteCommand(Object sender, GridViewDeleteEventArgs e)
            {
                Label lblRoomID = (Label)myGridView.Rows[e.RowIndex].FindControl("lblRoomID");
                strSQL = "DELETE FROM AddRoom WHERE RoomID = '" + lblRoomID.Text + "'";
                objConn.ConnectionString = strConnString;
                objCmd.Connection = objConn;
                objCmd.CommandText = strSQL;
                objCmd.CommandType = CommandType.Text; ;
                objCmd.CommandText = strSQL;
                objConn.Open();
                objCmd.ExecuteNonQuery();
                objConn.Close();
    
                myGridView.EditIndex = -1;
                BindData();
            }
    

    Thanks

    Best Regards




    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 9, 2013 7:24 AM
    Moderator

All replies

  • Hi AINAN12,

    Thanks for your post!

    Base on the error message, we can find the reason of the error is Connection is closed when executing “objCmd.ExecuteNonQuery()”;

    The error in your code:

    void modDeleteCommand(Object sender, GridViewDeleteEventArgs e)
            {
                Label lblRoomID = (Label)myGridView.Rows[e.RowIndex].FindControl("lblRoomID");
                strSQL = "DELETE FROM AddRoom WHERE RoomID = '" + lblRoomID.Text + "'";
                objCmd.Connection = objConn;
                objCmd.CommandText = strSQL;
                objCmd.CommandType = CommandType.Text; ;
                objCmd.CommandText = strSQL;
                objCmd.ExecuteNonQuery();
    
                myGridView.EditIndex = -1;
                BindData();
            }
    

    We can find you didn’t open the Connection before “objCmd.ExecuteNonQuery()”.

    So, please modify your code as follows:

    void modDeleteCommand(Object sender, GridViewDeleteEventArgs e)
            {
                Label lblRoomID = (Label)myGridView.Rows[e.RowIndex].FindControl("lblRoomID");
                strSQL = "DELETE FROM AddRoom WHERE RoomID = '" + lblRoomID.Text + "'";
                objConn.ConnectionString = strConnString;
                objCmd.Connection = objConn;
                objCmd.CommandText = strSQL;
                objCmd.CommandType = CommandType.Text; ;
                objCmd.CommandText = strSQL;
                objConn.Open();
                objCmd.ExecuteNonQuery();
                objConn.Close();
    
                myGridView.EditIndex = -1;
                BindData();
            }
    

    Thanks

    Best Regards




    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 9, 2013 7:24 AM
    Moderator
  • Hi AINAN12,

    Since we haven't heard from you for a long time, I temporarily close this case. I mark useful reply as answer. If you have any concerns, please free feel to reopen it or submit a new question. Thanks for your understanding.

    Thank

    Best Regards



    Starain Chen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 15, 2013 2:30 AM
    Moderator