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

问题描述

我有一个在MS Access这个项目表

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 INSERT文本没有b该错误的原因使用的参数。结果$ b $更糟的（SQL注入攻击）

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

**编辑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.

我认为，它始终是更好地使用

I think that it is always better to use

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

上的 /相对=nofollow> addWithValue问题

More info on AddWithValue problems can be found here

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