access autonincrement question
-
I have an MS Access table that I'm accessing from C# (via MSDE 2000). The primary key in the table is just an int column with identity set to "yes" and step set to '1'. So whenever I add a record Access automatically gives it a new primary key. Yay. That works fine, but what I'd really like is to be able to retrieve the key that Access generates at the time that I insert the new record. So right now my C# program just figures out the values for the non-primary keys and then sends an INSERT INTO command. Is there anyway to automatically retrieve the new ID that Access generates? The only idea that I have so far is to create an additional column in the table called "isNew" or something. When the user submits the new record, the SQL statement could set the "isNew" column for that record to the user name. Then I could immediately send a SELECT to retrieve the ID WHERE isNew = 'user name'. Of course, then I'd also have to send yet another UPDATE to SET isNew = ''. So it's kind of a cumbersome solution, but at least it will work. Anyone have anything better? The ends can never justify the means. It is the means that determine the ends.
-
I have an MS Access table that I'm accessing from C# (via MSDE 2000). The primary key in the table is just an int column with identity set to "yes" and step set to '1'. So whenever I add a record Access automatically gives it a new primary key. Yay. That works fine, but what I'd really like is to be able to retrieve the key that Access generates at the time that I insert the new record. So right now my C# program just figures out the values for the non-primary keys and then sends an INSERT INTO command. Is there anyway to automatically retrieve the new ID that Access generates? The only idea that I have so far is to create an additional column in the table called "isNew" or something. When the user submits the new record, the SQL statement could set the "isNew" column for that record to the user name. Then I could immediately send a SELECT to retrieve the ID WHERE isNew = 'user name'. Of course, then I'd also have to send yet another UPDATE to SET isNew = ''. So it's kind of a cumbersome solution, but at least it will work. Anyone have anything better? The ends can never justify the means. It is the means that determine the ends.
-
Get the most recently created id by running the query "select @@identidy" with the same database connection. --- b { font-weight: normal; }
Thanks! I think that will work well. My only concern is that if two users simultaneously add records to the table, there could be trouble. But if I run the select query within a line or two of running the insert into query the chances of this happening are very low, right? I would still like to have a full-proof method for future reference though. While I will only have a handful of users on this database at the same time, the program might conceivably need to be scaled up to over a 1,000 users. Any idea what might work in that case? The ends can never justify the means. It is the means that determine the ends.
-
Thanks! I think that will work well. My only concern is that if two users simultaneously add records to the table, there could be trouble. But if I run the select query within a line or two of running the insert into query the chances of this happening are very low, right? I would still like to have a full-proof method for future reference though. While I will only have a handful of users on this database at the same time, the program might conceivably need to be scaled up to over a 1,000 users. Any idea what might work in that case? The ends can never justify the means. It is the means that determine the ends.
Using identity column is not a good idea. There are many problems associated one being identity gap. I think you should implement your own increment procedure. Have a table with the follwoing schema CREATE TABLE dbo.Sequence ( TableName varchar(30) NOT NULL, NextVal int NOT NULL ) LOCK DATAROWS go GetSequence should get you the id CREATE PROCEDURE GetSequence ( @tbl varchar(30)) AS DECLARE @nextval int BEGIN BEGIN TRAN UPDATE Sequence SET NextVal = NextVal + 1 WHERE TableName = @tbl IF @@rowcount = 1 SELECT @nextval = NextVal FROM Sequence WHERE TableName = @tbl COMMIT TRAN SELECT @nextval END This will work even if there are 100 concurrent users
-
Thanks! I think that will work well. My only concern is that if two users simultaneously add records to the table, there could be trouble. But if I run the select query within a line or two of running the insert into query the chances of this happening are very low, right? I would still like to have a full-proof method for future reference though. While I will only have a handful of users on this database at the same time, the program might conceivably need to be scaled up to over a 1,000 users. Any idea what might work in that case? The ends can never justify the means. It is the means that determine the ends.
The @@identity method is absolutely safe. The value returned is the identity of the record last created within the same database session, e.g. with the same database connection. If two users create a record each at exactly the same time, they still have separate database sessions, so the id returned to each user is the id of the record that that specific user created. --- b { font-weight: normal; }