I was having the same issue on Visual Studio 2010, sql 2008 R2 and using WinForms, but I solved it.
I also had my tables with auto generated primary key from the server (auto-increment)
I used a DataSet and messed around with the DataSet Designer, created relationships, etc. But since I configured TableAdapters I didn't have to manually create the sql commands on my form class, which has one BindingNavigator with textboxes (Master table) and one DataGridView (Detail table).
I experienced the same issue he tells, if trying to save a new record from both the Master (bindingNavigatorAddNewItem in my case) and it's details (a new row), it will throw me an sqlException on my tableAdapterManager1.UpdateAll(DataSet1). Because the new master id (-1) must exist on the table before inserting a new detail along with its master id (FK_master_detail).
mhegazy94's answer gave me an idea, return the new id in the same insert query, so I went to the
DataSet.Designer.cs and scrolled from all that bunch of code, to the TableAdapter's
insertCommand of my master table. and after the INSERT INTO query, I added the following
SELECT column1,column2.. FROM yourmastertable
WHERE (id_column = SCOPE_IDENTITY())";
in my dataset designer's class it looks like this:
this._adapter.InsertCommand.CommandText = @"INSERT INTO Pedidos(fechaPedido,cant_pollitosNoVac,cant_pollitosVac,precioUnitario_pollitosNoVac,precioUnitario_pollitosVac,valor_PollosNoVac,valor_PollosVac,valorTotal_despacho,saldoPimpollo) VALUES(@fechaPedido,@cant_pollitosNoVac,@cant_pollitosVac,@precioUnitario_pollitosNoVac,@precioUnitario_pollitosVac,@valor_PollosNoVac,@valor_PollosVac,@valorTotal_despacho,@saldoPimpollo) SELECT idPedido, fechaPedido, cant_pollitosNoVac, cant_pollitosVac, precioUnitario_pollitosNoVac, precioUnitario_pollitosVac, valor_PollosNoVac, valor_PollosVac, valorTotal_despacho, saldoPimpollo FROM Pedidos
WHERE (idPedido = SCOPE_IDENTITY()";
Hope this helps someone else if using Datasets!!!
If wondering how I figure this out, this video helped me:
https://msdn.microsoft.com/es-es/vstudio/cc138241.aspx[
^], she shows exactly what this is all about, saving both a master and detail record together, so at the end of the video I looked closely at the SQL Server Profiler and checked the first query that was executed (insert into orders)