Full error: .Net SqlClient Data ProviderThe INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_SalesOrderHead". The conflict occurred in database "ControlSecurity", table "dbo.SalesOrderHead", column 'id'.
DB Table information: FK_SalesOrder_SalesOrderHead (relationship) has primary key id from SalesOrderHead, and FK=SalesOrderID.
SalesOrderInsert(Stored Procedure)
[dbo].[SalesOrderInsert]
@SalesOrder udtSalesOrder readonly
AS
BEGIN
INSERT INTO SalesOrder
(ProductID,Quantity,UnitPrice,SalesOrderID)
SELECT ProductID,Quantity,UnitPrice,SalesOrderID
FROM @SalesOrder
END
User Defined table udtSalesOrder:
CREATE TYPE [dbo].[udtSalesOrder] AS TABLE(
[ProductID] [int] NULL,
[Quantity] [int] NULL,
[SalesOrderID] [int] NULL,
[UnitPrice] [int] NULL
)
I am adding a row to SalesOrderHead table, then get the scope id and use stored procedure to insert multiple rows from a datatable into SalesOrder table. All of this in one transaction in c# code. But it seems that the second ExecuteNonQuery does not know it's part of the transaction, or my understanding of the transaction scope is incorrect. Should I post query of the stored procedure here. I'm not sure.
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbconn"].ToString()))
{
try
{
con.Open();
using (var tran = con.BeginTransaction())
{
try
{
SqlCommand cmd = new SqlCommand("Insert into SalesOrderHead(cust_id,Description,OrderDate,DeliveryDate,Priority,CustomerRef) VALUES(@cust_id,@Description,@OrderDate,@DeliveryDate,@Priority,@CustomerRef) SELECT @id = CAST(SCOPE_IDENTITY() AS int)",con,tran);
cmd.Parameters.AddWithValue("@cust_id", CustomersList.EditValue);
cmd.Parameters.AddWithValue("@Description", Remarks.Text);
cmd.Parameters.AddWithValue("@OrderDate", OrderDate.DateTime);
cmd.Parameters.AddWithValue("@DeliveryDate", DeliveryDate.DateTime);
cmd.Parameters.AddWithValue("@Priority", PriorityComboBox.Text);
cmd.Parameters.AddWithValue("@CustomerRef", CustomerRef.Text);
cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
var SalesOrderHeadID = cmd.Parameters["@id"].Value;
cmd.Parameters.Clear();
cmd.CommandText = "SalesOrderInsert";
//cmd = new SqlCommand("SalesOrderInsert", con, tran);
//cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
//Add from SalesOrderTB to SalesOrder stored procedure
cmd.Parameters.AddWithValue("@SalesOrder", SalesOrderTB);
cmd.ExecuteNonQuery();
// Record inserted in both table
tran.Commit();
XtraMessageBox.Show("Added to database success");
con.Close();
}
catch (SqlException ex)
{
tran.Rollback();
Console.WriteLine(ex.Source + ex.Message);
}
}
}
catch (Exception)
{
throw;
}
}
