Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BobLewiston
Starting Member
29 Posts |
 Posted - 2009-03-27 : 19:11:28
|
| 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-bitSQL Server 2008 Express with Advanced ServicesSQL2008 AdventureWorksSQL Server 2008 Management Studio ExpressVisual C# 2008 Express |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 19:47:04
|
| Check Books Online for the SCOPE_IDENTITY() function, it can return the identity value of the last inserted row.It's also possible to use a stored procedure to accept the values you want to insert, and use an INSERT statement with an OUTPUT clause. You would then refresh your DataSet or DataReader with the output of the stored procedure. The advantage of this is it can work with any number of rows.Will need more detail on exactly what you're trying to do to offer any additional advice. |
 |
|
|
BobLewiston
Starting Member
29 Posts |
Posted - 2009-03-27 : 22:51:19
|
| robvolk:I'm just a newbie following an online tutorial, trying to insert a new record in an SQL database table. All I wanted to do was to obtain the newly-incremented value of an identity column and display it in a WinForm. The purpose of this WinForm is to enter data for new records which would be added to the database. The main point is: I wanted to display this value BEFORE the new record being entered would actually be added to the database. But now, because it has been pointed out to me that multiple people would be inserting records into the database at the same time, this will not be feasible. Therefore, unless someone surprises me with some new information, the identity column value should not be displayed in the data-entry WinForm for new records that have not yet been added to the database. Do you agree? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 23:24:43
|
| That's correct. My point is that you CAN display it immediately after it is inserted into the database. This is not impacted by multiple users either, they can all insert at the same time without interfering with each other. But if you absolutely must see this value before insert (and I really don't see why you would), I'm afraid you're out of luck, identity won't serve this purpose. |
|
|
|
|
|
|
|
|
displaying an auto-incremented SQL identity column