OK, so how can I check if the insertion is success or not?
should I fetch for that record again immediate after the insert?


"Erland Sommarskog" <esquel@sommarskog.se> ???
news:Xns955F72B0A42D0Yazorman@127.0.0.1 ???...
> salamol (salamol@hotmail.com) writes:
> > The problem I am facing is neither cannot get an identity (identity not
> > null) nor getting a wrong identity. the server returns a correct
> > identity value which should belongs to the record that I just insert,
> > but when I fetch the record by that identity value, the record is
> > missing i.e. the identity for the last record is 100, and I do an
> > insert, the system return 101 as @@identity and 0 as @@Error, however if
> > i select the record from 95 - 105 it will returns records 95, 96, 97,
> > 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> > insert after 101)
>
> If you have received 101 in @@identity for an inserted row, and the row
> is later nowhere to be found, the insertion of the row has been rolled
> back, as I discussed in my other post.
>
> While you may already be aware of this, I like to point out that it lies
> in the concept of the IDENTITY property that you never can trust the
numbers
> to be contiguous. If you attempt to insert a row into a table, and that
> insertion fails for whatever reason, you have still consumed a number.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp