geeting following error on my windows form:The connection was not closed. The connection's current state is open.

Question

0.00/5 (No votes)

See more:

C#

private void textBox5_TextChanged(object sender, EventArgs e)
        {
             objConn1.Open(); 
            int nQty = 0;
            int nActualQty;
                if (!int.TryParse(textBox5.Text, out nActualQty))
                   {
                    // Report problem to user - he typed wrong.
                    
                   }
         
            string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
            SqlCommand com = new SqlCommand(sql3, objConn1);
            SqlDataReader objQty = com.ExecuteReader();
           
            if (objQty.Read())
            {
                nQty = Convert.ToInt32(objQty["Qty"]);
            }
            if (nQty < nActualQty)
            {
                MessageBox.Show("Qty is greater");
                
            }
            textBox5.Clear();
            objConn1.Close();
            
        }

Posted 16-Dec-12 19:47pm

Master Vinu

Updated 16-Dec-12 19:49pm

Krunal Rohit

v2

Add a Solution

3 solutions

Solution 3

try this.

C#

private void textBox5_TextChanged(object sender, EventArgs e)
{
if( objConn1.State == ConnectionState.Open)
 {
   objConn1.Close();  
 }
 objConn1.Open(); 
 int nQty = 0;
 int nActualQty;
 if (!int.TryParse(textBox5.Text, out nActualQty))
 {
     // Report problem to user - he typed wrong.
 }
 string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
 
 SqlCommand com = new SqlCommand(sql3, objConn1);
SqlDataReader objQty

 try
 {
    objQty = com.ExecuteReader();
           
    if (objQty.Read())
    {
      nQty = Convert.ToInt32(objQty["Qty"]);
    }
    if (nQty < nActualQty)
   {
      MessageBox.Show("Qty is greater");
   }
 } catch(Exception exp){}
 if( objQty != null)
 {
  objQty.Close(); // You need to close the Reader class once you finished using it.
 }
 textBox5.Clear();
 objConn1.Close();
            
}

Posted 16-Dec-12 21:29pm

Jibesh

Solution 1

C#

string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
            SqlCommand com = new SqlCommand(sql3, objConn1);
            SqlDataReader objQty = com.ExecuteReader();
           
            if (objQty.Read())
            {
                nQty = Convert.ToInt32(objQty["Qty"]);
            }
            if (nQty < nActualQty)
            {
                MessageBox.Show("Qty is greater");
                
            }
            textBox5.Clear();
            com.Connection.Close();  // add this...
            objConn1.Close();

Posted 16-Dec-12 19:56pm

Krunal Rohit

Comments

Master Vinu 17-Dec-12 1:59am

no dear ...not working same error when debug the code on objconnection.open()

[no name] 17-Dec-12 2:09am

ok, use try catch..finally...blocks

Master Vinu 17-Dec-12 2:38am

thx but still there is error

Master Vinu 17-Dec-12 2:36am

give folloeing errro on :using (SqlDataReader objQty = com.ExecuteReader())

There is already an open DataReader associated with this Command which must be closed first.

[no name] 17-Dec-12 2:41am

private void textBox5_TextChanged(object sender, EventArgs e)
{
int nQty = 0;
int nActualQty;
string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(sql3, objConn1);
DataTable dt = new DataTable();
da.fill(dt);

foreach(DataRow dr in dt.Rows)
{
nQty = int.Parse(dr["Qty"].ToString());
if (nQty < nActualQty)
{
MessageBox.Show("Qty is greater");

}
}

Master Vinu 17-Dec-12 2:45am

dear please ammend the following query:::thx in advance dear

private void textBox5_TextChanged(object sender, EventArgs e)
{
int nQty = 0;
int nActualQty ;
if (!int.TryParse(textBox5.Text, out nActualQty))
{
// Report problem to user - he typed wrong.

}

try
{
if (objConn1.State != ConnectionState.Open)
{
objConn1.Open();
}
string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
using (SqlCommand com = new SqlCommand(sql3, objConn1))
{
using (SqlDataReader objQty = com.ExecuteReader())
{
if (objQty.Read())
{
nQty = Convert.ToInt32(objQty["Qty"]);
}
if (nQty < nActualQty)
{
MessageBox.Show("Qty is greater");
textBox5.Text = "";

}
}

}

}
finally
{

objConn1.Close();

}

}

[no name] 17-Dec-12 2:47am

replace your code with what I've given in my earlier comment..

Master Vinu 17-Dec-12 4:07am

thx krunal

Master Vinu 17-Dec-12 2:51am

Error 8 'System.Data.SqlClient.SqlDataAdapter' does not contain a definition for 'fill' and no extension method 'fill' accepting a first argument of type 'System.Data.SqlClient.SqlDataAdapter' could be found (are you missing a using directive or an assembly reference?) G:\Spareage\Lib\CRSpareageFinance\GateForm\InventoryEntry.cs 279 56 CRSpareageFinance

[no name] 17-Dec-12 2:56am

Oh man !!!
da.Fill(dt); //f is capital..

Master Vinu 17-Dec-12 2:52am

private void textBox5_TextChanged(object sender, EventArgs e)
{
int nQty = 0;
int nActualQty ;
if (!int.TryParse(textBox5.Text, out nActualQty))
{
// Report problem to user - he typed wrong.

}

try
{
if (objConn1.State != ConnectionState.Open)
{
objConn1.Open();
}
string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(sql3, objConn1);
DataTable dt = new DataTable();
da.fill(dt);
foreach(DataRow dr in dt.Rows) { nQty = int.Parse(dr["Qty"].ToString());
if (nQty < nActualQty)

{
MessageBox.Show("Qty is greater");
}

}

}

finally
{

objConn1.Close();

}

}

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 · Accepted Answer · 2012-12-16T20:04:00

Probably, it is to do with your

C#

if (!int.TryParse(textBox5.Text, out nActualQty))
   {
    // Report problem to user - he typed wrong.

   }

Section - you don't show it, but if you do not close the connection there, it will remain open (since I assume you do a return in that bit).
There are two alternatives here:
1) Use this code for the open:

C#

if (objConn1.State != ConnectionState.Open)
   {
   objConn1.Open();
   }

2) Use a try..finalize block each time you open the connection:

C#

try
    {
    objConn1.Open();
    ...
    }
finally
    {
    if (objConn1.State == ConnectionState.Open)
       {
       objConn1.Close();
       }
    }

I would do both, and also move the validation code outside the try block (there is no point in opening in the connection if you aren't going to use it):

C#

private void textBox5_TextChanged(object sender, EventArgs e)
    {
    int nQty = 0;
    int nActualQty;
    if (!int.TryParse(textBox5.Text, out nActualQty))
        {
        // Report problem to user - he typed wrong.

        }
    else
        {
        try
            {
            if (objConn1.State != ConnectionState.Open)
                {
                objConn1.Open();
                }
            string sql3 = "select qty from dbo.RateMouldQuantity where ratechart= '" + comboBox5.SelectedValue.ToString() + "'";
            using (SqlCommand com = new SqlCommand(sql3, objConn1))
                {
                using (SqlDataReader objQty = com.ExecuteReader())
                    {
                    if (objQty.Read())
                        {
                        nQty = Convert.ToInt32(objQty["Qty"]);
                        }
                    if (nQty < nActualQty)
                        {
                        MessageBox.Show("Qty is greater");
                        }
                    }
                }
            textBox5.Clear();
            }
        finally
            {
            objConn1.Close();
            }
        }
    }

(I have also thrown in a couple of using blocks, because you should dispose SQL commands and readers)