HI all,
VS 2005 C# MSSQL2005
I have a parent child grandchild related tables. Claimheader, claim (deteails), claimfinancialloss
When ever I try to add row to claimfinancialloss table, I get this error, if and only if the claim detail table is empty:
Obviously as the foreign key is 0.
System.Data.SqlClient.SqlException was unhandled
Message="The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Claim_claimheader\". The conflict occurred in database \"RASRMIS\", table \"dbo.claimheader\", column 'headerid'.\r\nThe statement has been terminated."
IF the claim detail has atleast one row in it, then the data is added. The the insert works
Briefly here is what I do:
DataRow chrow = ds.claimheader.NewRow();
DateTime dt = Convert.ToDateTime(rows["DOL"].ToString());
DateTime drep = Convert.ToDateTime(rows["DREP"].ToString());
chrow["dhid"] = rows["dhid"];
chrow["DOL"] = dt.AddYears(4);
chrow["DREP"] = drep.AddYears(4);
chrow["DFIN"] = rows["DFIN"];
chrow["brkref"] = rows["BRK"];
chrow["clmperiod"] = rows["clmperiod"];
ds.claimheader.Rows.Add(chrow);
//update the claims details
clhprimarykey = Convert.ToInt32( chrow["headerid"].ToString());
DataRow claimrow = ds.Claim.NewClaimRow();
claimrow["headerid"] = clhprimarykey;
claimrow["ctkey"] = rows["ctkey"];
claimrow["brkref"] = rows["BRK"];
claimrow["DET"] = rows["DET"];
DataRow[] childrowperil = rows.GetChildRows(ds.Relations["dbfimport_Peril"].RelationName);
if (childrowperil.Length>0)
{
claimrow["peril"] = childrowperil[0]["perilid"];
}
DataRow[] childrowcause = rows.GetChildRows("dbfimport_Cause");
if (childrowcause.Length>0)
{
claimrow["cause"] = childrowcause[0]["causeid"];
}
DataRow[] childrowfault = rows.GetChildRows("dbfimport_Fault");
if (childrowfault.Length>0)
{
claimrow["fault"] = childrowfault[0]["faultid"];
}
DataRow[] childrowresc = rows.GetChildRows("dbfimport_Resc");
if (childrowresc.Length>0)
{
claimrow["resc"] = childrowresc[0]["rescid"];
}
claimrow["Assid"] = 5;
claimrow["polsecdiv"] = rows["PSD_Key"];
claimrow["companyid"] = rows["Companyid"];
ds.Claim.Rows.Add(claimrow);
//Update and add claim financial loss details
//get the newly added claimid
int claimid = Convert.ToInt32(claimrow["ClaimID"].ToString());
DataRow cflRow = ds.ClaimFinancialLoss.NewClaimFinancialLossRow();
//Add MD
cflRow["Ammount"] = rows["MD"] == DBNull.Value ? 0.00 : rows["MD"];
cflRow["Clacorder"] = 1;
cflRow["ClaimId"] = claimid;
cflRow["calcxs"] = true;
cflRow["losstype"] = 1; //MD
cflRow["inclgross"] = true;
ds.ClaimFinancialLoss.Rows.Add(cflRow);
}
ch.Update(ds);
claimdetail.Update(ds);
cfl.Update(ds);
It seems that when the table is empty the claimid after inserting a rows is still 0, or is the same as the claimheaderid, which also seems to be the same as the row added, so if it is two rows added then claimheaderid and claim id will be 2,even tho select ident_current() shows the correct last values, as 5777 from claimheaderid, and 5026 from claimid.
But if the claim table has a row in it, then the claimid has a or next correct value.
What am I missing here
Thanks
Robert