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");
}

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.

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.

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 Identity" is also set.

Environment

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

What's causing this?

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

The real answer:

• read the notes below!

Get identity from tableadapter insert function

I keep getting questions about this issue very often and never found time to write it down.

Well, problem is following: you have table with primary key with int type defined as Identity and after insert you need to know PK value of newly inserted row. Steps for accomplishing to do this are following:

use wizard to add new insert query (let's call it InsertQuery) in the body of query just add SELECT SCOPE_IDENTITY() at the bottom after saving this query, change ExecuteMode property of this query from NonQuery to Scalar in your code write following (ta is TableAdapter instance) :

int id;

try
{
 id = Convert.toInt32(ta.InsertQuery(firstName, lastName, description));
}
catch (SQLException ex)
{
//...
}
finally
{
//...
}

Make money with this! :) Posted 12th March 2009 by Draško Sarić

From: http://quickdeveloperstips.blogspot.nl/2009/03/get-identity-from-tableadapter-insert.html

Notes:

• Setting the ExecutMode to Scalar is possible via the Properties of your generated Insert Query. (press F4).

• In my version (Visual Studio 2010 SP1 ) the select statement was generated automatically.