I have the following two insert commands and I'm trying to figure out how I can take the primary key value that is created when the first insert occurs for "cmdAllocHdrInsert" and insert that value into as a foreign key value when the second insert occurs for "cmdAlloDetlsInsert"

Any help is appreciated. Thanks,
-Dman100-

<%
if(Session("ddo") <> "") then cmdAllocHdrInsert__varddo = Session("ddo")
if(Session("reqname") <> "") then cmdAllocHdrInsert__varrequestor = Session("reqname")
if(Session("date") <> "") then cmdAllocHdrInsert__vardt = Session("date")
if(Session("AlloABR") <> "") then cmdAllocHdrInsert__vartype = Session("AlloABR")
if(Session("Allosubject") <> "") then cmdAllocHdrInsert__varsubject = Session("Allosubject")
if(Session("Allodescription") <> "") then cmdAllocHdrInsert__vardescription = Session("Allodescription")
if(Session("Alloreview") <> "") then cmdAllocHdrInsert__varreview = Session("Alloreview")
%>
<%
if(Session("yrA" & x) <> "") then cmdAlloDetlsInsert__varyr = Session("yrA" & x)
if(Session("strategyA" & x) <> "") then cmdAlloDetlsInsert__varstrategy = Session("strategyA" & x)
if(Session("BAA" & x) <> "") then cmdAlloDetlsInsert__varBA = Session("BAA" & x)
if(Session("BOA" & x) <> "") then cmdAlloDetlsInsert__varBO = Session("BOA" & x)
if(Session("origBAA" & x) <> "") then cmdAlloDetlsInsert__varorigBA = Session("origBAA" & x)
if(Session("IncrDecrA" & x) <> "") then cmdAlloDetlsInsert__varIncrDecr = Session("IncrDecrA" & x)
if(Session("origAllocation") <> "") then cmdAlloDetlsInsert__varorigallo = Session("origAllocation")
if(Session("reqAllocation") <> "") then cmdAlloDetlsInsert__varreqallo = Session("reqAllocation")
%>
<%
set cmdAllocHdrInsert = Server.CreateObject("ADODB.Command")
cmdAllocHdrInsert.ActiveConnection = MM_DBConn_STRING
cmdAllocHdrInsert.CommandText = "INSERT INTO dbo.DIM_ABR_MASTER_HDR (ABR_ddo, ABR_requestor, ABR_dt, ABR_type, ABR_subject, ABR_description, ABR_review) VALUES ('" + Replace(cmdAllocHdrInsert__varddo, "'", "''") + "','" + Replace(cmdAllocHdrInsert__varrequestor, "'", "''") + "','" + Replace(cmdAllocHdrInsert__vardt, "'", "''") + "','" + Replace(cmdAllocHdrInsert__vartype, "'", "''") + "','" + Replace(cmdAllocHdrInsert__varsubject, "'", "''") + "','" + Replace(cmdAllocHdrInsert__vardescription, "'", "''") + "','" + Replace(cmdAllocHdrInsert__varreview, "'", "''") + "') "
cmdAllocHdrInsert.CommandType = 1
cmdAllocHdrInsert.CommandTimeout = 0
cmdAllocHdrInsert.Prepared = true
cmdAllocHdrInsert.Execute()
%>
<%
set cmdAlloDetlsInsert = Server.CreateObject("ADODB.Command")
cmdAlloDetlsInsert.ActiveConnection = MM_DBConn_STRING
cmdAlloDetlsInsert.CommandText = "INSERT INTO dbo.DIM_ABR_TRNSACT_DETLS (ABR_TRNSACT_APPROP_YR, ABR_TRNSACT_STRATEGY, ABR_BUDG_ACCT, ABR_BUDG_OBJ, ABR_ORIG_BUDG_AMT, ABR_INCR_DECR, ABR_ORIG_ALLO, ABR_REQ_ALLO) VALUES ('" + Replace(cmdAlloDetlsInsert__varyr, "'", "''") + "','" + Replace(cmdAlloDetlsInsert__varstrategy, "'", "''") + "','" + Replace(cmdAlloDetlsInsert__varBA, "'", "''") + "','" + Replace(cmdAlloDetlsInsert__varBO, "'", "''") + "',convert(money,'" + Replace(cmdAlloDetlsInsert__varorigBA, "'", "''") + "'),convert(money,'" + Replace(cmdAlloDetlsInsert__varIncrDecr, "'", "''") + "'),convert(money,'" + Replace(cmdAlloDetlsInsert__varorigallo, "'", "''") + "'),convert(money,'" + Replace(cmdAlloDetlsInsert__varreqallo, "'", "''") + "')) "
cmdAlloDetlsInsert.CommandType = 1
cmdAlloDetlsInsert.CommandTimeout = 0
cmdAlloDetlsInsert.Prepared = true
cmdAlloDetlsInsert.Execute()
%>
<%
Response.Redirect("confirmation.asp")
%>

Your best bet is to use a stored proc for the two inserts. Call the stored proc, dumping all the values as parameters, and getting the primary key from the first insert like below(assuming the primary is the identity of the first table and you are running SQL Server):


create procedure spInsertData

--put your parameters here

as
declare @Pkey int

insert into table
(column names)
values (values)

--next line gets the primary
set @Pkey = @@Identity

insert into table2
(columns)
values (values with the new primary key)




Need any help on the stored procedure, how to call it in code, or you are not using SQL let me know.

DMW