ngohuuhung
New Member
The Database forum has several solutions for finding the record ID for the last record added to an SQL or Access database, but they all relate to classic ASP and recordsets.<BR><BR>Has anyone seen a simple ASP.Net approach for determining the auto-incriment number assigned to a record when added to an Access database?<BR><BR>This is a pretty common need, so it's likely someone has already pegged an answer.<BR><BR>Any suggestions would be appreciated.<BR><BR>Thanks,<BR>Tom Ti've been looking for an answer on this as well with little luck. one of the solutions i saw was to add a date field to the table you're adding a record to and when you're inserting the new record grab the system date/time and insert that as well. from here, you can do another select statement selecting the record with the date/time that you just grabbed.<BR><BR>i've also seen examples with the "SELECT @@Identity" statement which returns the id of the last inserted record, but i think this covers the entire database and not just the table you're inserting into.<BR><BR>hopefully there's a more straight forward and reliable way of doing this with .Net and we just haven't found it.I've been able to come up with an approach that works, but I still think there must be a better (cleaner) way to do it.<BR><BR>In the same Sub where I call the Update command to add the new record to the data base, I include a call to the database after the update command. It use the following select command:<BR><BR>Dim objCmd6 As New oleDBCommand _<BR>("SELECT MAX(Ref) FROM Scores where rID = @rID",conn)<BR><BR>The rID field in the database is an ID of the user who entered the record. The Ref field in the database is the auto-incriment field I'm after. Since I'm selecting the MAX ref for the current user (who just added the record) I get the record ID of the record just entered by "This" user.<BR><BR>I then put the ref number in my dataset so it is up to data and concurrent with the database.<BR><BR>It works well, and I avoid the concurrency violations I was getting earlier.<BR><BR>Until I find a better way (since Access doesn't support @@Identity), I will keep using this approach.<BR><BR>Please post any new findings you come across.<BR><BR>Regards, <BR><BR>Tom T<BR>