C# code cannot insert a row into an Access table

Question

I'm having difficulty manipulating an Access 2007 database. My code is this:

using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\User\\db.accdb;Jet OLEDB:Database Password=" + password + ";Persist Security Info=False"))
{
    connection.Open();
    string query = "INSERT INTO Book(Name, Author) VALUES('Hello', 'World')"; ;
    OleDbCommand cmd = new OleDbCommand(query, connection);
    cmd.ExecuteNonQuery();
    connection.Close();
}

Why can't I write the record to the table of that database? How can I view the cell of the record?

What happens when you run it?

Jeff
– Jeff

2013-10-16 15:23:45 +00:00
Commented Oct 16, 2013 at 15:23 — Jeff
– Jeff, Commented Oct 16, 2013 at 15:23

Gord Thompson · Accepted Answer · 2013-10-17 10:35:12Z

1

Name is a reserved word in Access SQL, so you need to wrap that column name in square brackets ([]). Try this instead:

string query = "INSERT INTO Book ([Name], Author) VALUES ('Hello', 'World')";

Edit re: comments

With the information presented so far I can see no reason why your INSERT query should fail with "Operation must use an updateable query". For what it's worth, the following C# code successfully adds a row to the [Book] table...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace oleDbTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string myConnectionString;
            myConnectionString =
                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                    @"Data Source=C:\Users\Public\Database1.accdb;";

            using (var con = new OleDbConnection())
            {
                con.ConnectionString = myConnectionString;
                con.Open();

                using (var cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText =
                        @"INSERT INTO Book (Denomination, Author) VALUES ('Hello', 'World')";
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
            Console.WriteLine("Done.");
        }
    }
}

...like so:

Denomination  Author
------------  ------
Hello         World

edited Oct 17, 2013 at 10:35

answered Oct 16, 2013 at 15:44

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Smith Over a year ago

I've changed "Name" with "Denomination" but not function because the error is below "Operation must use an updateable query"

Gord Thompson Over a year ago

@MikeSmith Can you open the database in Access itself and verify that you can add a new row to [Book] by typing values into a Datasheet View?

Mike Smith Over a year ago

In Access you can insert a new row, but in C# code from the above error

Collectives™ on Stack Overflow

C# code cannot insert a row into an Access table

1 Answer 1

Edit re: comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit re: comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related