IDENTITY INSERT with Access and ADO.NET

[Sanj]

I'm using ADO.NET (using a dataadapter update) to update
data in an Access data table. One of the columns is an
Autonumber column, but I want this column to contain the
values I sepcifically want inserted, not the autonumbers
generated. I know you can achieve this in SQL Server by
using the IDENTITY INSERT feature. Does anyone know how I
can do this with Access?

 

[Michael Lang]

I'm using ADO.NET (using a dataadapter update) to update
data in an Access data table. One of the columns is an
Autonumber column, but I want this column to contain the
values I sepcifically want inserted, not the autonumbers
generated. I know you can achieve this in SQL Server by
using the IDENTITY INSERT feature. Does anyone know how I
can do this with Access?

Why don't you turn autonumber off? A primary key does have to be unique,
but does not have to be designated as "autonumber".

Michael Lang, MCSD

 

[shootsie]

Did you ever figure out how to do this?

 

[Sylvain Lafontaine]

What is your exact problem? Retrieving the value of @identity avec an
insert or something else?

S. L.

 

[shootsie]

I want to set Identity Insert on, insert a value into a database
including the primary key by updating it with a recordset, and keep the
primary keys in tact. Currently the code I have to do this works
fine, except the primary keys are being generated by the database
server. Here is the code:

' Set AcceptChangesDuringFill = False
objDataAdapterSource.AcceptChangesDuringFill = False
' Fill the data adapter with some data from the source "myTable"
objDataAdapterSource.Fill(objDataSetSource, "myTable")
' This is a function I wrote that just calls the "SET
' IDENTITY_INSERT myTableON"
SetIdentityInsert(True, "myTable")
' Select everything from myTable on the database toupdate and
' create a data adapter
strSQL = "SELECT * FROM myTable"
objDataAdapterDest= New SqlClient.SqlDataAdapter(strSQL, strConn)
' create a commandbuilder object
objCB = New SqlClient.SqlCommandBuilder(objDataAdapterDest)'
'Update the destination data set
objDataAdapterDest.Update(objDataSetSource, "myTable")


Let's say the Source object objDataSetSource had a "myTable" with two
columns" "TableID" and "Description" where "TableID" was the key.
Let's say it had one row, where the TableID = 1 and Description =
"TEST". I am expecting the same table in the destination database to
be inserted with the exact same data. Instead, the TableID ends up
being generated by the database as if Identity_Insert isn't turned on.
Any suggestions?

Thanks in advance!

 

[David Sceppa]

When the CommandBuilder determines that a column is an auto-increment
column, it omits that column from the InsertCommand logic. There is no
switch to tell the CommandBuilder to send the client-generated value for
the column to the database.

Your best bet would be to use CommandBuilder or VS.NET DataAdapter
Configuration Wizard logic as a starting point, and modify that logic to
push the client-generated value(s) into the database.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.