C# 如何使用oledb将记录插入访问表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10941284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert a record into a access table using oledb?
提问by mepk
I have a this Items table in ms access
我在 ms access 中有一个 this Items 表
Items(Table)
Item_Id(autonumber)
Item_Name(text)
Item_Price(currency)
and i'm trying to insert a record using this code.
我正在尝试使用此代码插入记录。
OleDbConnection myCon = new OleDbConnection(ConfigurationManager.ConnectionStrings["DbConn"].ToString());
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Items ([Item_Name],[Item_Price]) values ('" + itemNameTBox.Text + "','" + Convert.ToDouble(itemPriceTBox.Text) + "')";
cmd.Connection = myCon;
myCon.Open();
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("An Item has been successfully added", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
myCon.Close();
Code is running without error but at the end no record is found in the table what mistake i'm doing?
代码运行没有错误，但最后在表中没有找到记录我在做什么错误？
采纳答案by Steve
Your sql insert text doesn't use parameters.
This is the cause of bugs and worse (SqlInjection)
您的 sql 插入文本不使用参数。
这是错误和更糟的原因（SqlInjection）
Change your code in this way;
以这种方式更改您的代码；
using(OleDbConnection myCon = new OleDbConnection(ConfigurationManager.ConnectionStrings["DbConn"].ToString()))
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Items ([Item_Name],[Item_Price]) values (?,?);
cmd.Parameters.AddWithValue("@item", itemNameTBox.Text);
cmd.Parameters.AddWithValue("@price", Convert.ToDouble(itemPriceTBox.Text));
cmd.Connection = myCon;
myCon.Open();
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("An Item has been successfully added", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
Of course this assumes that the text box for price contains a correct numeric value.
To be sure add this line before calling the code above
当然，这假设价格的文本框包含正确的数值。
在调用上面的代码之前一定要添加这一行
double price;
if(double.TryParse(itemPriceTBox.Text, out price) == false)
{
MessageBox.Show("Invalid price");
return;
}
then use priceas value for the parameter @price
然后price用作参数的值@price
**EDIT 4 YEARS LATER **
** 4 年后编辑 **
This answer needs an update. In the code above I use AddWithValue to add a parameter to the Parameters collection. It works but every reader should be advised that AddWithValue has some drawbacks. In particular if you fall for the easy path to add just strings when the destination column expects decimal values or dates. In this context if I had written just
这个答案需要更新。在上面的代码中，我使用 AddWithValue 将参数添加到 Parameters 集合中。它有效，但应告知每位读者 AddWithValue 有一些缺点。特别是如果您喜欢在目标列需要十进制值或日期时只添加字符串的简单方法。在这种情况下，如果我刚刚写了
cmd.Parameters.AddWithValue("@price", itemPriceTBox.Text);
the result could be a syntax error or some kind of weird conversion of the value and the same could happen with dates. AddWithValue creates a string Parameter and the database engine should convert the value to the expected column type. But differences in locale between the client and the server could create any kind of misinterpretation of the value.
结果可能是语法错误或某种奇怪的值转换，日期也可能发生同样的情况。AddWithValue 创建一个字符串参数，数据库引擎应该将该值转换为预期的列类型。但是客户端和服务器之间的语言环境差异可能会导致对该值的任何类型的误解。
I think that it is always better to use
我认为使用总是更好
cmd.Parameters.Add("@price", OleDbType.Decimal).Value =
Convert.ToDecimal(itemPriceTBox.Text);
More info on AddWithValue problems can be found here
可以在此处找到有关AddWithValue 问题的更多信息
