asked on

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.

drittich

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.

ASKER CERTIFIED SOLUTION

Michel Sakr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbanik

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.

LCP

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.

girona

ASKER

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.

Michel Sakr

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