• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Getting the Primary Key in an insert sentence from ASP

Hi,
   I'd like to know how can I obtain the primary key of a register that I'm inserting. Is there any property in ASP to get the value of a primary key that is autonumeric?
Thanks.
0
girona
Asked:
girona
1 Solution
 
drittichCommented:
What is the db you are using?  As a last resort you can do SELECT Max(YourCol) FROM YourTable, although this is not usually good with multi-user dbs.
0
 
Michel SakrCommented:
How do I get the ID number of a just-inserted record?

SQL Server

 
With SQL Server 2000, there are a couple of new functions that are better than @@IDENTITY. Both of these functions are not global to the connection, which is an important weak point of @@IDENTITY. After doing an insert, you can call:
 
PRINT IDENT_CURRENT('table')
 
 
 
This will give the most recent IDENTITY value for 'table' - regardless of whether you created it or not (this overrides the connection limitation of @@IDENTITY -- which can be useful).
 
Another thing you can do is:
 
PRINT SCOPE_IDENTITY()
 
 
 
This will give the IDENTITY value last created within the current stored procedure, trigger, etc. One of the problems with the global nature of @@IDENTITY is that if you do an INSERT, and that table has a TRIGGER ON INSERT which then, in turn, inserts into another table with an IDENTITY field, @@IDENTITY is populated with the second table's value.
 
If you are not using SQL Server 2000, the best way with SQL Server is to use a single stored procedure that handles both the INSERT and the IDENTITY retrieval using @@IDENTITY. Here is sample code for the stored procedure:
 
CREATE PROCEDURE myProc
    @param1 INT
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO someTable
    (
        intField
    )
    VALUES
    (
        @param1
    )
    SET NOCOUNT OFF
    SELECT NEWID = @@IDENTITY
END
 
 
And you would call this from ASP as follows:
 
<%
    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
%>
 

Access
 
With Access, you should be able to get away with something like this:
 
<%
    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
%>
 
 
Now I say "should" because it is (though very obscurely) possible for two people to "cross" inserts, and receive the wrong ID back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering SQL Server (see FAQ #93). However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement):
 
<%
    fakeValue = 5
    set conn = Server.CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    set rs = Server.CreateObject("ADODB.Recordset")
    rs.open "select * from someTable where 1=0", conn, 1, 3
    rs.close: set rs = nothing
    conn.close: set conn = nothing
    rs.AddNew
    rs("intField") = fakeValue
    rs.update
    response.write "New ID was " & rs("id")
    rs.close: set rs = nothing
    conn.close: set conn = nothing
%>
 

You can also look at Manohar's excellent articles at Active Server Corner:
 
    http://www.kamath.com/tutorials/tut004_autonum.asp 
    http://www.kamath.com/tutorials/tut007_identity.asp 
 
And check out MS' KB article:
 
    http://support.microsoft.com/support/kb/articles/q221/9/31.asp
0
 
gbanikCommented:
This is what I do:

I have created a general purpose SP which excepts a query and returns me the identity value.

-------
CREATE PROCEDURE dbo.SPReturnIdentity @InputString varchar(5000), @Outputval varchar(500) OUTPUT

AS
exec sp_sqlexec @InputString
set @Outputval = CONVERT(varchar,@@Identity)
return
GO
-------

@@Identity directly from code has always been a problem for me. Hence the above code. Just call the above SP and pass your INSERT Query at the InputString and the Output String comes out as the Identity.
(Use command object rather than connection string for execution)

GB.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LCPCommented:
Are you using AddNew in recordset? u can obtain back the identity field immediately in the recordset. But remember u must use OpenKeySet for the recordset.
0
 
gironaAuthor Commented:
I'm ussing MSAccess 2000, but I'd like to find a solution for SQL Server 2000 also.
What I code in the ASP pages is something like that:

Set conexion = Server.CreateObject("ADODB.Connection")

conexion.Open "dsIncidencias"

sqlTrabajo="insert into TRABAJOS (ESTADO, TIEMPO) values ('"&vCEstado&"', "&vCTiempo&")"

Set insertarTrabajo = conexion.Execute(sqlTrabajo)

Is there any way of getting the ID with the insert SQL statement?

Thanks.
0
 
Michel SakrCommented:
in access you'll need to do a VBA for it.. in SQL make a stored procedure as stated.. reread my last comment
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now