CODEWITHSUNDEEP
c#databasems-accessms-access-2007
Mike Smith
Mike Smith

Reputation: 13

C# code cannot insert a row into an Access table

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?

Upvotes: 0

Views: 2867

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

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

Upvotes: 1

Related Questions