Invalid identifier returned after insertion using TableAdapter

Question

When I do an insert with a TableAdapter :

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

It returns an invalid identifier of 1 , while it should return 15.

How to get the correct identifier?

Answer 1

set the runtime property to Scalar , after which you will get an identifier , otherwise the rows were damaged. You are not setting the query properties properties window in the query wizard.

(fig. 28)

Answer 2

The table adapter returns the number of rows that are not affected by the identifier.

Answer 3

I assume you have a pid column with an auto-generated value.

The answer to this post has the answer.

 select @@pid 

From the same open connection should do it.

Answer 4

1) Stored procedure:

The body of the stored procedure should look like this:

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

The important part here is to use SELECT SCOPE_IDENTITY () rather than RETURN SCOPE_IDENTITY (). Edit: using RETURN will work if you call a function instead of a stored procedure.

2) Table adapter:

Right-click on your table adapter and select "Add Query" from the context menu. The Query Configuration Wizard is created:

• Select the type of command: select "Use an existing stored procedure."

• Select the data form returned by SP: select "Single value".

• Enter the appropriate name for the method, for example. InsertXYZ

3) Your code:

Now, when you call the InsertXYZ method in your table adapter, it returns an object that you can pass to Int32. This value is the identifier of the new record!

Answer 5

How about this message. ASP.NET table insertion does not return the correct identity value.

I really don't know C #, but can a function return the number of rows affected or a boolean value of 1 for true or 0 for false?