Click here to Skip to main content
15,891,033 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am inserting values into table, after inserting i am getting scope identity value also, but at the same time that particular record also deleted from table. what would be the reason?

Which one is best option to get maximum id of table scope identity or get the maximum id in sql server

What I have tried:

In one scenario, when inserting one column value is empty, data is inserted, but its deleted automatically
Posted
Updated 27-Mar-16 21:38pm

Never use maximum value to assume an ID for uniqueness - in a multiuser environment, it will give intermittent problems which are a pain to work out, much less fix. Always use scope identity, but make it part of the INSERT - i.e. write a stored procedure to do the insert, and return either the ID value or an error code. Never try to do it as two commands from your C# code.
The alternative (which I prefer) is not to use IDENTITY fields at all, but to use GUID ID's instead, via a UNIQUEIDENTIFIER column, and assign it from your C# code when you do the insert. That way, you are in total control of the values and don't need to "look up" what value was inserted. I only use IDENTITY fields to provide unique values that I don't care about, and won't cross reference such as log table IDs.

As for your "deleted rows" problem - without the relevant code fragments we can't have any idea! :laugh:
 
Share this answer
 
Comments
Santosh K. Tripathi 28-Mar-16 7:04am    
good answer. as always :)
Check out if there are constraints in the table properties. Suppose you are not allowing blanks in a particular column and in the query you are passing blank as a value, then that won't insert the record.

Refer - SQL SERVER - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record - Journey to SQL Authority with Pinal Dave[^].
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900