0014 0014 - 11 months ago 87
C# Question

Insert arbitrary values into AutoNumber field via OleDbDataAdapter.Update

I have a table like this in my Access Database

id name address date
1 first addresss 5/6/2005 9:17:52 AM
2 test1 address1 5/11/2005 5:23:32 AM
3 test22 address2 5/11/2006 5:23:32 AM
8 test3 address3 5/11/2007 5:23:32 AM
9 test4 address4 5/11/2008 5:23:32 AM
13 test address 5/11/2008 5:23:32 AM

filed is an
Primary Key
field. Now, how can I insert a record with an id 11 into this table using

When I try insertions with id under 13 or above 14 the next inserted value will be 14. What happens if I want to insert a deleted record back into the table ?

Any workarounds or actual solutions addressing this problem will be appreciated.

Edit 1:

Its good to hear that its possible to insert, values into an
field. Here is the function that I use to make bulk insertions. The DataTable has the same column names with similar data types. The code works however as I said above, the primary keys are assigned automatically.

Might there be any kind of fix for this particular code ?

public void AccessBulkCopy(DataTable table)
foreach (DataRow r in table.Rows)

var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn);

var cbr = new OleDbCommandBuilder(myAdapter);
cbr.QuotePrefix = "[";
cbr.QuoteSuffix = "]";


Answer Source

The Access Database Engine does indeed allow us to use SQL to insert arbitrary values into an AutoNumber field. The following will work in your case, provided that there isn't already a row in the table with [id]=11:

string sql = "INSERT INTO [YourTable] ([id], [name]) VALUES (?,?)";
using (var cmd = new OdbcCommand(sql, conn))
    cmd.Parameters.AddWithValue("?", 11);
    cmd.Parameters.AddWithValue("?", "Gord");


With your OleDbDataAdapter, the InsertCommand that is automatically generated by the OleDbCommandBuilder recognizes that [id] is an AutoNumber column and omits it from the CommandText ("INSERT INTO ..."). So, the "id" column in your DataTable was being ignored and you were getting automatically-assigned [id] values.

If you want the [id] column included in the INSERT then you need to create your own OleDbDataAdapter.InsertCommand like so:

using (var da = new OleDbDataAdapter("SELECT [id], [name] FROM [YourTable] WHERE 1=0", conn))
    DataTable dt = new DataTable();

    var cmd = new OleDbCommand("INSERT INTO [YourTable] ([id], [name]) VALUES (?,?)", conn);
    cmd.Parameters.Add(new OleDbParameter("?", OleDbType.Integer, 0, "id"));
    cmd.Parameters.Add(new OleDbParameter("?", OleDbType.VarWChar, 255, "name"));
    da.InsertCommand = cmd;

    DataRow dr = dt.NewRow();
    dr["id"] = 11;
    dr["name"] = "Gord";