Comprehensive Solution-how to obtain the ID number of the records just inserted into the database?

How do I obtain the ID number of the record just inserted into the database?
1. SQL Server
For SQL Server 2000, it provides two brand new functions (IDENT_CURRENT, SCOPE_IDENTITY) and improves the @ IDENTITY deficiency. When you insert a new record, you can call the function:
PRINT IDENT_CURRENT ('table') ', which will obtain a new IDENTITY value, regardless of whether there are records added in the database (this avoids the connection limit of @ IDENTITY)
Or: PRINT SCOPE_IDENTITY () ', which will obtain the IDENTITY value of the latest records created in the current stored procedure, trigger, and other programs.
The global variable @ IDENTITY has a problem. When an insert operation is performed on a table, if a trigger program is executing the insert operation on the table, insert the record to another table, in this way, the @ IDENTITY value is the IDENTITY value of the second table.
If you are not using SQL Server 2000, you can use a simple stored procedure to solve this problem.
Create procedure myProc
@ Param1 INT
AS
BEGIN
SET NOCOUNT ON
Insert into someTable
(
IntField
)
VALUES
(
@ Param1
)
SET NOCOUNT OFF
Select newid = @ IDENTITY
END
In ASP, you can do this:
<%
FakeValue = 5
Set conn = Server. CreateObject ("ADODB. Connection ")
Conn. open "<conn string>"
Set rs = conn.exe cute ("exec myProc @ param1 =" & fakeValue)
Response. write "New ID was" & rs (0)
Rs. close: set rs = nothing
Conn. close: set conn = nothing
%>
2. Access
For Access, you can use the following method:
<%
FakeValue = 5
Set conn = Server. CreateObject ("ADODB. Connection ")
Conn. open "<conn string>"
Conn.exe cute "Insert into someTable (intField) values (" & fakeValue &")"
Set rs = conn.exe cute ("select MAX (ID) from someTable ")
Response. write "New ID was" & rs (0)
Rs. close: set rs = nothing
Conn. close: set conn = nothing
%>
However, when multiple users add data to the database at the same time, we need to use the adOpenKeyset cursor of the record set to prevent errors. For example: