Indeed, using a stored procedure would by far and away be the best solution. after an insert, sql server special variable @@IDENTITY contains the new pk of what you just inserted. so this will probably work:-
sqlInsert = "INSERT into OnlineAccess(userID,lastName,firstName,...) values('"&userID&"','"&lastName&"','"&firstName&"',...) ; SELECT @@IDENTITY AS [newkeyname]"
RS = MyConn.Execute(sqlInsert)
requestID = RS("newkeyname")
But really thats a bag of sh!t of an implementation. Stored procedures are the way to go. Why? 1. stored procedures run much quicker, because they are precompiled. 2. They are like functions in proper languages; you can change the internal code without changing the interface. You could most probably do the whole job in one SP, like this:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [p_YourProc]
@UserId int,
@Firstname varchar(100),
@Lastname varchar(100),
@NewPKout integer output
AS
-- define a sql variable
declare @NewPK int;
-- do the main insert first.
INSERT into OnlineAccess(userID,lastName,firstName) values
( @UserId, @LastName, @Firstname);
-- retrieve the new key
select @NewPK=@@IDENTITY;
-- do the other inserts
INSERT into DateTime (requestID,status)
values(@NewPK,'OPEN')
-- might as well send the pk back to asp
set @NewPKout = @NewPK
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
There. Bob's your uncle. Signature space for rent. Apply by email to....
