Inserting into multiple tables using more than one ID that just got created

I had to add another table into my database and now I need to go back and update a page that allows inserts into more than one table. I didn't write this page, so I'm trying to clean everything up, but there are some parts that I don't really understand. Now I have broken the page and it only inserts into one table. The very first one that I insert into.

ProductName: goes into the Product table

Description: goes into the Picklist table as Data.....it also is supposed to generate an insert into the marketing table based on the PicklistID which is an identity column. The marketing table tells the Picklist table that it is looking for a description.

Price: goes into Product table

Category: goes into the CategoryLink table which also inserts the recently generated ProductID.

Company: goes into the CompanyLink table which also inserts the recently generated ProductID.

Target audience: goes into the TargetLink table which also inserts the recently generated ProductID.

Status: goes into the Product table

Protected Sub submitButton_Click(ByVal sender As Object, 
ByVal e As System.EventArgs) Handles submitButton.Click
    Dim CategoryID As String = txtCategoryID.Value
    Dim CompanyIDs As New ArrayList
    'Get selected companies-----
    Dim CompanyCount As Integer = CompanyCheckbox.Items.Count
    For i As Integer = 0 To CompanyCount - 1
        If CompanyCheckbox.Items(i).Selected Then
            CompanyIDs.Add(CompanyCheckbox.Items(i).Value)
        End If
    Next
    'Get selected targets---
    Dim TargetIDs As New ArrayList
    Dim TargetCount As Integer = TargetCheckbox.Items.Count
    For i As Integer = 0 To TargetCount - 1
        If TargetCheckbox.Items(i).Selected Then
            TargetIDs.Add(TargetCheckbox.Items(i).Value)
        End If
    Next
    'Get Status---
    Dim Status As String = Nothing
    If StatusCheckbox.Checked Then
        Status = "1"
    Else
        Status = "0"
    End If
    'SQL Insert: Product Table
    Dim sqlInsertProduct As String = "IF NOT EXISTS (SELECT * FROM Product 
    WHERE ProductName= @ProductName) 
    BEGIN INSERT INTO Product 
    (ProductName, Status, CreateDate, ModifyDate, CreateUser, ModifyUser, Price) 
    VALUES (@ProductName, @Status ,getdate(),getdate(), @CreateUser, @ModifyUser,
    @Price) END;
    INSERT INTO Picklist (Title, Data) VALUES ('About this product', @Data);"

    'Create SQL Connection
    Using cn As New 
    SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings
    ("LocalSqlServer").ConnectionString)

        Using cmd As New SqlCommand(sqlInsertProduct, cn)

            cmd.Parameters.Add(New SqlParameter("@ProductName", 
            txtNewProductName.Text))
            cmd.Parameters.Add(New SqlParameter("@Status", StatusCheckbox.Checked))
            cmd.Parameters.Add(New SqlParameter("@Price", txtPrice.Text))
            cmd.Parameters.Add(New SqlParameter("@Data", txtProductDesc.Text))
            cmd.Parameters.Add(New SqlParameter("@CreateUser",
            System.Web.HttpContext.Current.User.Identity.Name))
            cmd.Parameters.Add(New SqlParameter("@ModifyUser",
            System.Web.HttpContext.Current.User.Identity.Name))
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
        'Get the productID of the newly inserted product
        Dim sqlGetID As String = "SELECT @@IDENTITY"
        Dim ProductID As String = Nothing
        Dim cmdGetID As New SqlCommand(sqlGetID, cn)
        Dim myReader As SqlDataReader = cmdGetID.ExecuteReader()
        While myReader.Read
            If IsDBNull(myReader(0)) Then
                ProductID = ""
            Else
                ProductID = myReader(0)
            End If
        End While
  开发者_Go百科      myReader.Close()
        cn.Close()

        'SQL Insert: Marketing Table
        Dim sqlInsertMarketing As String = "INSERT INTO Marketing (ProductID, 
        MarketingTypeID, MarketingTitle, MarketingData) VALUES ('" & ProductID & "', 2,
        'Description', scope_identity())"
        'SQL Insert: Category Table
        If CategoryID <> Nothing Then
            Dim sqlInsertCategory As String = "INSERT INTO CategoryLink (CategoryID,
            ProductID) VALUES (@CategoryID,'" & ProductID & "')"
            Using cmdInsertCategory As New SqlCommand(sqlInsertCategory, cn)
                cmdInsertCategory.Parameters.Add(New SqlParameter("@CategoryID",
                txtCategoryID.Value))
                cn.Open()
                cmdInsertCategory.ExecuteNonQuery()
            End Using
            cn.Close()
        End If
        If CompanyIDs.Count > 0 Then
            For i = 0 To CompanyIDs.Count - 1
                Dim sqlInsertCompany = "INSERT INTO CompanyLink (CompanyID, ProductID)
                VALUES ('" & CompanyIDs(i) & "','" & ProductID & "')"
                Using cmdInsertCompany As New SqlCommand(sqlInsertCompany, cn)
                    cn.Open()
                    cmdInsertCompany.ExecuteNonQuery()
                End Using
                cn.Close()
            Next

        End If
        If TargetIDs.Count > 0 Then
            For i = 0 To TargetIDs.Count - 1
                Dim sqlInsertTarget = "INSERT INTO TargetLink (TargetID, ProductID) 
                VALUES ('" & TargetIDs(i) & "','" & ProductID & "')"
                Using cmdInsertTarget As New SqlCommand(sqlInsertTarget, cn)
                    cn.Open()
                    cmdInsertTarget.ExecuteNonQuery()
                End Using
                cn.Close()
            Next
        End If

    End Using

Response.Write("<script type='text/javascript'>{ alert('Product added successfully'); 
document.location.href = 'AddProduct.aspx'; }</script>")
End Sub
End Class

Like I said before, only the insert that goes into the Product table works. Before I added the Picklist table and tried to reconnect everything, this entire page worked. The code was a lot sloppier and didn't have parameters, so that could also be where I messed up since I am still trying to learn how to use them. If I need to include additional information I can do that. I don't know how detailed to make this post. Thanks

UPDATE:

I have gotten everything to INSERT except for the INSERT to the Marketing table. I would really appreciate someone who can help me get that PicklistID to insert into the MarketingData column of the Marketing table

---

If you only have one identity value to get use SCOPE_IDENTITY(), never use @@IDENTITY. The problem with @@IDENTITY is that it always returns the last identity value, even if it was from another "scope". For example, you insert into your table and an identity value is generated, a trigger then fires that inserts in to a log table with an identity. You then call @@IDENTITY, guess what you get the log table identity value, it was last. SCOPE_IDENTITY() gives you the last within your local scope.

HOWEVER, if you need to capture multiple identity values because you insert multiple rows in one statement, you must use the OUTPUT clause. It works on INSERT, UPDATE and DELETE, but here is an example on an UPDATE: view the changed values after an update statement

---

This is one of the drawbacks of surrogates i.e. harder to perform bulk-creation of entities. I avoid using surrogates myself but often come up against this problem. My general approach is to use a staging table to map system-generated surrogates to their natural keys.

Assuming this somewhat simplified structure:

CREATE TABLE Products 
(
 ProductName VARCHAR(20) NOT NULL UNIQUE,  -- natural key
 ProductID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE  -- surrogate
);

CREATE TABLE PickLists
(
 PicklistDescription VARCHAR(30) NOT NULL UNIQUE,  -- natural key
 PicklistID INTEGER IDENTITY(50, 50) NOT NULL UNIQUE  -- surrogate
);

CREATE TABLE Marketing
(
 ProductID INTEGER NOT NULL UNIQUE REFERENCES Products (ProductID), 
 PicklistID INTEGER NOT NULL UNIQUE REFERENCES PickLists (PicklistID), 
 MarketingComment VARCHAR(40) NOT NULL
);

the following is a basic outline using vanilla Standard SQL-92 (IDENTITY keyword excepted!):

(note: natural keys are those generated outside of the DBMS and surrogates are DBMS-generated values)

CREATE TABLE StagingMarketing
(
 ProductName VARCHAR(20) NOT NULL UNIQUE, 
 PicklistDescription VARCHAR(30) NOT NULL UNIQUE, 
 MarketingComment VARCHAR(40) NOT NULL
);

-- Bulk insert staging table using natural key values
INSERT INTO StagingMarketing (ProductName, PicklistDescription, MarketingComment)
   VALUES ('Widget55', 'Stuff22', 'Prototype');
INSERT INTO StagingMarketing (ProductName, PicklistDescription, MarketingComment)
   VALUES ('Widget99', 'Stuff152', 'Research');

-- Update referenced tables
INSERT INTO Products (ProductName)
   SELECT ProductName FROM StagingMarketing;
INSERT INTO PickLists (PicklistDescription)
   SELECT PicklistDescription FROM StagingMarketing;

-- Finally, update referencing table
INSERT INTO Marketing (ProductID, PicklistID, MarketingComment)
   SELECT P.ProductID, L.PicklistID, S.MarketingComment
     FROM StagingMarketing S
          INNER JOIN Products P
             ON P.ProductName = S.ProductName
          INNER JOIN PickLists L
             ON L.PicklistDescription = S.PicklistDescription;

-- Cleanup
DELETE FROM StagingMarketing;

SELECT * FROM Marketing;

p.s. if the designer chose to use IDENTITY as an artificial key in absence of a natural key then you are up the creek.