充分利用的TableAdapter @@ IDENTITY [英] Getting @@IDENTITY from TableAdapter

问题描述

我试图完成一个看似简单的任务，已经变成了几个小时的冒险：获得 @@身份从 TableAdapter.Insert（） 。

I am trying to complete a seemingly simple task that has turned into a several hour adventure: Getting @@Identity from TableAdapter.Insert().

下面是我的code：

protected void submitBtn_Click(object sender, EventArgs e)
{
    AssetsDataSetTableAdapters.SitesTableAdapter sta = new AssetsDataSetTableAdapters.SitesTableAdapter();
    int insertedID = sta.Insert(siteTxt.Text,descTxt.Text);

    AssetsDataSetTableAdapters.NotesTableAdapter nta = new AssetsDataSetTableAdapters.NotesTableAdapter();
    nta.Insert(notesTxt.Text, insertedID, null,null,null,null,null,null);
    Response.Redirect("~/Default.aspx");
}

一个的 answer建议所有我可能需要做的就是修改 ExecuteMode 。我试过了。这使得的GetData（）辞掉工作（因为我现在回来，而不是rowdata标量）（我需要保持的GetData（））。它也没有解决，所述insertedID变量仍设置为1问题

One answer suggests all I may have to do is change the ExecuteMode. I tried that. This makes GetData() quit working (because I'm returning a scalar now instead of rowdata) (I need to keep GetData()). It also does not solve the issue in that the insertedID variable is still set to 1.

我试图创建第二个的TableAdapter 在 TypedDataSet.XSD 和该适配器设置属性为标，但它仍然失败，该变量获得的值为1。

I tried creating a second TableAdapter in the TypedDataSet.XSD and setting the property for that adapter to "scalar", but it still fails with the variable getting a value of 1.

生成的INSERT命令是

The generated insert command is

INSERT INTO [dbo].[Sites] ([Name], [Description]) VALUES (@Name, @Description);
SELECT Id, Name, Description FROM Sites WHERE (Id = SCOPE_IDENTITY())

和刷新数据表（增加后INSERT和UPDATE语句检索身份也设置了SELECT语句。

And the "Refresh the Data Table" (adds a select statement after Insert and Update statements to retrieve Identity" is also set.

环保

SQL Server 2008 R2中时，Visual Studio 2010，.NET 4，Windows XP中，所有本地同一台机器。

SQL Server 2008 R2, Visual Studio 2010, .NET 4, Windows XP, all local same machine.

是什么造成的？

修改/更新

我要澄清的是，我使用Visual Studio中自动生成的code。我不知道，产生的code中的工具是什么，但如果你双击* .xsd文件它显示的SQL表架构的一个UI和相关的TableAdapter的。我想用自动生成的code，以保持并以某种方式使得到认同。我不想写这个全部由手工用存储过程。

I want to clarify that I am using auto-generated code within Visual Studio. I don't know what the "tool" that generated the code is, but if you double click the *.XSD file it displays a UI of the SQL Table Schema's and associated TableAdapter's. I want to keep using the auto-generated code and somehow enable getting the Identity. I don't want to write this all by hand with stored procedures.

推荐答案

下面是我的SQL code的作品。

Here's my SQL Code that works.

CREATE PROCEDURE [dbo].[Branch_Insert]
(
    @UserId uniqueidentifier,
    @OrganisationId int,
    @InsertedID int OUTPUT
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Branch] ([UserId], [OrganisationId]) 
VALUES (@UserId, @OrganisationId);

SELECT Id, UserId, OrganisationId FROM Branch WHERE (Id = SCOPE_IDENTITY())
SELECT @InsertedID = SCOPE_IDENTITY()

然后，当我创建表适配器 - 我可以立即看到@InsertedID参数

Then when I create the Table Adapter - I can instantly see the @InsertedID parameter.

然后从code，所有我做的是：

Then from code, all I do is:

int? insertedId = 0;
branchTA.Insert(userId, orgId, ref insertedId);

我不是100％使用REF是否是最好的选择，但是这对我的作品。

I'm not 100% whether using ref is the best option but this works for me.

祝你好运。

这篇关于充分利用的TableAdapter @@ IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！