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

Question

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?

Solution

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

Comments

[mepk]

one problem now is record is inserted and can be view from gridview at runtime but when i stop the project execution and preview data in the table there is no data and when i again run the project there is no data in the gridview.

[Steve]

Difficult to say, check your connection string. The code above should work. Add some record with Access inside the table and check if they are visible on your grid.

[mepk]

after inserting data into access table the gridview is showing the records. but new record is not inserted into the table.

[Roland]

Try committing your changes to the database. You may not have autocommit enabled.

[Steve]

If you still have problems with saving your data then you could check this answer about problems with the DataDirectory substitution string in the connection string Why saving changes to database fails?