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

Question

1.00/5 (1 vote)

See more:

I am not geeting what do in this case?
can any plz help me?

What I have tried:

C#

private void button1_Click(object sender, EventArgs e)
        {
            using (OleDbConnection con1 = new OleDbConnection(con))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = con1;
                OleDbDataAdapter da;

                DataSet ds = new DataSet();

                if ((textBox4.Text == string.Empty) || (textBox5.Text == string.Empty) || (comboBox1.Text == string.Empty) || (comboBox2.Text == string.Empty))
                {

                    MessageBox.Show("Please enter Your All the Details in the Related Fields ...!!!!!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);


                }
                else
                {

                    cmd = new OleDbCommand("SELECT * from Spldetails where Date = @d1 and EmpId = @v1 and Splwrk = @c1 and Splwrkdetails = @sd", con1);
                    con1.Open();
                    da = new OleDbDataAdapter(cmd);
                    da.SelectCommand.Parameters.Add("@v1", OleDbType.Variant, 30).Value = this.textBox1.Text;
                    da.SelectCommand.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
                    da.SelectCommand.Parameters.Add("@c1", OleDbType.Variant, 100).Value = this.comboBox1.Text;
                    da.SelectCommand.Parameters.Add("@sd", OleDbType.Variant, 300).Value = this.textBox6.Text;
                    da.Fill(ds);
                    int i = ds.Tables[0].Rows.Count;
                    if (i > 0)
                    {
                        MessageBox.Show("Duplicate Details !!!! You have already Submitted your details on " + dateTimePicker1.Text + ".");
                        //ds.Clear();
                        textBox4.Text = "";
                        textBox5.Text = "";
                        textBox6.Text = "";
                        comboBox1.Text = "";
                        comboBox2.Text = "";
                        con1.Close();

                    }
                    else
                    {

                        try
                        {

                            
                            string query1 = "INSERT INTO Spldetails([Date],[EmpId],[EmpName],[Designation],[Records],[Splwrkhrs],[Team],[Splwrk],[Splwrkdetails],[Allottedby]) VALUES(@d1,@v1,@v2,@dg,@rd,@shr,@tm,@sw,@sd,@ab)";
                            cmd = new OleDbCommand(query1, con1);
                            con1.Open();
                            da = new OleDbDataAdapter(cmd);
                            da.SelectCommand.Parameters.Add("@v1", OleDbType.Variant, 30).Value = this.textBox1.Text;
                            da.SelectCommand.Parameters.Add("@v2", OleDbType.Variant, 100).Value = this.textBox2.Text;
                            da.SelectCommand.Parameters.Add("@dg", OleDbType.Variant, 50).Value = this.textBox3.Text;
                            da.SelectCommand.Parameters.Add("@rd", OleDbType.Variant, 15).Value = Convert.ToDouble(this.textBox4.Text);
                            da.SelectCommand.Parameters.Add("@shr", OleDbType.Variant, 15).Value = Convert.ToDouble(this.textBox5.Text);
                            da.SelectCommand.Parameters.Add("@tm", OleDbType.Variant, 100).Value = this.textBox8.Text;
                            da.SelectCommand.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
                            da.SelectCommand.Parameters.Add("@ab", OleDbType.Variant, 100).Value = this.comboBox1.Text;
                            da.SelectCommand.Parameters.Add("@sw", OleDbType.Variant, 100).Value = this.comboBox2.Text;
                            da.SelectCommand.Parameters.Add("@sd", OleDbType.Variant, 300).Value = this.textBox6.Text;
                            cmd.ExecuteNonQuery();
                            con1.Close();

                            MessageBox.Show("Congratulations....Your Details have been submitted successfully");

                            textBox4.Text = "";
                            textBox5.Text = "";
                            textBox6.Text = "";
                            comboBox1.Text = "";
                            comboBox2.Text = "";


                            
                        }

                        catch (Exception exp)
                        {
                            MessageBox.Show("error" + exp);
                        }

                    }
                }
              
            }
        }

Posted 3-Jun-18 0:38am

Prateek gsharma

Updated 3-Jun-18 0:54am

OriginalGriff

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-06-03T00:55:00

Solution 1

You are trying to open an already open connection:

private void button1_Click(object sender, EventArgs e)
        {
            using (OleDbConnection con1 = new OleDbConnection(con))
            {
...
                if (...)
                {
...
                }
                else
                {
...
                    con1.Open();
...
                    if (i > 0)
                    {
...                        con1.Close();

                    }
                    else
                    {
                        try
                        {
...
                            con1.Open();
...
                            con1.Close();
...
                        }
                        catch (Exception exp)
                        {
                            MessageBox.Show("error" + exp);
                        }
                    }
                }
            }
        }

Do your validity checks at the top of the method, and exit if they fail.
Then create your connection and immediately open it.
It will be closed automatically when it goes out of scope of the using block.

Posted 3-Jun-18 0:55am

OriginalGriff

Comments

Prateek gsharma 3-Jun-18 7:06am

i am getting this error in try block sir

OriginalGriff 3-Jun-18 7:15am

Yes - because you already opened the connection outside the try block.
Look at the abstracted code I posted, which is your code with irrelevant code removed.

Prateek gsharma 3-Jun-18 7:14am

if i remove con1.open(); from try block i am getting data type mismatch error

i am not understanding what to do in this case?

Prateek gsharma 3-Jun-18 7:35am

still i am getting same error

OriginalGriff 3-Jun-18 8:15am

You're still getting the connection open error? Odd. Show the actual latest code.

Prateek gsharma 3-Jun-18 8:23am

private void button1_Click(object sender, EventArgs e)
{
using (OleDbConnection con1 = new OleDbConnection(con))
{
OleDbCommand cmd = new OleDbCommand();

OleDbDataAdapter da;

DataSet ds = new DataSet();

if ((textBox4.Text == string.Empty) || (textBox5.Text == string.Empty) || (comboBox1.Text == string.Empty) || (comboBox2.Text == string.Empty))
{

MessageBox.Show("Please enter Your All the Details in the Related Fields ...!!!!!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);

}
else
{

cmd = new OleDbCommand("SELECT * from Spldetails where Date = @d1 and EmpId = @v1 and Splwrk = @c1 and Splwrkdetails = @sd", con1);

da = new OleDbDataAdapter(cmd);
da.SelectCommand.Parameters.Add("@v1", OleDbType.Variant, 30).Value = this.textBox1.Text;
da.SelectCommand.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
da.SelectCommand.Parameters.Add("@c1", OleDbType.Variant, 100).Value = this.comboBox1.Text;
da.SelectCommand.Parameters.Add("@sd", OleDbType.Variant, 300).Value = this.textBox6.Text;
da.Fill(ds);
int i = ds.Tables[0].Rows.Count;
if (i > 0)
{
MessageBox.Show("Duplicate Details !!!! You have already Submitted your details on " + dateTimePicker1.Text + ".");
//ds.Clear();
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
comboBox1.Text = "";
comboBox2.Text = "";

}
else
{

try
{

string query1 = "INSERT INTO Spldetails([Date],[EmpId],[EmpName],[Designation],[Records],[Splwrkhrs],[Team],[Splwrk],[Splwrkdetails],[Allottedby]) VALUES(@d1,@v1,@v2,@dg,@rd,@shr,@tm,@sw,@sd,@ab)";
cmd = new OleDbCommand(query1, con1);

da = new OleDbDataAdapter(cmd);
da.SelectCommand.Parameters.Add("@v1", OleDbType.Variant, 30).Value = this.textBox1.Text;
da.SelectCommand.Parameters.Add("@v2", OleDbType.Variant, 100).Value = this.textBox2.Text;
da.SelectCommand.Parameters.Add("@dg", OleDbType.Variant, 50).Value = this.textBox3.Text;
da.SelectCommand.Parameters.Add("@rd", OleDbType.Variant, 15).Value = Convert.ToDouble(this.textBox4.Text);
da.SelectCommand.Parameters.Add("@shr", OleDbType.Variant, 15).Value = Convert.ToDouble(this.textBox5.Text);
da.SelectCommand.Parameters.Add("@tm", OleDbType.Variant, 100).Value = this.textBox8.Text;
da.SelectCommand.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
da.SelectCommand.Parameters.Add("@ab", OleDbType.Variant, 100).Value = this.comboBox1.Text;
da.SelectCommand.Parameters.Add("@sw", OleDbType.Variant, 100).Value = this.comboBox2.Text;
da.SelectCommand.Parameters.Add("@sd", OleDbType.Variant, 300).Value = this.textBox6.Text;

cmd.ExecuteNonQuery();
con1.Close();

MessageBox.Show("Congratulations....Your Details have been submitted successfully");

textBox4.Text = "";

OriginalGriff 3-Jun-18 8:58am

So you've ignored what I said, and done whatever you fancied?

Stop and look at your code. It's not difficult to workout what you are doing wrong...

Prateek gsharma 3-Jun-18 9:20am

i am unable to solve this sir.

send me the correct code sir.