Insert new DataRow with autoincrement Primary Key with C# (OleDB)

Question

I' m trying to insert a new DataRow (locally stored in a DataSet) to a Access table with C#. The table is also created by this app and has 3 columns:

• ID (integer, primary key, Required is set to No though I've set it as primary key)
• Name (string)
• Money (int)

My current code looks like that:

        OleDbConnection con = new OleDbConnection();
        OleDbCommand cmd = new OleDbCommand();
        OleDbDataAdapter da;
        DataSet ds = new DataSet();
        DataView dv = new DataView();
        DataRow row;

        string Con = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=";
        string path = "V:\\ProjectProgress\\Test.mdb";
        con.ConnectionString = Con + path;

        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        cmd.Connection = con;

        cmd.CommandText = "SELECT ID, Name, Money FROM Test";
        da = new OleDbDataAdapter(cmd);
        da.TableMappings.Add("Table", "Test");
        da.Fill(ds, "Test");

        ds.Tables["Test"].Columns[0].AutoIncrement = true;
        ds.Tables["Test"].Columns[0].AutoIncrementSeed = -1;
        ds.Tables["Test"].Columns[0].AutoIncrementStep = -1;

        dv.Table = ds.Tables["Test"];

        row = ds.Tables["Test"].NewRow();
        row["Name"] = "Huber";
        row["Money"] = 100;

        ds.Tables["Test"].Rows.Add(row);

        string strOLE = "INSERT INTO Test ([Name], [Money]) Values(@Name, @Money)";

        OleDbCommand cmdi = new OleDbCommand(strOLE, con);
        cmdi.Parameters.Add("@Name", OleDbType.VarChar, 25, "Name");
        cmdi.Parameters.Add("@Money", OleDbType.Integer, 4, "Money");


        da.InsertCommand = cmdi;

        da.Update(ds.Tables["Test"]);

        con.Close();

When updating I'm always getting a

Index or primary key cannot contain a Null value

error.

Setting the Required value of the ID column to Yes, will throw a

Index or Primary Key Cannot Contain a Null Value

error.

How can I let Access assign the right primary key and how do I get the new value back into my dataset?

Using SCOPE_IDENTITY() is not possible in Access as far as I know and found in some forums.

(Working with Visual C# Express 2010, Access 2003)

Answer 1

The following is complete working test code to illustrate the procedure. All we need to do is provide an OleDbDataAdapter.SelectCommand that includes the primary key and the columns we want to update, and the OleDbCommandBuilder object will create the INSERT statement for us:

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 (OleDbConnection con = new OleDbConnection())
            {
                con.ConnectionString = myConnectionString;
                con.Open();

                using (OleDbDataAdapter da = new OleDbDataAdapter())
                using (OleDbCommandBuilder bld = new OleDbCommandBuilder(da))
                {
                    bld.QuotePrefix = "[";  // these are
                    bld.QuoteSuffix = "]";  //   important!

                    da.SelectCommand = new OleDbCommand(
                            "SELECT [ID], [Name], [Money] " +
                            "FROM [Test] " +
                            "WHERE False",
                            con);
                    using (System.Data.DataTable dt = new System.Data.DataTable("Test"))
                    {
                        // create an empty DataTable with the correct structure
                        da.Fill(dt);

                        System.Data.DataRow dr = dt.NewRow();
                        dr["Name"] = "Huber";
                        dr["Money"] = 100;
                        dt.Rows.Add(dr);

                        da.Update(dt);  // write new row back to database
                    }
                }
                con.Close();
            }
            Console.WriteLine();
            Console.WriteLine("Done.");
        }
    }
}

Answer 2

My first thought is that your problem might be that both AutoIncrementSeed and AutoIncrementStep are set to a negative value of -1. Try setting both to a positive value.

[EDIT]

Second thought, you might want to try the OleDbCommandBuilder (MSDN documentation here). It creates your INSERT, UPDATE, and DELETE statements automatically using the column information of your DataSet. At my former employer we used OleDbCommandBuilder all the time when working with Access and it worked like a charm, even with auto increment fields.