Identity column changing value, when posted to database

[PoloOpenAir]

vb.net application:

I'm having trouble with an identity-column changing to a new value, if
an error is raised on insert - ex. violation of unique constraint on
another db-column. An new value is assigned to the ID-column in the
database, but not in the form, when I correct the values violating the
unique constraint. The ID-field in the form doesn't get updated.

An example:
Table:
ID:
BIGINT, Identity:YES, Identity Seed:1, Identity Increment:1
ORDERNO:
BIGINT, Unique constraint

Scenario:
Calling AddNew-method on bindingmanager. The value 500 is assigned to
the ID-field in the form. Enter an Orderno. which will violate the
unique constraint. Trying to insert the record. An unique-constraint
exception is thrown. I corret the Orderno. so it doesn't violate the
constraint anymore. Trying to insert. Insert succeded. The value of
the ID-field is still 500, but in the database the value has changed
to 501.

I have generated the DataAdapter with the wizard - and the
refresh-option is enabled. But the value doesn't get updated.

Has anyone experinced the same?

 

[Mary Chipman]

Identity column values are not set until the record is committed.
Therefore, anything assumed about the final Identity column value on
the client before the INSERT statement occurs will likely be
erroneous. for more information, see the @@IDENTITY topic in SQL
BooksOnline.

--Mary

 

[jlapenta]

We are in the process of creating an Windows Forms application which
contains more than 100 tables. Many of these require “maintenance routines”
to update the information in the tables. I would think that many
applications require similar capability. I've worked my way through a
number of books and documents (my favorite is Microsoft ADO.NET by David
Sceppa) and have built a number of routines. We identified some basic
capability we'd like to provide:

· Basic navigation through the DataSet (First, Last, Next, Last, Find).

· Delete, Add.

· We want to do the field editing on a Form (as opposed to in a grid)

· The set of records from the Database to be edited needs to be controlled
by a Filter.

· Since some tables have many fields, multiple pages may be required.

· In many cases, fields in the primary table will have a relationship
defined with another table. Simple lookups of the values from related table
will be provided.


All pretty basic. We've got a number of these routines built. However,
I'm wondering if there are tools available anywhere to either help generate
the code or (even better) to help define some base classes which support
this type of routine.

The latest issue is what to do when ADDING a new record. For example, if
the primary table is EMPLOYEE, when Add is selected, we clear the fields.
We'd like focus to automatically go to the primary key. Currently, we
have the routine trapping the entry of an existing record when the record is
added to the DataSet. However, it would be better if this checked occur
immediately. Of course, for some Tables, the primary key may consist of
several fields, and so the logic for checking existence of record is a bit
more complicated.

An suggestions on where to find assistance with creating this type of
routines would be appreciated.

Thanks.