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?

Trefoil

Please reformat your question to make it a question and to make it readable. – Herod

It is a questions now :) – Trefoil

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)

Lifeline

Be aware that (as is mentioned in the article) you still need to modify the insert-query to have it return the requested identity. – Herod

Answer 2

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

Herod

It's not really an issue, it's by design :-) – Sludge

There should be a way round for this 'feature' – Trefoil

What would the Insert have to return when multiple rows have been inserted? How would the Insert method 'know' what column contains the id you are looking for? In other words: follow the links in the other answers and see how others handle this. – Herod

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.

Knorring

pid is PurchaseId with autoincremented value – Trefoil

Talk about the circular nature of life; I was the one who wrote that reply. – Dislike

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!

Instate