附加信息：连接未关闭。连接的当前状态是打开的。 [英] Additional information: The connection was not closed. The connection's current state is open.

问题描述

一次又一次地给我这种类型错误：

附加信息：连接未关闭。连接的当前状态是打开的。

在以下代码中。

 public void store_detailinsertion（） 
 {
 
 try 
 {
 ConnectionString.cnn.Open（）; 
 cmd.Parameters.Clear（）; 
 cmd = new SqlCommand（Insert into store_detail（prdid，quantity，itemtotal）Values（@ prdid，@ quantity，@ itemtotal），ConnectionString.cnn）; 
 cmd.Parameters.Add（@ prdid，SqlDbType.VarChar）.Value = mtbprdid.Text.ToString（）; 
 cmd.Parameters.Add（@ quantity，SqlDbType.VarChar）.Value = txtqty.Text.ToString（）; 
 cmd.Parameters.Add（@ itemtotal，SqlDbType.VarChar）.Value = txttotalstkqty.Text.ToString（）; 
 cmd.ExecuteNonQuery（）; 
 cmd.Connection.Close（）; 
} 
 catch（例外）
 {
 throw; 
} 
最后
 {
 
 ConnectionString.cnn.Close（）; 
 ConnectionString.cnn.Dispose（）; 
} 
 
 
} 
 
 public void store_detailUpdation（）
 {
 
 try 
 { 
 ConnectionString.cnn.Open（）; 
 
 cmd.Connection = ConnectionString.cnn; 
 ConnectionString.cnn.Open（）; 
 cmd.CommandText =更新store_detail set itemtotal =''+ txttotalstkqty.Text +''，quantity =''+ txtqty.Text +''其中prdid =''+ mtbprdid.Text +  ''; 
 
 cmd.ExecuteNonQuery（）; 
 cmd.Connection.Close（）; 
} 
 catch（例外）
 {
 throw; 
} 
最后
 {
 ConnectionString.cnn.Close（）; 
 ConnectionString.cnn.Dispose（）; 
} 
 
} 
 
 private void store_detailOperation（）
 {
 try 
 {
 ConnectionString.cnn。打开（）; 
 DataSet ds = new DataSet（）; 
 
 
 SqlDataAdapter da = new SqlDataAdapter（select * from store_detail where prdid =''+ mtbprdid.Text +''，ConnectionString.cnn）; 
 int s =（da.Fill（ds））; 
 if（s> = 1）
 {
 store_detailUpdation（）; 
 // DisableTextBoxs（）; 
 // EnableButtons（）; 
 //btnsave.Enabled = false; 
 //ConnectionString.cnn.Close（）; 
} 
 else 
 {
 
 store_detailinsertion（）; 
 // DisbleTextBoxs（）; 
 // EnableButtons（）; 
 //btnsave.Enabled = false; 
} 
 
} 
 catch（例外）
 {
 throw; 
} 
最后
 {
 ConnectionString.cnn.Close（）; 
 ConnectionString.cnn.Dispose（）; 
} 
} 

Plzzz，任何人都可以帮助我

解决方案

使用store_detailUpdation中的此代码，将Connection对象设置为已打开的Connection对象。这可能是错误消息的来源。

 cmd.Connection = ConnectionString.cnn; 
 ConnectionString.cnn.Open（）; 

在store_detailUpdation中，您将关闭连接两次。进入尝试块，然后进入最后块。



这两个都关闭数据库连接：

 cmd.Connection.Close（）; 

 ConnectionString.cnn.Close（）; 

请参阅try-finally(C＃Reference） [ ^ ]

如果可能的话，最好在命令和连接对象中使用 using 块。

  public   void  store_detailinsertion（ string  connectionString， string  sqlText）
 {
 使用 （SqlConnection conn =  new  SqlConnection（connectionString））
 {
 conn.Open（） ; 
 使用（SqlCommand cmd =  new  SqlCommand（sqlText，conn））
 { 
 cmd.Parameters.Add（  @ prdid，SqlDbType.VarChar）.Value = mtbprdid.Text.ToString（）; 
 cmd.Parameters.Add（  @ quantity，SqlDbType.VarChar）.Value = txtqty.Text.ToString（）; 
 cmd.Parameters.Add（  @ itemtotal，SqlDbType.VarChar）.Value = txttotalstkqty.Text.ToString（）; 
 cmd.ExecuteNonQuery（）; 
} 
} 
} 

问题从这个方法开始

 私有  void  store_detailOperation（）

您在这里打开了一个连接并执行数据库操作并在此处打开连接

 ConnectionString.cnn.Open（）; 
 DataSet ds =  new  DataSet（）; 
 
 SqlDataAdapter da =  new  SqlDataAdapter（  select * from store_detail，其中prdid =' + mtbprdid.Text +  '，ConnectionString.cnn）; 
  int  s =（da.Fill（ds））; 

所以在此调用后< b>''store_detailUpdation（）''或 store_detailinsertion（）在这些方法中，您尝试再次打开已为上述select语句打开的连接。从错误消息中可以看出这一点。



在尝试再次打开数据库连接之前添加连接状态检查的常见和最佳做法。



即在''store_detailUpdation，store_detailinsertion 方法中添加连接状态检查，如下所示

< pre lang =cs> public void store_detailUpdation（）
{
< span class =code-keyword> try
{
if （ConnectionString.cnn.State == ConnectionState.Open）
{
ConnectionString.cnn.Close（）;
}
ConnectionString.cnn.Open（）;
....
....
....

give me this type error again and again:
Additional information: The connection was not closed. The connection''s current state is open.
in the following code.

public void store_detailinsertion()
        {

            try
            {
                ConnectionString.cnn.Open();
                cmd.Parameters.Clear();
                cmd = new SqlCommand("Insert into store_detail (prdid,quantity,itemtotal) Values (@prdid,@quantity,@itemtotal)", ConnectionString.cnn);
                cmd.Parameters.Add("@prdid", SqlDbType.VarChar).Value = mtbprdid.Text.ToString();
                cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = txtqty.Text.ToString();
                cmd.Parameters.Add("@itemtotal", SqlDbType.VarChar).Value = txttotalstkqty.Text.ToString();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {

                ConnectionString.cnn.Close();
                ConnectionString.cnn.Dispose();
            }


        }

public void store_detailUpdation()
      {

          try
          {
              ConnectionString.cnn.Open();

              cmd.Connection = ConnectionString.cnn;
              ConnectionString.cnn.Open();
              cmd.CommandText = "Update store_detail set itemtotal=''" + txttotalstkqty.Text + "'',quantity=''" + txtqty.Text + "'' Where prdid=''" + mtbprdid.Text + "''";

              cmd.ExecuteNonQuery();
              cmd.Connection.Close();
          }
          catch (Exception)
          {
              throw;
          }
          finally
          {
              ConnectionString.cnn.Close();
              ConnectionString.cnn.Dispose();
          }

      }

 private void store_detailOperation()
        {
            try
            {
                ConnectionString.cnn.Open();
                DataSet ds = new DataSet();
               

                SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid=''" + mtbprdid.Text + "''", ConnectionString.cnn);
                int s = (da.Fill(ds));
                if (s >= 1)
                {
                    store_detailUpdation();
                    //DisableTextBoxs();
                    //EnableButtons();
                    //btnsave.Enabled = false;
                    //ConnectionString.cnn.Close();
                }
                else
                {

                    store_detailinsertion();
                    //DisbleTextBoxs();
                    //EnableButtons();
                    //btnsave.Enabled = false;
                }

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionString.cnn.Close();
                ConnectionString.cnn.Dispose();
            }
        }

Plzzz, Anyone Can Help Me

解决方案

With this code in store_detailUpdation, you set a Connection object to an already open Connection object. This is probably the source of the error message.

cmd.Connection = ConnectionString.cnn;
ConnectionString.cnn.Open();

In store_detailUpdation, you are closing the connection twice. Once in the Try block and once in the Finally block.

These both close the database connection:

cmd.Connection.Close();

ConnectionString.cnn.Close();

See the documentation for try-finally (C# Reference)[^]

If at all possible its best to use a using block with your command and connection objects.

public void store_detailinsertion(string connectionString, string sqlText)
{ 
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using(SqlCommand cmd = new SqlCommand(sqlText,conn))
        {
             cmd.Parameters.Add("@prdid", SqlDbType.VarChar).Value = mtbprdid.Text.ToString();
             cmd.Parameters.Add("@quantity", SqlDbType.VarChar).Value = txtqty.Text.ToString();
             cmd.Parameters.Add("@itemtotal", SqlDbType.VarChar).Value = txttotalstkqty.Text.ToString();
             cmd.ExecuteNonQuery();
        }
     }
}

The problem starts from this method

private void store_detailOperation()

You opened a connection here and performed a database operation and leaving the connection open in here

ConnectionString.cnn.Open();
DataSet ds = new DataSet();
               
SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid='" + mtbprdid.Text + "'", ConnectionString.cnn);
int s = (da.Fill(ds));

so after this invoking ''store_detailUpdation()'' or store_detailinsertion() inside these method you are trying to open the connection again which is already opened for above select statement. and It''s very obvious from the error message.

it''s a common and best practice to add a Connection state checking before you try to open database connection again.

i.e add the conneciton state checking inside the ''store_detailUpdation,store_detailinsertion methods as shown below

public void store_detailUpdation()
{
   try
   {
        if(ConnectionString.cnn.State == ConnectionState.Open)
        {
          ConnectionString.cnn.Close();
        }
        ConnectionString.cnn.Open();
        ....
        ....
        ....

这篇关于附加信息：连接未关闭。连接的当前状态是打开的。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！