从插入的记录的主键检索标识值 [英] Retrieve identity value from inserted record's primary key
问题描述
我不能在我的DataRow检索新的标识值从插入的记录(通过DataAdapter.Update)。
i cannot retrieve the new identity value from an inserted record(via DataAdapter.Update) in my DataRow.
我使用的是强类型数据集作为DAL。我想更新该表joinded与其他表中的SelectCommand,因此,设计人员不能自动生成插入 - /最新情况/删除,命令,也不能刷新数据表自动(S http://msdn.microsoft.com/de-de/library/dex7k4dw %28V = VS.100%29.aspx )。
I'm using a strong typed Dataset as DAL. The table i want to update is joinded with other tables in the SelectCommand, hence the designer cannot automatically generate the insert-/update-/delete-commands and also it cannot "Refresh the DataTable" automatically(s. http://msdn.microsoft.com/de-de/library/dex7k4dw%28v=VS.100%29.aspx).
我试着设置自动增加=真/假的主键的DataColumn的,但结果是一样的:过去的MAX-ID + 1,而不是数据库生成的实际ID(SQL-Server 2005的EP; PK数据类型:INT,是一种身份:是的,标识增量:1)
I've tried to set AutoIncrement=true/false on the primary-key's DataColumn but the result is the same: last MAX-ID+1 instead of the actual ID the database generated(SQL-Server 2005 EP; pk datatype:int, Is identity:yes, Identity Increment:1).
这将是错误的值FE如果应用程序的另一个实例插入一个记录,一审尚未知道,因此产生一个已经存在的ID。
This will be the wrong value f.e if another instance of the application inserted a record that the first instance yet not knows and therefore generates an ID that already exists.
从检索新的身份分贝我已经下到我的插入命令的CommandText中所附的:
To retrieve the new identity from db i've appended following to my insert-command's CommandText:
;SELECT CAST (SCOPE_IDENTITY() AS int) AS newIdRMA
也是我试图添加一个输出参数,它的参数集合:
Also i've tried to add an Output-Parameter to it's parameter-collection:
这是我的code部分,更新数据库,并设置了新的ID(即不工作):
This is part of my code that updates the database and sets the new ID(that doesn't work):
Me.dsRMA.RMA.AddRMARow(newRMA) ' adding new row to the (strong typed) DataTable '
numRowsUpdated = daRMA.Update(Me.dsRMA.RMA) ' update via DataAdapter and insert the new record in DB '
DirectCast(Page, Services).IdRma = newRMA.IdRMA ' this is not the actual value from DB but old Max-ID +1 '
编辑:
这是我的TableAdapter的InsertCommand会的截图,它的参数集合:
this is a screenshot of my TableAdapter's InsertCommand and it's parameter-collection:
在此先感谢。
推荐答案
下面我要做的事:我追加下面我插入命令的CommandText中:
Here How i do it: I append the following to my insert-command's CommandText:
--Return the new id
SELECT SCOPE_IDENTITY()
然后,我设置了 ExecuteMode 的查询,以标,而不是 NonQuery
Then I set the "ExecuteMode" of that Query to "Scalar" instead of "NonQuery"
所以我可以retrive这种方式:
so I could retrive it this way:
newRMA = Me.dsRMA.RMA.AddRMARow()
这篇关于从插入的记录的主键检索标识值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!