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.

Answer 2

All of the info is here, but I didn't find any single answer complete, so here is the complete steps I use.

Add an insert query, and append SELECT SCOPE_IDENTITY() to it, like so:

INSERT INTO foo(bar) VALUES(@bar);
SELECT SCOPE_IDENTITY()

Make sure you add a ; to the end of the INSERT statement that VS creates for you.

After you Finish the add query wizard, make sure the query is selected in the design view then change Execute Mode to Scalar from the properties pane.

Make sure you use Convert.ToInt32() when you call the query from your code, like so:

id = Convert.ToInt32( dataTableAdapter.myInsertQuery("bar") )

You will not get compiler errors without the Convert.ToInt32, but you will get the wrong return value.

Also, any time you modify the query, you have to reset the Execute Mode back to Scalar, because VS will change it back to Non Query every time.

Answer 3

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

Here's how you do it (in the visual Designer)

1. Right Click the Table Adapter and "Add Query"
2. SQL Statements - Choose Update (best auto-gen parameters)

3. Copy and paste your SQL, it can be multi-line, just make sure that the "Query Designer" doesn't open up, as it will not be able to interpret the multiple commands - my example shows a sample "merge" set of statements (note that new SERVERS have Merge commands).

   UPDATE YOURTABLE
   SET  YourTable_Column1 = @YourTable_Column1, YourTable_Column2 = @YourTableColumn2
   WHERE (YourTable_ID = @YourTable_ID)
   IF @@ROWCOUNT=0
     INSERT INTO YOURTABLE ([YourTable_Column1], [YourTable_Column2])
     VALUES (@YourTable_Column1, @YourTable_Column2)
   @YourTable_ID = SCOPE_IDENTITY()
   

4. Change/Add the @YourTable_ID Parameters from the query properties window/sidebar. In the Parameter Collection Editor, The ID parameter needs to be have a Direction of InputOutput, so that the value gets updated when the Table Adapter function is called. (Special note: Make sure that whatever column you make InputOutput that the designer doesn't have this column as "Read Only" and that the data types match up as well, otherwise change the column in the datatable, or the parameter information accordingly)

This should save the need of writing a Stored procedure for such a simple activity.

Much Wow. You will notice that this method is a fast way of doing Data Layer functions without having to break into the SQL procedures and write up a ton of Procedures. The only problem, there is a lot of dancing you have to do...

Answer 5

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 6

You have exactly two choices:

• change your SQL code manually
• use whatever Visual Studio generates

I'd use the following SQL and ExecuteScalar.

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

Answer 7

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