displaying an auto-incremented SQL identity column

[BobLewiston]

Most databases have multiple users. For these databases it is a good idea to auto-increment the identity column.

I understand that an SQL identity column is not incremented until the newly-created record is inserted.

I guess this means the identity column is incremented when you actually reconnect to the database and insert the new record there, rather than just add the record to the locally-resident DataSet, correct?

And I guess this also means that for auto-incremented identity columns it is not possible to accurately display the newly-incremented identity column in the data entry WinForm used to create the new record and add it to the database, right?

For what it’s worth, the environment I’m working in is:

32-bit
SQL Server 2008 Express with Advanced Services
SQL2008 AdventureWorks
SQL Server 2008 Management Studio Express
Visual C# 2008 Express

[Old Pedant]

I guess this means the identity column is incremented when you actually reconnect to the database and insert the new record there, rather than just add the record to the locally-resident DataSet, correct?

And I guess this also means that for auto-incremented identity columns it is not possible to accurately display the newly-incremented identity column in the data entry WinForm used to create the new record and add it to the database, right?

Right on both counts.

FWIW, the solution MS uses in disconnected datasets (in rich client apps, not usually in the web) is to use a GUID for the id instead of an IDENTITY field. GUIDs *can* be created client side and then passed to the server for the actual insert.

Of course, GUIDs are ugly as pig snot in appearance (32 characters long!), so you still wouldn't want to display them to the user.