Incorrect ID is returned after insert using TableAdapter

Question

When I perform an insert with TableAdapter:

int pid = this.purchaseTableAdapter.Insert(supplierid, datetime, "", 
    totalprice, amountpaid);

It returns the incorrect id 1 while it should return 15.

How to get the correct ID?

Answer 1

set the execute mode property to Scalar, then you get the ID, otherwise the rows-affected. You set the property properties window of the query not in the Query wizard.

(fig 28)

Answer 2

The table adapter returns the number of rows affected not the id.

Answer 3

I'm assuming that you have a pid column with an autogenerated value.

The reply to this post has the answer.

select @@pid 

From the same open connection should do it.

Answer 4

1) The stored procedure:

The body of your stored procedure must be similar to this:

INSERT INTO MyTable (C1, C2, C3) VALUES (@c1, @c2, @c3);

SELECT SCOPE_IDENTITY();

The important part here is to use SELECT SCOPE_IDENTITY() and not RETURN SCOPE_IDENTITY(). Edit: using RETURN will work if you are calling a function instead of a stored procedure.

2) The table adapter:

Right click on your table adapter and select Add Query from the context menu. The TableAdapter Query Configuration Wizard pops-up:

• Choose a Command Type: select "Use existing stored procedure".

• Choose the shape of data returned by the SP: select "A single value".

• Enter an appropriate name for the method, e.g. InsertXYZ

3) Your code:

Now, when you call the method InsertXYZ on your table adapter, it will return an object which you can cast to Int32. That value is the ID of the new record!