CODEWITHSUNDEEP
c#winformserror-handling
Jeff
Jeff

Reputation: 29

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

How to fix this problem? I don't know why this error occur when I already add Clode().

The error will go here:

public void existType()
    {
        try
        {
            con.Open();
            string existquery = "SELECT*FROM tblRoomType WHERE Type = '" + txtRoomType.Text + "'";
            da = new OleDbDataAdapter(existquery, con);
            da.Fill(ds, "tblRoomType");
            int counter = 0;
            string tabletype = "tblRoomType";
            if (counter < ds.Tables[tabletype].Rows.Count)
            {
                string type = ds.Tables[tabletype].Rows[counter]["Type"].ToString();
                if (type == txtRoomType.Text)
                {
                    editRoomType(); //There will be no error if I remove this and include the MessageBox below.
                    MessageBox.Show("Successfully Edited", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //MessageBox.Show("This " + txtRoomType.Text + " is already exist.", "EXIST", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else //Remove this will remove the error
                {
                    addRoomType();
                    MessageBox.Show("Successfully Added", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }//to here
            }
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

This are the code of editRoomType() and addRoomType():

public void addRoomType()
    {
        con.Open();
        string addtypequery = "INSERT INTO tblRoomType VALUES ('" + this.typeid + "','" + txtRoomType.Text + "','" + txtRoomRate.Text + "','" + txtExtraCharge.Text + "','" + txtCancelFee.Text + "','" + txtMaxOcc.Text + "')";
        cmd = new OleDbCommand(addtypequery, con);
        cmd.ExecuteNonQuery();
        con.Close();

        loadRoomType();
        txtRoomType.ReadOnly = false;
        txtRoomType.Enabled = false;
        txtRoomRate.Enabled = false;
        txtMaxOcc.Enabled = false;
        txtExtraCharge.Enabled = false;
        txtCancelFee.Enabled = false;
        btnAddRoomType.Enabled = false;
        txtRoomType.Clear();
        txtRoomRate.Clear();
        txtMaxOcc.Clear();
        txtExtraCharge.Clear();
        txtCancelFee.Clear();
    }

public void editRoomType()
    {
        con.Open();
        string edittypequery = "UPDATE tblRoomType SET Rate = '" + txtRoomRate.Text + "', ExtraCharge = '" + txtExtraCharge.Text + "', CancelFee = '" + txtCancelFee.Text + "', MaxOcc = '" + txtMaxOcc.Text + "' WHERE TypeID = '" + txtRoomType.Text + "'";
        cmd = new OleDbCommand(edittypequery, con);
        cmd.ExecuteNonQuery();
        con.Close();
    }

The existType() will be go here:

private void btnAddRoomType_Click(object sender, EventArgs e)
    {
        if (txtRoomType.Text != "" || txtRoomRate.Text != "" || txtExtraCharge.Text != "" || txtCancelFee.Text != "" || txtMaxOcc.Text != "")
        {
            existType();
        }
        else
        {
            MessageBox.Show("Please fill in the space provided","OOPPSS!!", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

Upvotes: 0

Views: 894

Answers (2)

Roy
Roy

Reputation: 69

Try like this - 

try
    {
         con.Open();
         .......
         con.Close();
    }
    catch (Exception ex)
    {
         MessageBox.Show(ex.Message);
    }
    finally
    {
         if (con.State == ConnectionState.Open)
         {
              con.Open(); 
         }
    }

Upvotes: 0

Dave Zych
Dave Zych

Reputation: 21897

The error is because you call con.Open in existType, then you either call addRoomType or editRoomType, both of which call con.Open again before you call con.Close in existType. Calling con.Open on an already open connection will throw the error you are seeing.

You can either remove the calls to con.Open/con.Close inside of the addRoomType or editRoomType and only call them in existType, or use local connections for each method, like so:

public void existType()
{
    try
    {
        using (var conn = new SqlConnection())
        {
            conn.Open();
            string existquery = "SELECT*FROM tblRoomType WHERE Type = '" + txtRoomType.Text + "'";
            da = new OleDbDataAdapter(existquery, conn);
            da.Fill(ds, "tblRoomType");
        }
        //rest of code
    }
}

public void editRoomType()
{
    using (var conn = new SqlConnection())
    {
        conn.Open();
        string edittypequery = "UPDATE tblRoomType SET Rate = '" + txtRoomRate.Text + "', ExtraCharge = '" + txtExtraCharge.Text + "', CancelFee = '" + txtCancelFee.Text + "', MaxOcc = '" + txtMaxOcc.Text + "' WHERE TypeID = '" + txtRoomType.Text + "'";
        cmd = new OleDbCommand(edittypequery, conn);
        cmd.ExecuteNonQuery();
    }
}

Note that you don't have to call Close since the using statement will do that for you.

Upvotes: 1

Related Questions