Update() not keeping AutoNumber values

  • Thread starter Thread starter headware
  • Start date Start date
H

headware

I'm writing code to copy the rows of a certain table in one database
to a table of the same structure in another database. I loop through
the rows in the source table and add them to a DataTable object
representing the destination table. Then I use the OleDbCommandBuilder
and the OleDbDataAdapter.Update() method to write the changes to the
destination database.

The problem is that the primary key of the table is an AutoNumber
field and the values in the source table aren't being copied over into
the destination table. ADO.NET seems to allow Access to specify it's
own values instead of using the currently existing ones. I can get it
to work using an INSERT statement and the OleDbCommand class, but I'd
rather use the Update() method for various reasons. How can I tell
ADO.NET to use the AutoNumber values in the source table?

Thanks,
Dave
 
Hi,

You should modify adapter.InsertCommand not to ignore autonumber field (it
simply ignores it for insert).
Also, you might want to avoid using commandbuilder and build your adapters
at design time. In this way you'll have the control over code and you'll
also gain some speed.
 
Thanks for replying, but how can I tell the InsertCommand to ignore
the AutoNumber field?

Miha Markic said:
Hi,

You should modify adapter.InsertCommand not to ignore autonumber field (it
simply ignores it for insert).
Also, you might want to avoid using commandbuilder and build your adapters
at design time. In this way you'll have the control over code and you'll
also gain some speed.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

headware said:
I'm writing code to copy the rows of a certain table in one database
to a table of the same structure in another database. I loop through
the rows in the source table and add them to a DataTable object
representing the destination table. Then I use the OleDbCommandBuilder
and the OleDbDataAdapter.Update() method to write the changes to the
destination database.

The problem is that the primary key of the table is an AutoNumber
field and the values in the source table aren't being copied over into
the destination table. ADO.NET seems to allow Access to specify it's
own values instead of using the currently existing ones. I can get it
to work using an INSERT statement and the OleDbCommand class, but I'd
rather use the Update() method for various reasons. How can I tell
ADO.NET to use the AutoNumber values in the source table?

Thanks,
Dave
Click to expand...
Click to expand...
 
Hi,

Modify the sql insert statement - remove autonumber field and remove
adjacent parameter.
If you have:
insert into mytable (autonumbercol, someothercol, ...)
values(@autonumbercol, @someothercol, ..)
remove the autonumbercorl
insert into mytable (someothercol, ...) values(@someothercol, ..)
I suggest you to create commands at design time and not use commandbuilder.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

headware said:
Thanks for replying, but how can I tell the InsertCommand to ignore
the AutoNumber field?

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi,

You should modify adapter.InsertCommand not to ignore autonumber field (it
simply ignores it for insert).
Also, you might want to avoid using commandbuilder and build your adapters
at design time. In this way you'll have the control over code and you'll
also gain some speed.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

headware said:
I'm writing code to copy the rows of a certain table in one database
to a table of the same structure in another database. I loop through
the rows in the source table and add them to a DataTable object
representing the destination table. Then I use the OleDbCommandBuilder
and the OleDbDataAdapter.Update() method to write the changes to the
destination database.

The problem is that the primary key of the table is an AutoNumber
field and the values in the source table aren't being copied over into
the destination table. ADO.NET seems to allow Access to specify it's
own values instead of using the currently existing ones. I can get it
to work using an INSERT statement and the OleDbCommand class, but I'd
rather use the Update() method for various reasons. How can I tell
ADO.NET to use the AutoNumber values in the source table?

Thanks,
Dave
Click to expand...
Click to expand...
Click to expand...
 
Back
Top