c# - Insert arbitrary values into AutoNumber field via OleDbDataAdapter.Update -

i have table in access database

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

here, id filed primary key - autonumber field. now, how can insert record id 11 table using c# ?

when try insertions id under 13 or above 14 next inserted value 14. happens if want insert deleted record table ?

any workarounds or actual solutions addressing problem appreciated.

edit 1:

its hear possible insert, values autonumber field. here function use make bulk insertions. datatable has same column names similar data types. code works said above, primary keys assigned automatically.

might there kind of fix particular code ?

public void accessbulkcopy(datatable table) {     foreach (datarow r in table.rows)         r.setadded();      var myadapter = new oledbdataadapter("select * " + table.tablename, _myaccessconn);      var cbr = new oledbcommandbuilder(myadapter);     cbr.quoteprefix = "[";     cbr.quotesuffix = "]";     cbr.getinsertcommand(true);      myadapter.update(table); } 

the access database engine does indeed allow use sql insert arbitrary values autonumber field. following work in case, provided there isn't row in table [id]=11:

string sql = "insert [yourtable] ([id], [name]) values (?,?)"; using (var cmd = new odbccommand(sql, conn)) {     cmd.parameters.addwithvalue("?", 11);     cmd.parameters.addwithvalue("?", "gord");     cmd.executenonquery(); } 

edit

with oledbdataadapter, insertcommand automatically generated oledbcommandbuilder recognizes [id] autonumber column , omits commandtext ("insert ..."). so, "id" column in datatable being ignored , getting automatically-assigned [id] values.

if want [id] column included in insert need create own oledbdataadapter.insertcommand so:

using (var da = new oledbdataadapter("select [id], [name] [yourtable] 1=0", conn)) {     datatable dt = new datatable();     da.fill(dt);      var cmd = new oledbcommand("insert [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";     dt.rows.add(dr);     da.update(dt); }