Have you ever had an insert statement in a datatableadapter in .NET that returned the wrong insert identity? Today I had a problem where a datatable adapter would always return the same SCOPE_IDENTITY for a simple datatable adapter. I fired up the debugger and watched the return from that insert method, and it was always returning 2. 

Diving further into the problem, I traced it up to the datatable adapter's insert method, and realized that the method in the datatableadapter designer properties had the ExecuteMode set to non-query. So, it was in fact returning the number of rows inserted, and not the insert identity, or primary key of the inserted element. The fix was as simple as changing the ExecuteMode to Scalar, and Voilé, it just works (and returns the primary key instead). It makes me feel stupid, and maybe Microsoft should fix that issue, so that if I change a method that ends up regenerating the tableadapter's methods, it keeps the ExecuteModes set as they previously were. It took me more time to build the fix to production than to make the change... just a bit frustrating!