The connection is already open - C#

[darrylnuyda]

Hi Everyone.

Can I ask your help?

Actually I encountering the error The "connection is already open.".

here is my DBconnect.cs

public class DBConnect
    {
        public static string csConnect = "uid=root; database=membership; pooling = false; convert zero datetime=True";
        public static MySqlConnection csCon= new MySqlConnection(Classes.DBConnect.csConnect);

        public MySqlCommand cmdCon = new MySqlCommand();
        public MySqlDataReader reader;

        public void nonQuery(string cmdText)
        {
            cmdCon.Connection = csCon;
            csCon.Open();
            cmdCon.CommandText = cmdText;
            cmdCon.ExecuteNonQuery();
            cmdCon.Dispose();
            csCon.Close();
        }

        public void OPEN(string cmdtext)
        {
            cmdCon.Connection = Classes.DBConnect.csCon;
            Classes.DBConnect.csCon.Open();
            cmdCon.CommandText = cmdtext;
            reader = cmdCon.ExecuteReader();
        }

        public void CLOSE()
        {
            reader.Close();
            cmdCon.Dispose();
            Classes.DBConnect.csCon.Close();
        }


    }

the Select query from listView

private void txtboxlname_TextChanged(object sender, EventArgs e)
        {
            listView1.Items.Clear();

            OpenConCls.OPEN("select * from tblpair_individual_membership  where pilname Like '%" + txtboxlname.Text + "%'");


            while (OpenConCls.reader.Read())
            {


                ListViewItem li = new ListViewItem(OpenConCls.reader[0].ToString());
                li.SubItems.Add(OpenConCls.reader[1].ToString());
                li.SubItems.Add(OpenConCls.reader[2].ToString());
                li.SubItems.Add(OpenConCls.reader[3].ToString());
                li.SubItems.Add(OpenConCls.reader[4].ToString());

                listView1.Items.AddRange(new ListViewItem[] { li });

            }

            OpenConCls.CLOSE();





        }

Okay. I will direct to the point, in my form i have a 3 select query statement(ListView2(textbox2) & ListView3(textbox3))..

Here is the problem, in one(1) form i have using a tabControl, 2 tabpage put up. in my tabpage1 I have a Select staement query which is search the data from the db but before I can do that I need to go to tabpage2 to Open the Connection, before I go back to the TabPage1. But when I try to search in tabpage page1, the error showed like this"(The connection is already open)".

According to my friends they are saying to close connection, I applied on that but the problem is the 3 select query statement will the same form.

Can anyone give the Idea?

Sincerely,

Darryl

[pitic]

Hi. Since you went with this approach to create your connection classs, in the OPEN method only open the connection if it is closed.

if (csCon.State == ConnectionState.Closed)
{
    csCon.Open();
}

[darrylnuyda]

    if (csCon.State == ConnectionState.Closed)
    {
    csCon.Open();
    }

Hi Pitic,

I tried the code but I encounter the same problem,.

regards,
Darryl

[darrylnuyda]

I Solved my problem, I trace my code, and I forgot to put the Close connection to my query.

[KushMishra]

Hi,

May be a better approach would be you use try-catch-finally blocks.
In the constructor/page load event you may call the open connection and in the end under finally block you close your connection.