Solved

Getting the Primary Key in an insert sentence from ASP

Posted on 2001-08-13
6
292 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 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
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!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP Button to clear text 4 69
Obtain data from database .mdb 4 30
VBScript on Html 15 58
JQuery DataTable Functionality 8 20
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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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