SQL - 使用 Scope_Identity() 插入 - 获取记录 ID [英] SQL - INSERT with Scope_Identity() - getting the record id
问题描述
我有一个用 VB.NET 编写的 ASP.NET 页面,它通过使用带有 INNER JOIN
SELECT 语句将项目放入 GridView
> 并且还允许您向发票添加项目.
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
从 items
和 project_items
获取数据.
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")
(有一个外键project_items.item_id -> items.item_id
.)
我尝试在 VB.NET 中使用 SQL 语句尝试同时插入两个表.我尝试的是尝试使用该数据获取创建的最后一条记录的 item_id
并插入到另一个表 (project_items
) 中.但是,数据仅被输入到第一个表中.
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.
知道我能做什么吗?
这是代码:
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 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
推荐答案
首先为什么要在多个语句中执行此操作?为什么不:
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);
现在您只需调用一个 ExecuteNonQuery()
并且您的应用程序不必关心实际生成的 SCOPE_IDENTITY()
值.(当然,您仍然可以检索 SCOPE_IDENTITY()
,当然,使用 ExecuteScalar
- 但正如 Nenad 正确指出的那样,选择一个而不是同时调用两个.)
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.)
既然我们现在知道这里有一个显式的外键,即使我们不能使用 OUTPUT
子句,我们仍然可以将您的 C# 代码减少到一次调用.
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.
这篇关于SQL - 使用 Scope_Identity() 插入 - 获取记录 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!