Getting @@IDENTITY from TableAdapter

Question

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

Here's my 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");
}

So you might say "Oh yea, all you have to do is ExecuteNonQuery by changing the ExecuteMode property for the TableAdapter" like this StackOverflow Answer. XXX Incorrect. I already tried this. Not only does it make GetData() quit working (cause I'm returning a scalar now instead of rowdata) (I need to KEEP GetData()), but it also doesn't fix the problem. The insertedID variable is still set to the value 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.

Worse yet, the generated insert command is

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

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

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

So what's wrong?

EDIT/UPDATE 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.

Answer 1

You'll need to setup the insert to return the identity as an output value and then grab it as a parameter in your adapter.

These two links should get you going:

http://www.akadia.com/services/dotnet_autoincrement.html

http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

Answer 2

One way is to run a select query after the insert command. A good way is to wrap the original command like this:

public int WrapInsert(Parameters)
        {
            .....
            int RowsAffected = this.Insert(..Parameters..);
            if ( RowsAffected > 0)
            {
                try
                {
                    SqlCommand cm = this.Connection.CreateCommand();
                    cm.CommandText = "SELECT @@IDENTITY";
                    identity = Convert.ToInt32(cm.ExecuteScalar());
                }
                finally
                {
                    ....
                }
            }

            return RowsAffected;
        }

Answer 3

Hi - see if this helps.

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

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

Then from code, all I do is:

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

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

Good luck.

Answer 4

You have exactly 2 choices

• change your SQL code manually
• use whatever VS generates

I'd use this SQL and ExecuteScalar

INSERT INTO [dbo].[Sites] ([Name], [Description])
OUTPUT INSERTED.ID
VALUES (@Name, @Description);