CODEWITHSUNDEEP
c#databasems-accessinsert
0014
0014

Reputation: 933

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

Here, id filed is an Primary Key - AutoNumber field. Now, how can I insert a record with an id 11 into this table using C# ?

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 AutoNumber 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)
        r.SetAdded();

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

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

    myAdapter.Update(table);
}

Upvotes: 2

Views: 1101

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

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");
    cmd.ExecuteNonQuery();
}

Edit

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();
    da.Fill(dt);

    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";
    dt.Rows.Add(dr);
    da.Update(dt);
}

Upvotes: 2

Knox
Knox

Reputation: 2919

You shouldn't. An auto-number, primary key field in MS Access should not ever be edited or changed. Even if you found a hack around it, it's bad practice. Some SQL databases have workarounds that allow it.

Typically what we do is never to delete out of the table. Instead, have a field, perhaps a text field, maybe a single character, that indicates A for Active, or D for deleted.

Then setup a view (a query) that returns all the fields, but only the active rows. You can then do all your reports, forms, queries, work, on that view.

When you need to insert a deleted row back, then you have a special update that changes the deleted status back to active.

Upvotes: 0

Related Questions