insert statement C# with MS Access

Question

Hi guys I have got a simple insert statement, I know something is missing as when I click the button to insert the data it dose not insert. any ideas?

 protected void saveyear_Click(object sender, EventArgs e)
{
    OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\ASPNetDB.mdb;Persist Security Info=True");
    OleDbDataAdapter da = new OleDbDataAdapter();

    da.InsertCommand = new OleDbCommand("INSERT INTO DVD_Infomation (Year) VALUES (@Year)", connection);
    {
        da.InsertCommand.Parameters.AddWithValue("@Year", Year.Text);
    }

    connection.Open();

        da.InsertCommand.ExecuteNonQuery();

    connection.Close();
}

Steve · Accepted Answer · 2013-12-20 19:02:44Z

YEAR is a reserved keyword for MS-Access Jet.
If you want to use it as name of your column you should enclose it in square brackets

da.InsertCommand = new OleDbCommand("INSERT INTO DVD_Infomation ([Year]) VALUES (@Year)", connection);
{
    da.InsertCommand.Parameters.AddWithValue("@Year", Year.Text);
}

If it is still possible, I suggest to change the name of that column. It will be annoying to stump on this error every time you try to use that column in your statements

As a side note, I would write the code above as

using(OleDbConnection connection = new OleDbConnection(.....))
using(OleDbCommand cmd = new OleDbCommand("INSERT INTO DVD_Infomation ([Year]) VALUES (@Year)", connection))
{
    cmd.Parameters.AddWithValue("@Year", Year.Text);
    connection.Open();
    cmd.ExecuteNonQuery();
}

Adding the using statement around the creation of the connection and the command will ensure a proper closing and disposing of these objects also in case of exceptions and keep your program more resource usage friendly

Johnny Bones · Accepted Answer · 2013-12-20 18:59:51Z

2

Never use a Reserved word in your tables, if you can help it. Since YEAR is a Reserved word, you need to use brackets around the name. Preferrably, change the field name in your table to avoid that issue in the future. Similarly, using field names with spaces in them is also frowned upon as it, too, creates problems when referencing them.

answered Dec 20, 2013 at 18:59

Johnny Bones

8,5027 gold badges61 silver badges127 bronze badges

Collectives™ on Stack Overflow

insert statement C# with MS Access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related