Can i know Why this error 'The connection was not closed. The connection's current state is open.' is shown?

Question

       public partial class Form1: Form
       {
           OleDbConnection con = new OleDbConnection();
           OleDbCommand cmd = new OleDbCommand();

       public Form1()
       {
            InitializeComponent();

       }

        private void label1_Click(object sender, EventArgs e)
        {

        }  

    private void radioButton2_CheckedChanged(object sender, EventArgs e)
    {

    }

    private void Form1_Load(object sender, EventArgs e)
    {
        con.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0;Data 
                                Source= Assignment.accdb";
        con.Open();




    }

    private void radioButton3_CheckedChanged(object sender, EventArgs e)
    {

    }

    private void button2_Click(object sender, EventArgs e)
    {
        Application.Exit();
    }

    private void button1_Click(object sender, EventArgs e)
    {

    }

    private void button1_Click_1(object sender, EventArgs e)
    {

        con.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandText = "select * from SignUp where Username = '" + 
                           textBox1.Text + "' and Password= '" + 
                           textBox2.Text + "'";
        OleDbDataReader dr = cmd.ExecuteReader();
        int count = 0;
        while (dr.Read())
        {
            count++;
        }
        if (count ==1 )
        {
            MainMenu menu = new MainMenu();
            menu.Show();
            this.Close();
        }
        else
        {
            MessageBox.Show("Incorrect Username or Password");
        }

        con.Close();

can anyone help me to check my code How to fix this error 'The connection was not closed. The connection's current state is open.'

although I have closed the connection

** the message The connection was not closed. The connection's current state is open.is shown at (con.open(); [private void button1_Click_1])

You open the connection in both the click event **and** `Form_Load`. This is bad. Remove one of them (likely the `Form_Load` one). It is like you are going up to a door and opening it up. Then going up a few seconds later and trying to open it again. Rightfully the door is saying 'no you can't, I am already open'. — mjwills, Jan 13 '19 at 04:38
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jan 13 '19 at 04:40
I'd strongly recommend having a read of [Best way to store password in database](https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database) since it looks like you are storing your passwords in a raw form. This is a **very** bad idea. — mjwills, Jan 13 '19 at 05:04
Small tip on asking questions about code that doesn't work: the code should be a [mcve]. *Minimal* means not including empty event handlers which are unrelated to the question ;) — Richardissimo, Jan 13 '19 at 07:25

Grant Winney · Answer 1 · 2019-01-13T05:29:26.777

It could be because this.Close(); closes the Form before that last line executes, or because you're opening the connection in multiple places.

Remove those lines of code from the Form1_Load event and use using statements to create the connection right when you need it, which should also cleanup resources (and close connections) for you, no matter what (even if an exception is thrown).
Also, always try to parameterize your queries.
Since you're only expecting a single record returned (or nothing if the login is wrong), you can use ExecuteScalar to simplify things a bit more.

Here's a modified version of your code:

using (var con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Assignment.accdb"))
{
    con.Open();

    using (var cmd = new OleDbCommand())
    {
        cmd.Connection = con;
        cmd.CommandText = "select * from SignUp where Username = @user and Password = @pass";
        cmd.Parameters.Add(new OleDbParameter("@user", textBox1.Text));
        cmd.Parameters.Add(new OleDbParameter("@pass", textBox2.Text));

        var result = cmd.ExecuteScalar();

        if (result == null)
        {
            MessageBox.Show("Incorrect Username or Password");
            return;
        }

        MainMenu menu = new MainMenu();
        menu.Show();
        this.Close();
    }
}

another error 'The ConnectionString property has not been initialized.' is shown — Seng Foong, Jan 13 '19 at 04:43
another error 'The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.' is shown — Seng Foong, Jan 13 '19 at 05:04

score -1 · Answer 2 · answered Jan 13 '19 at 08:57

you are trying to open the same connection within two functions: private void Form1_Load(object sender, EventArgs e) and private void button1_Click_1(object sender, EventArgs e)

Simply, remove con.Open(); form private void Form1_Load(object sender, EventArgs e)

Can i know Why this error 'The connection was not closed. The connection's current state is open.' is shown?

2 Answers2