Solved

Getting the Primary Key in an insert sentence from ASP

Posted on 2001-08-13
6
286 Views
Last Modified: 2007-12-19
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
Comment
Question by:girona
6 Comments
 
LVL 8

Expert Comment

by:drittich
ID: 6379602
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
 
LVL 20

Accepted Solution

by:
Silvers5 earned 50 total points
ID: 6379609
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
 
LVL 13

Expert Comment

by:gbanik
ID: 6381451
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:LCP
ID: 6382511
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
 

Author Comment

by:girona
ID: 6383278
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6383942
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now