Hiya Josh ----

I've had situations where running SELECT @@IDENTITY came back with a null or zero or some other unexpected value. After going nuts on this for a while, I started adding a surrogate key to tables with an identity datatype as the primary key. This surrogate key I populate from the presentation or data services tier then send the INSERT. I can then refresh and SELECT on the surrogate key and then grad the new identity field value. Yes, I know it's back-asswards but it's the only way I've found to work with those damn identity columns in a foolproof manner.


>Well, it's a bit more complicated than that. In SQL Server you can SELECT @@INDENTIY. That returns the last identity value created on that connection. I usually just do that and then REPLACE the PK field in the view with that value (no harm done since that field isn't updatable, anyway). If you just REQUERY() it's going to use the parameters of the view (if any) to limit the records returned. The record you added might not even come back.