vb.net SQL - 使用 Scope_Identity() 插入 - 获取记录 ID

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

提问by Brian

I have an ASP.NET page written in VB.NET that gets the items into a GridViewby using a SELECTstatement with INNER JOINand also allows you to add an item to the invoice.

我有一个用 VB.NET 编写的 ASP.NET 页面，它GridView通过使用SELECTwith 语句将项目放入 aINNER JOIN并允许您将项目添加到发票中。

INNER JOINthat gets data from itemsand project_items.

INNER JOIN从items和获取数据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 isSession("ProjectID")

@parameter isSession("ProjectID")

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

（有一个外键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_idof 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.

我试图在 VB.NET 中使用 SQL 语句尝试同时插入两个表。我尝试的是尝试使用该数据获取item_id创建的最后一条记录并插入到另一个表 ( project_items) 中。但是，数据仅被输入到第一个表中。

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-PC\SQLEXPRESS;Initial Catalog=master_db;Integrated Security=True")
        Dim addItemComm As String = "SELECT item_id FROM project_items WHERE project_id=@ProjectID"
        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

回答by Aaron Bertrand

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.)

现在您只需要调用一个ExecuteNonQuery()，您的应用程序就不必关心实际SCOPE_IDENTITY()生成的值。（SCOPE_IDENTITY()当然，如果您愿意，您仍然可以检索ExecuteScalar- 但正如 Nenad 正确指出的那样，选择一个而不是同时调用两个。）

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 OUTPUTclause.

由于我们现在知道这里有一个显式外键，即使我们不能使用该OUTPUT子句，我们仍然可以将您的 C# 代码减少到一次调用。

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.

将它放入存储过程会更干净。

回答by Nenad Zivkovic

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.

相邻的这两行将执行命令两次。您应该删除第二个 - ExecuteNonQuery。这会将您的数据插入两次Items- 两个相同的行，但具有不同的 ID。

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.

由于您仅从第一行中检索 ItemID，因此应该将其插入到 中project_items，但最后插入到 items 中的另一个将没有匹配的行。

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.

此外 - 从按钮单击方法开始的完整部分Dim AddNewItemComm As String- 您打开和关闭 DataReader 并且对它不执行任何操作似乎完全没有必要。