Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: help with insert statement

    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")
    %>

  2. #2
    Join Date
    May 2004
    Posts
    125
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •