如何使用oledb将记录插入访问表? [英] How to insert a record into a access table using oledb?

问题描述

我在 ms access 中有一个 this Items 表

I have a this Items table in ms access

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?

推荐答案

你的 sql 插入文本不使用参数.
这是错误和更糟的原因 (SqlInjection)

Your sql insert text doesn't use parameters.
This is the cause of bugs and worse (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;
}

然后使用 price 作为参数 @price

then use price as value for the parameter @price

**4 年后编辑 **

**EDIT 4 YEARS LATER **

这个答案需要更新.在上面的代码中，我使用 AddWithValue 将参数添加到 Parameters 集合中.它有效，但应告知每位读者 AddWithValue 有一些缺点.特别是如果您喜欢在目标列需要十进制值或日期时只添加字符串的简单方法.在这种情况下，如果我刚刚写了

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

cmd.Parameters.AddWithValue("@price", itemPriceTBox.Text); 

结果可能是语法错误或值的某种奇怪转换，日期也可能发生同样的情况.AddWithValue 创建一个字符串参数，数据库引擎应该将该值转换为预期的列类型.但是客户端和服务器之间的语言环境差异可能会导致对该值的任何类型的误解.

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.

我认为使用总是更好

cmd.Parameters.Add("@price", OleDbType.Decimal).Value = 
           Convert.ToDecimal(itemPriceTBox.Text); 

有关 AddWithValue 问题可以在这里找到

这篇关于如何使用oledb将记录插入访问表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！