how to update primary key?

Question

hi all
how can i update the primary key field in access database using ado.net
note: i am using dataset (disconnected level)
thanks for your help in advance

Adam Erstelle · Answer

I do not believe you can update an auto number field in Access. If this number is to change, the field will have to be changed to Number, and you will have to track the next number.

asic · Answer

hi
thanks too much for your help and i will try that
my primary key is an auto number
my program has problems because of dealing with this autonumber(insert ,delete)
thanks too much for your help

p.jones · Answer

When you populate your dataset datatable use dataAdapter.FillSchema first to configure the datatable and then dataAdapter.Fill to execute select SQL to populate the datatable. Either your primary key is autonumber or it is set manually, fillschema allows dataset to recognise this.
If its a manual key populate it as you would any other column, if autonumber when create a new datarow the key value will be generated for you.
Your problem comes when you connect back to the database and use dataAdapter.Update to process row changes (add, modify, delete) against the RDBMS. Because primary key values need to be unique. The help system tells you that for autonumber the RDBMS will on INSERT sql execution for the DataRow ignore the value set by the dataset and produce its own - this ensures uniqueness in the database even when there are several clients banging away making insertions. You must then set up a method to obtain that unique key from the RDBMS and insert its value back into the DataRow. See Visual Studio -> Help -> Search -> autonumber for the code solution. I have implemented it for Jet 4.0 Access 2000 and it works fine.
For manually populated key you will have to find your own solution, probably asociated with a single column single row table with a value that gets retrieved and then incremented by each client to ensure uniqueness, and that would have to be under transactional control.
To check whether you have an autonumber primary key, after fillschema search dataTable.PrimaryKey, which is a collection of primary key columns, and look for a column with Autoincrement = true - there should only be one such per table.