?
Solved

Getting the Primary Key in an insert sentence from ASP

Posted on 2001-08-13
6
Medium Priority
?
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 150 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

765 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