一次又一次地给我这种类型错误: 附加信息:连接未关闭。连接的当前状态是打开的。 在以下代码中。
public void store_detailinsertion() { try { ConnectionStringn.Open(); cmd.Parameters.Clear(); cmd = new SqlCommand(Insert into store_detail(prdid,quantity,itemtotal)Values(@ prdid,@ quantity,@ itemtotal),ConnectionStringn); 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; } 最后 { ConnectionStringn.Close(); ConnectionStringn.Dispose(); } } public void store_detailUpdation() { try { ConnectionStringn.Open(); cmd.Connection = ConnectionStringn; ConnectionStringn.Open(); cmd.CommandText =更新store_detail set itemtotal =''+ txttotalstkqty.Text +'',quantity =''+ txtqty.Text +''其中prdid =''+ mtbprdid.Text + ''; cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch(例外) { throw; } 最后 { ConnectionStringn.Close(); ConnectionStringn.Dispose(); } } private void store_detailOperation() { try { ConnectionStringn。打开(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(select * from store_detail where prdid =''+ mtbprdid.Text +'',ConnectionStringn); int s =(da.Fill(ds)); if(s> = 1) { store_detailUpdation(); // DisableTextBoxs(); // EnableButtons(); //btnsave.Enabled = false; //ConnectionStringn.Close(); } else { store_detailinsertion(); // DisbleTextBoxs(); // EnableButtons(); //btnsave.Enabled = false; } } catch(例外) { throw; } 最后 { ConnectionStringn.Close(); ConnectionStringn.Dispose(); } }Plzzz,任何人都可以帮助我
解决方案使用store_detailUpdation中的此代码,将Connection对象设置为已打开的Connection对象。这可能是错误消息的来源。 cmd.Connection = ConnectionStringn; ConnectionStringn.Open();
在store_detailUpdation中,您将关闭连接两次。进入尝试块,然后进入最后块。 这两个都关闭数据库连接:
cmd.Connection.Close();ConnectionStringn.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()
您在这里打开了一个连接并执行数据库操作并在此处打开连接
ConnectionStringn.Open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter( select * from store_detail,其中prdid =' + mtbprdid.Text + ',ConnectionStringn); int s =(da.Fill(ds));所以在此调用后''store_detailUpdation()''或 store_detailinsertion()在这些方法中,您尝试再次打开已为上述select语句打开的连接。从错误消息中可以看出这一点。 在尝试再次打开数据库连接之前添加连接状态检查的常见和最佳做法。 即在''store_detailUpdation,store_detailinsertion 方法中添加连接状态检查,如下所示 public void store_detailUpdation() { try { if (ConnectionStringn.State == ConnectionState.Open) { ConnectionStringn.Close(); } ConnectionStringn.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 { ConnectionStringn.Open(); cmd.Parameters.Clear(); cmd = new SqlCommand("Insert into store_detail (prdid,quantity,itemtotal) Values (@prdid,@quantity,@itemtotal)", ConnectionStringn); 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 { ConnectionStringn.Close(); ConnectionStringn.Dispose(); } } public void store_detailUpdation() { try { ConnectionStringn.Open(); cmd.Connection = ConnectionStringn; ConnectionStringn.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 { ConnectionStringn.Close(); ConnectionStringn.Dispose(); } } private void store_detailOperation() { try { ConnectionStringn.Open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid=''" + mtbprdid.Text + "''", ConnectionStringn); int s = (da.Fill(ds)); if (s >= 1) { store_detailUpdation(); //DisableTextBoxs(); //EnableButtons(); //btnsave.Enabled = false; //ConnectionStringn.Close(); } else { store_detailinsertion(); //DisbleTextBoxs(); //EnableButtons(); //btnsave.Enabled = false; } } catch (Exception) { throw; } finally { ConnectionStringn.Close(); ConnectionStringn.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 = ConnectionStringn; ConnectionStringn.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();ConnectionStringn.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
ConnectionStringn.Open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select * from store_detail where prdid='" + mtbprdid.Text + "'", ConnectionStringn); 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(ConnectionStringn.State == ConnectionState.Open) { ConnectionStringn.Close(); } ConnectionStringn.Open(); .... .... ....更多推荐
附加信息:连接未关闭。连接的当前状态是打开的。
发布评论