SQL - 与SCOPE_IDENTITY INSERT() - 获取记录id [英] SQL - INSERT with Scope_Identity() - getting the record id
问题描述
我已经写在VB.NET ASP.NET页面,获取项目成 GridView控件
使用 SELECT
与 INNER JOIN
,也可以让你的项目添加到发票。
INNER JOIN
从项目中获取数据
和 project_items
。
SELECT Items.item_id,Items.item_name,Items.item_cost,project_items.item_quantity
从装备
INNER JOIN project_items
ON items.item_id = project_items.item_id
WHERE project_items.project_id = @parameter
@parameter是
会议(专案编号)
(有一个外键 project_items.item_id - 方式> items.item_id
)
我有一个试图用SQL语句在VB.NET试图同时INSERT操作两个表。我试过是我试图让创造的最后一个记录的 ITEM_ID
,并插入到另一个表( project_items
)使用该数据。然而,数据只被输入到该第一表
任何想法,我能做些什么?
这是code:
保护小组btnAddItem_Click(发送者为对象,E作为EventArgs的)把手btnAddItem.Click 昏暗康恩作为新的SqlConnection(数据源= BRIAN-PC \\ SQLEX $ P $干燥综合征;初始目录= master_db;集成安全性=真)
昏暗addItemComm作为字符串=SELECT ITEM_ID FROM project_items WHERE PROJECT_ID = @专案编号
昏暗user_id_select作为新的整数 昏暗addItemSQL作为新的SqlCommand conn.Open() addItemSQL =新的SqlCommand(addItemComm,康涅狄格州)
addItemSQL.Parameters.AddWithValue(@专案编号,Convert.ToInt32(会话(专案编号)))
昏暗的DataReader作为SqlDataReader的= addItemSQL.ExecuteReader()
datareader.Close()
conn.Close() 昏暗AddNewItemComm的String =INSERT INTO项目(ITEM_NAME,ITEM_COST,ITEM_ code)VALUES(@ItemName,@ItemCost,@项目code); SELECT SCOPE_IDENTITY()
昏暗AddNewItem2Comm的String =INSERT INTO project_items(ITEM_ID,PROJECT_ID,item_quantity)VALUES(@ItemID,@ProjectID,@ItemQuantity)
昏暗AddNewItemSQL作为新的SqlCommand
conn.Open() AddNewItemSQL =新的SqlCommand(AddNewItemComm,康涅狄格州)
AddNewItemSQL.Parameters.AddWithValue(@ ITEMNAME,txtItemName.Text.Trim)
AddNewItemSQL.Parameters.AddWithValue(@ ItemCost,Convert.ToInt32(txtItemCost.Text))
AddNewItemSQL.Parameters.AddWithValue(@项目code,txtItemCost.Text.ToString.ToUpper) 昏暗的itemid作为整数 ITEMID = AddNewItemSQL.ExecuteScalar() AddNewItemSQL.ExecuteNonQuery() conn.Close() conn.Open() AddNewItemSQL =新的SqlCommand(AddNewItem2Comm,康涅狄格州) AddNewItemSQL.Parameters.AddWithValue(@项ID,ITEMID)
AddNewItemSQL.Parameters.AddWithValue(@专案编号,Convert.ToInt32(会话(专案编号)))
AddNewItemSQL.Parameters.AddWithValue(@ ItemQuantity,Convert.ToInt32(txtItemQuantity.Text)) AddNewItemSQL.ExecuteNonQuery() conn.Close() 结束小组
你为什么要在多条语句首先这样做呢?为什么不:
INSERT dbo.Items(ITEM_NAME,ITEM_COST,ITEM_ code)
输出inserted.ItemID,@ProjectID,@ItemQuantity
INTO dbo.project_items(ITEM_ID,PROJECT_ID,item_quantity)
VALUES(@ItemName,@ItemCost,@项目code);
现在,你只需要调用有一个的ExecuteNonQuery()
和您的应用程序不必关心实际 SCOPE_IDENTITY()$生成的C $ C>值。 (您仍然可以检索
SCOPE_IDENTITY()
如果你愿意,当然,使用的ExecuteScalar
- 但内纳德正确地指出,挑一个不是调用两个。)
由于我们现在知道,有一个明确的外键在这里,我们还可以降低你的C#code到一个电话,即使我们不能使用输出
条款。
DECLARE @i INT;INSERT dbo.Items(ITEM_NAME,ITEM_COST,ITEM_ code)
SELECT @ItemName,@ItemCost,@项目code;SELECT @i = SCOPE_IDENTITY();INSERT dbo.project_items(ITEM_ID,PROJECT_ID,item_quantity)
SELECT @i,@ProjectID,@ItemQuantitySELECT @i; - 如果有必要
将是更清洁把这个变成一个存储过程。
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-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
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.
这篇关于SQL - 与SCOPE_IDENTITY INSERT() - 获取记录id的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!