错误:ExecuteNonQuery需要一个打开的可用连接
本文关键字：一个 连接 ExecuteNonQuery 错误 | 更新日期: 2023-09-27 18:03:25
我正在创建一个系统，允许用户在文本框控件中输入10个数据，然后保存在数据库中。但这个错误让我股票了一段时间，我不知道我错在哪里。有人能帮我一下吗?请提前表示感谢。也可以随意编辑我的问题。
下面是我的代码:
private void btnAdd_Click(object sender, EventArgs e)
{
DateTime getdate = DateTime.Now;
String time = getdate.ToString("F");
try
{
con.Open();
var rows = new[]
{
new {Item = txtItem.Text, Product = txtProduct.Text, Quantity = txtQuantity.Text},
new {Item = txtItem2.Text, Product = txtProduct2.Text, Quantity = txtQuantity2.Text},
new {Item = txtItem3.Text, Product = txtProduct3.Text, Quantity = txtQuantity3.Text},
new {Item = txtItem4.Text, Product = txtProduct4.Text, Quantity = txtQuantity4.Text},
new {Item = txtItem5.Text, Product = txtProduct5.Text, Quantity = txtQuantity5.Text},
new {Item = txtItem6.Text, Product = txtProduct6.Text, Quantity = txtQuantity6.Text},
new {Item = txtItem7.Text, Product = txtProduct7.Text, Quantity = txtQuantity7.Text},
new {Item = txtItem8.Text, Product = txtProduct8.Text, Quantity = txtQuantity8.Text},
new {Item = txtItem9.Text, Product = txtProduct9.Text, Quantity = txtQuantity9.Text},
new {Item = txtItem10.Text, Product = txtProduct10.Text, Quantity = txtQuantity10.Text}
};
foreach (var row in rows)//Check if ItemCode exist
{
if (!String.IsNullOrEmpty(row.Item))
{
OleDbCommand command = new OleDbCommand(@"Select * from TblInventory where ItemCode='" + txtItem.Text + "'");
command.Connection = con;
command.Parameters.AddWithValue("itemcode", txtItem.Text);
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
OleDbCommand cmd = new OleDbCommand(@"Update TblInventory set Quantity = Quantity + @Quantity WHERE ItemCode = @itemcode");
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtQuantity.Text));
txtProduct.Text = reader["ProductName"].ToString();
cmd.Parameters.AddWithValue("@itemcode", txtItem.Text);
cmd.Parameters.AddWithValue("@DateAndTime", time);
cmd.ExecuteNonQuery();// HERE <<<
MessageBox.Show("You added " + txtQuantity.Text + " " + txtProduct.Text, "Existing Item");
}
}
else //Add new Data if Item Code is not exit;
{
var rows2 = new[]
{
new {Item = txtItem.Text, Product = txtProduct.Text, Quantity = txtQuantity.Text},
new {Item = txtItem2.Text, Product = txtProduct2.Text, Quantity = txtQuantity2.Text},
new {Item = txtItem3.Text, Product = txtProduct3.Text, Quantity = txtQuantity3.Text},
new {Item = txtItem4.Text, Product = txtProduct4.Text, Quantity = txtQuantity4.Text},
new {Item = txtItem5.Text, Product = txtProduct5.Text, Quantity = txtQuantity5.Text},
new {Item = txtItem6.Text, Product = txtProduct6.Text, Quantity = txtQuantity6.Text},
new {Item = txtItem7.Text, Product = txtProduct7.Text, Quantity = txtQuantity7.Text},
new {Item = txtItem8.Text, Product = txtProduct8.Text, Quantity = txtQuantity8.Text},
new {Item = txtItem9.Text, Product = txtProduct9.Text, Quantity = txtQuantity9.Text},
new {Item = txtItem10.Text, Product = txtProduct10.Text, Quantity = txtQuantity10.Text}
};
foreach (var row2 in rows)
{
if (!String.IsNullOrEmpty(row2.Item) && !String.IsNullOrEmpty(row2.Product) && !String.IsNullOrEmpty(row2.Quantity))
{
OleDbCommand cmdInsert = new OleDbCommand(
@"insert into TblInventory (ItemCode,ProductName,Quantity,DateAndTime)values(@ItemCode,@ProductName,@Quantity,@DateAndTime)");
cmdInsert.Connection = con;
cmdInsert.Parameters.AddWithValue("ItemCode", row2.Item);
cmdInsert.Parameters.AddWithValue("ProductName", row2.Product);
cmdInsert.Parameters.AddWithValue("Quantity", row2.Quantity.ToString());
cmdInsert.Parameters.AddWithValue("DateAndTime", DateTime.Now);
cmdInsert.ExecuteNonQuery(); HERE <<<
MessageBox.Show("You added " + row.Quantity + " " + row.Product, "New Item");
}
}
}
showGrid2();
con.Close();
clear();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
这是我第一次回答问题，如果我误解了什么，请原谅我。
从我所看到的，在foreach循环中，cmdInsert.ExecuteNonQuery();在 con.Close();之后被称为。因为每次迭代完成if子句时都调用con.Close();。
可以试试这样写:
private void btnAdd_Click(object sender, EventArgs e)
{
DateTime getdate = DateTime.Now;
String time = getdate.ToString("F");
try
{
var rows = new[]
{
new {Item = txtItem.Text, Product = txtProduct.Text, Quantity = txtQuantity.Text},
new {Item = txtItem2.Text, Product = txtProduct2.Text, Quantity = txtQuantity2.Text},
new {Item = txtItem3.Text, Product = txtProduct3.Text, Quantity = txtQuantity3.Text},
new {Item = txtItem4.Text, Product = txtProduct4.Text, Quantity = txtQuantity4.Text},
new {Item = txtItem5.Text, Product = txtProduct5.Text, Quantity = txtQuantity5.Text},
new {Item = txtItem6.Text, Product = txtProduct6.Text, Quantity = txtQuantity6.Text},
new {Item = txtItem7.Text, Product = txtProduct7.Text, Quantity = txtQuantity7.Text},
new {Item = txtItem8.Text, Product = txtProduct8.Text, Quantity = txtQuantity8.Text},
new {Item = txtItem9.Text, Product = txtProduct9.Text, Quantity = txtQuantity9.Text},
new {Item = txtItem10.Text, Product = txtProduct10.Text, Quantity = txtQuantity10.Text}
};
foreach (var row in rows)//Check if ItemCode exist
{
con.Open();
if (!String.IsNullOrEmpty(row.Item))
{
OleDbCommand command = new OleDbCommand(@"Select * from TblInventory where ItemCode='" + txtItem.Text + "'");
command.Connection = con;
command.Parameters.AddWithValue("itemcode", txtItem.Text);
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
OleDbCommand cmd = new OleDbCommand(@"Update TblInventory set Quantity = Quantity + @Quantity WHERE ItemCode = @itemcode");
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtQuantity.Text));
txtProduct.Text = reader["ProductName"].ToString();
cmd.Parameters.AddWithValue("@itemcode", txtItem.Text);
cmd.Parameters.AddWithValue("@DateAndTime", time);
cmd.ExecuteNonQuery();// HERE
MessageBox.Show("You added " + txtQuantity.Text + " " + txtProduct.Text, "Existing Item");
}
}
else //Add new Data if Item Code is not exit;
{
var rows2 = new[]
{
new {Item = txtItem.Text, Product = txtProduct.Text, Quantity = txtQuantity.Text},
new {Item = txtItem2.Text, Product = txtProduct2.Text, Quantity = txtQuantity2.Text},
new {Item = txtItem3.Text, Product = txtProduct3.Text, Quantity = txtQuantity3.Text},
new {Item = txtItem4.Text, Product = txtProduct4.Text, Quantity = txtQuantity4.Text},
new {Item = txtItem5.Text, Product = txtProduct5.Text, Quantity = txtQuantity5.Text},
new {Item = txtItem6.Text, Product = txtProduct6.Text, Quantity = txtQuantity6.Text},
new {Item = txtItem7.Text, Product = txtProduct7.Text, Quantity = txtQuantity7.Text},
new {Item = txtItem8.Text, Product = txtProduct8.Text, Quantity = txtQuantity8.Text},
new {Item = txtItem9.Text, Product = txtProduct9.Text, Quantity = txtQuantity9.Text},
new {Item = txtItem10.Text, Product = txtProduct10.Text, Quantity = txtQuantity10.Text}
};
foreach (var row2 in rows)
{
if (!String.IsNullOrEmpty(row2.Item) && !String.IsNullOrEmpty(row2.Product) && !String.IsNullOrEmpty(row2.Quantity))
{
OleDbCommand cmdInsert = new OleDbCommand(
@"insert into TblInventory (ItemCode,ProductName,Quantity,DateAndTime)values(@ItemCode,@ProductName,@Quantity,@DateAndTime)");
cmdInsert.Connection = con;
cmdInsert.Parameters.AddWithValue("ItemCode", row2.Item);
cmdInsert.Parameters.AddWithValue("ProductName", row2.Product);
cmdInsert.Parameters.AddWithValue("Quantity", row2.Quantity.ToString());
cmdInsert.Parameters.AddWithValue("DateAndTime", DateTime.Now);
cmdInsert.ExecuteNonQuery(); HERE
MessageBox.Show("You added " + row.Quantity + " " + row.Product, "New Item");
}
}
}
showGrid2();
clear();
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
use Using打开连接，并自动处理连接的关闭。因此，最佳和高效的做法是尽可能晚地打开连接，并尽快关闭它。
foreach (var row in rows)//Check if ItemCode exist
{
if (!String.IsNullOrEmpty(row.Item))
{
OleDbCommand command = new OleDbCommand(@"Select * from TblInventory where ItemCode='" + txtItem.Text + "'");
using (command.Connection = con)
{
command.Parameters.AddWithValue("itemcode", txtItem.Text);
con.open;
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
OleDbCommand cmd = new OleDbCommand(@"Update TblInventory set Quantity = Quantity + @Quantity WHERE ItemCode = @itemcode");
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Quantity", Convert.ToInt32(txtQuantity.Text));
txtProduct.Text = reader["ProductName"].ToString();
cmd.Parameters.AddWithValue("@itemcode", txtItem.Text);
cmd.Parameters.AddWithValue("@DateAndTime", time);
cmd.ExecuteNonQuery();// HERE
MessageBox.Show("You added " + txtQuantity.Text + " " + txtProduct.Text, "Existing Item");
}
}
}
}
//dont use con.Close anywhere in the code .