Insert | resolve | data | How does the database get the ID number of the record you just inserted into the database?
1.SQL Server
For SQL Server 2000来, it provides two new functions (ident_current,scope_identity) and improves the @ @IDENTITY. When you insert a new record, you can call the function:
PRINT ident_current (' table ') ' This will get the new identity value, regardless of whether there is a record added to the database (this avoids the @ @IDENTITY connection limit)
Or: PRINT scope_identity () ' This will get the IDENTITY value of the latest record created by other programs, such as the current stored procedure, trigger, and so on.
The global variable @ @IDENTITY has a problem when an insert is performed on a table, if the table has a trigger program executing the insert operation, and then inserting a record in another table, this returns the @ @IDENTITY value is the identity value of the second table.
If you're not using SQL Server 2000, you'd better have 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 the ASP you can do this:
<%
Fakevalue = 5
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "<conn string>"
Set rs = Conn.execute ("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 methods:
<%
Fakevalue = 5
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "<conn string>"
Conn.execute "Insert into sometable (Intfield) VALUES (" & Fakevalue & ")"
Set rs = Conn.execute ("Select MAX (ID) from SomeTable")
Response.Write "New ID was" & RS (0)
Rs.close:set rs = Nothing
Conn.close:set conn = Nothing
%>
However, for multiple people to add data to the database at the same time, we need to use the recordset's adOpenKeyset cursor to prevent errors. For example, the following example:
<%
Fakevalue = 5
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "<conn string>"
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Rs.Open "SELECT [Intfield] from sometable where 1=0", Conn, 1, 3
Rs. AddNew
RS ("Intfield") = Fakevalue
Rs.update
Response.Write "New ID was" & RS ("id")
Rs.close:set rs = Nothing
Conn.close:set conn = Nothing
%>
