INSERT statement fails - Foreign key conflict

Question

0.00/5 (No votes)

See more:

I've done this numerous times

Big picture:
CRUD WinForms application with SQL Server 2008 R2 database.
I 've added few new things and one which I haven't meddled with is now broken.

Code is standard, nothing exotic here and it worked before last changes.

C#

try{
//...
    trans = _connection.BeginTransaction();
    
    //adapter responsible for particular parent table
    _workingOrdersAdapter.Transaction = trans;
    _rawMaterialsStockAdapter.Transaction = trans;
    _stockEntriesAdapter.Transaction = trans;
    _stockEntriesItemsAdapter.Transaction = trans;
    _stockReservationAdapter.Transaction = trans;
    _mixingPlanUtemsAdapter.Transaction = trans;

    retval = _workingOrdersAdapter.Update(dataSet.WorkingOrders);
    retval += _mixingPlanUtemsAdapter.Update(dataSet.MixingPlanItems.Select("","", DataViewRowState.Deleted));
    retval += _mixingPlanUtemsAdapter.Update(dataSet.MixingPlanItems.Select("","", DataViewRowState.ModifiedCurrent));
                
    retval += _rawMaterialsStockAdapter.Update(dataSet.RawMaterialsStock);
    retval += _stockEntriesAdapter.Update(dataSet.StockEntries);
    retval += _stockEntriesItemsAdapter.Update(dataSet.StockEntryItems);

    //here it fails. grrrrr
    retval += _mixingPlanUtemsAdapter.Update(dataSet.MixingPlanItems.Select("", "", DataViewRowState.Added));
    //this line is new. 
    retval += _stockReservationAdapter.Update(dataSet.StockReservations);


     trans.Commit();

}catch(SqlException sqlex)
{
//...
}

When updating data from dataset to DB on one data adapter INSERT fails:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MixingPlanItems_WorkingOrders". The conflict occurred in database "Recepture", table "dbo.WorkingOrders", column 'WorkingOrderID'.
The statement has been terminated.

WTH?!?
When I execute query in Management Studio it works perfectly.
It is a simple INSERT query.

When updating data (with UPDATE query) it runs as it should.

So far I have done the following:
Configured data adapter several times.
Removed and added particular adapter again.
Removed and added particular table connection in DB.

Please excuse me if I am being incomprehensible I have't slept much. :(

Posted 3-Jan-13 23:29pm

Oshtri Deka

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pooja S Patil · Answer 1 · 2013-01-03T23:32:00

GlobalClass c = new GlobalClass();

protected void imgbtnsave_Click(object sender, ImageClickEventArgs e)
{

OracleCommand InsertUser = new OracleCommand("ins_domain_info",con);
InsertUser.CommandType=CommandType.StoredProcedure;

InsertUser.Parameters.Add("@domain_info_url", OracleDbType.Varchar2);
InsertUser.Parameters["@domain_info_url"].Value = txt_url.Text;

InsertUser.Parameters.Add("@website_registrant", OracleDbType.Varchar2);
InsertUser.Parameters["@website_registrant"].Value = txt_websiteregistrant.Text;

InsertUser.Parameters.Add("@created_date", OracleDbType.Date);
InsertUser.Parameters["@created_date"].Value = txt_createddate.Text;

InsertUser.Parameters.Add("@expiry_date", OracleDbType.Date);
InsertUser.Parameters["@expiry_date"].Value = txt_expirydate.Text;

InsertUser.Parameters.Add("@domain_age_in_years", OracleDbType.Varchar2);
InsertUser.Parameters["@domain_age_in_years"].Value = txt_domainage.Text;

InsertUser.Parameters.Add("@last_updated", OracleDbType.Date);
InsertUser.Parameters["@last_updated"].Value = txt_lastupdate.Text;

InsertUser.Parameters.Add("@top_level_url", OracleDbType.Varchar2);
InsertUser.Parameters["@top_level_url"].Value = txt_TopLevelURL.Text;

InsertUser.Parameters.Add("@domain_result", OracleDbType.Varchar2);
InsertUser.Parameters["@domain_result"].Value = lbl_Domainresult.Text;

InsertUser.Parameters.Add("@recommendation", OracleDbType.Varchar2);
InsertUser.Parameters["@recommendation"].Value = txt_domainrecommendation.Text;

InsertUser.Parameters.Add("@REGISTER_PROSPECT_ID", OracleDbType.Varchar2);
InsertUser.Parameters["@REGISTER_PROSPECT_ID"].Value = Session["seo_prosid"].ToString();

InsertUser.Parameters.Add("@domain_info_id", OracleDbType.Int64, 20);
InsertUser.Parameters["@domain_info_id"].Direction = ParameterDirection.Output;

try
{
if (con.State == 0)
{
con.Open();
}
InsertUser.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

finally
{

MessageBox.Show("Data Saved Successfully");
clear();
con.Close();
}

}

INSERT statement fails - Foreign key conflict

1 solution

Solution 1

Add your solution here

Preview 0

INSERT statement fails - Foreign key conflict

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us