I am developing an application that utilizes MS SQL Server. All of my tables have a single IDENTITY Field, used as a Primary Key. As I was mulling over some code in my head, I thought about a possible problem that might crop up, and I thought it would be a good idea to ask if anyone can validate if what I am thinking is correct.

Being that ADO.Net is a disconnected data environment, I foresaw a problem with IDENTITY field, and returning the value of it when inserting a new record. I searched the internet and found some samples of how to get around this problem, by appending a SELECT command to the end of the INSERT statement (I got this idea directly from Microsoft.com). I testing the code and it works perfectly, after clearing out some bugs. But here is my concern...If multiple users are appending a large amount of data simultaneously into the same table, would the return of the IDENTITY information/row be always correct? Or would there be the possibility that I may accidentally grab someone else's generated IDENTITY value?

I am using the following INSERT Statement, as instructed by Microsoft.com

     objAdapter.InsertCommand.CommandText = "INSERT INTO tblUsers( Name ) VALUES ( @p1 );" & _
                                            "SELECT * FROM tblUsers WHERE ID = SCOPE_IDENTITY()"