Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

ADO .NET not automatically updating ID (autoincrem

ADO .NET maintains and works with a memory copy of the database table.

Let's say that you use an Access database and there is an ID field, set to autoincrement.

You add a record.

Unfortunately, ADO .NET, because of a bad design decision of the ADO .NET designers, writes the record out, but DOES NOT READ THE ID FIELD VALUE into the memory dataset!

So, in the database table you will have one value, and in the memory table you will have another value!

And it doesn't announce you in any way!

Then, when you try to delete or edit the newly added records, very strange problems and exceptions appear.

I have searched the MSDN and the web. There are 3 suggested solutions:

1. Go to the adapter, right click, choose "configure data adapter". Press Next until you reach a step in the wizard where it has an "Advanced" button.

Click that, then check the "refresh the dataset" checkbox.

This doesn't work - no matter what I do, the checkbox is disabled.

2. Add a "SELECT @@IDENTITY" query to the RowUpdating or RowUpdated event. This returns the ID of the table, and you can update the memory dataset by hand.

This doesn't work - the return value is always zero.

I tried both events, different code variations, read the help, etc.

Doesn't work.

3. Every time you add records, FILL the datatable. That is, make ADO .NET load the data from disk into memory.

This solution wastes a lot of time for big tables and creates user disconfort.

Please excuse my broken English, but it's not my native language, and I am very nervous because I have tried to get this working for 5 hours straight.

In Delphi I never had such problems using ADO, BDE, and other database systems.

Tuesday, July 22, 2003

Well, being furious, I got the window handle of the checkbox inside the wizard, and tried to perform an EnableWindow on the wizard in order to enable it.

I have used this before, on the IIS config dialogs, and it worked.

Unfortunately, now it didn't work!

You know that the hardware is the part of the computer that can be kicked. I wish I could kick the ADO .NET, but unfortunately it's software, so...

:-( :-( :-(

I'll keep hacking at it.

It's not for myself, it's for a friend.

I'm a happy programmer, but I use other development tool.

Tuesday, July 22, 2003

This can be done.  It is just a little complex and not well documented (yet).

You need to build your own commands using the sql command builder object and append '; SET @fieldname  = SCOPE_IDENTITY()' to the insert command.  Once you have the commands built you just them to your data adapter.

One note, if you write a general purpose routine that automaticlly identifies the auto increment field and updates the data adapter, the data adapter object needs the following parameter set:

_dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey


Eric Budd

Eric Budd
Tuesday, July 22, 2003

1 . SCOPE_IDENTITY is a SQLServer2000 function not available in Access

2. For Access db's use the Jet 4.0 OLEDB Provider and not the ODBC ones .. then you;ll get your ID's behave the way you want them ..

erhan hosca
Friday, August 1, 2003

*  Recent Topics

*  Fog Creek Home