How to insert a record into a access table using oledb?

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?

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;
}

then use price as value for the parameter @price

**EDIT 4 YEARS LATER **

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);

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);

More info on AddWithValue problems can be found here

How to insert a record into a access table using oledb?

Related

Recent Posts