SQL – INSERT with Scope_Identity() – getting the record id

I have an ASP.NET page written in VB.NET that gets the items into a GridView by using a SELECT statement with INNER JOIN and also allows you to add an item to the invoice.

INNER JOIN that gets data from items and project_items.

SELECT Items.item_id, Items.item_name, Items.item_cost, project_items.item_quantity
FROM Items 
INNER JOIN project_items
ON items.item_id = project_items.item_id 
WHERE project_items.project_id = @parameter

@parameter is Session("ProjectID")

(There is a foreign key project_items.item_id -> items.item_id.)

I have an trying to use an SQL statement in VB.NET to try and INSERT into two tables simultaneously. What I tried is I tried to get the item_id of the last record created and insert into another table (project_items) by using that data. However, data is only being entered into the first table.

Any idea what I can do?

This is the code:

Protected Sub btnAddItem_Click(sender As Object, e As EventArgs) Handles btnAddItem.Click

        Dim conn As New SqlConnection("Data Source=BRIAN-PCSQLEXPRESS;Initial Catalog=master_db;Integrated Security=True")
        Dim addItemComm As String = "SELECT item_id FROM project_items WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f8888a97929d9b8ca7919cc5b8a88a97929d9b8cb1bc">[email protected]</a>"
        Dim user_id_select As New Integer

        Dim addItemSQL As New SqlCommand

        conn.Open()

        addItemSQL = New SqlCommand(addItemComm, conn)
        addItemSQL.Parameters.AddWithValue("@ProjectID", Convert.ToInt32(Session("ProjectID")))


        Dim datareader As SqlDataReader = addItemSQL.ExecuteReader()


        datareader.Close()
        conn.Close()

        Dim AddNewItemComm As String = "INSERT INTO Items (item_name,  item_cost, item_code) VALUES (@ItemName, @ItemCost, @ItemCode); SELECT SCOPE_IDENTITY()"
        Dim AddNewItem2Comm As String = "INSERT INTO project_items (item_id, project_id, item_quantity) VALUES (@ItemID, @ProjectID, @ItemQuantity) "
        Dim AddNewItemSQL As New SqlCommand


        conn.Open()

        AddNewItemSQL = New SqlCommand(AddNewItemComm, conn)
        AddNewItemSQL.Parameters.AddWithValue("@ItemName", txtItemName.Text.Trim)
        AddNewItemSQL.Parameters.AddWithValue("@ItemCost", Convert.ToInt32(txtItemCost.Text))
        AddNewItemSQL.Parameters.AddWithValue("@ItemCode", txtItemCost.Text.ToString.ToUpper)

        Dim ItemId As Integer

        ItemId = AddNewItemSQL.ExecuteScalar()

        AddNewItemSQL.ExecuteNonQuery()

        conn.Close()

        conn.Open()

        AddNewItemSQL = New SqlCommand(AddNewItem2Comm, conn)

        AddNewItemSQL.Parameters.AddWithValue("@ItemID", ItemId)
        AddNewItemSQL.Parameters.AddWithValue("@ProjectID", Convert.ToInt32(Session("ProjectID")))
        AddNewItemSQL.Parameters.AddWithValue("@ItemQuantity", Convert.ToInt32(txtItemQuantity.Text))

        AddNewItemSQL.ExecuteNonQuery()

        conn.Close()



    End Sub

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Why are you doing this in multiple statements in the first place? Why not:

INSERT dbo.Items (item_name,  item_cost, item_code) 
  OUTPUT inserted.ItemID, @ProjectID, @ItemQuantity 
  INTO dbo.project_items(item_id, project_id, item_quantity)
VALUES (@ItemName, @ItemCost, @ItemCode);

Now you only have to call one ExecuteNonQuery() and your app doesn’t have to care about the actually SCOPE_IDENTITY() value generated. (You can still retrieve SCOPE_IDENTITY() if you want, of course, using ExecuteScalar – but as Nenad rightly points out, pick one instead of calling both.)

Since we now know that there is an explicit foreign key here, we can still reduce your C# code to one call even if we can’t use the OUTPUT clause.

DECLARE @i INT;

INSERT dbo.Items (item_name,  item_cost, item_code) 
  SELECT @ItemName, @ItemCost, @ItemCode;

SELECT @i = SCOPE_IDENTITY();

INSERT dbo.project_items(item_id, project_id, item_quantity)
  SELECT @i, @ProjectID, @ItemQuantity 

SELECT @i; -- if necessary

Would be even cleaner to put this into a stored procedure.

Method 2

ItemId = AddNewItemSQL.ExecuteScalar()

        AddNewItemSQL.ExecuteNonQuery()

These two rows next to each other will execute the command twice. You should remove the second one – ExecuteNonQuery. This will have your data inserted twice in the Items – two same rows but with different IDs.

Since you only retrieve ItemID from the first row, that one should be inserted in project_items, but the other one that was last inserted in items will have no matching row.

Also – complete section from beginning of button click method up before Dim AddNewItemComm As String – where you open and close DataReader and do nothing with it seems completely unnecessary.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0