Click here to Skip to main content
15,358,707 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to insert value in two table OrderDetails And CustOrder using transaction but it gives me error: You cannot add or change a record because a related record is required in table.
Code is :
C#
private void FunInsOrderDetails()
        {
            using (OleDbConnection connection = new OleDbConnection(DataConnection.constring))
            {
                OleDbTransaction transaction = null;
                try
                {
                    OleDbCommand command = connection.CreateCommand();                  
                    connection.Open();
                    transaction = connection.BeginTransaction(); // Assign Transaction to Command
                    command.Transaction = transaction;
                   command.CommandText= "insert into Custorder(OrderType,OrderNo,OrderDate,YourOrderNo,CustSuppId)values('" + cmbOrderType.Text + "'," + txtOrderNo.Text + ",'" + dtpDate.Value.ToString("yyyy-MM-dd") + "','" + txtYourOrderNo.Text + "'," + cmbCustName.SelectedValue.ToString() + ")";                  
                   command.ExecuteNonQuery();                   
                    FunInsOrder(command);
                    transaction.Commit();
                    connection.Close();
                }
                catch
                {
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }
            }           
        }

private void FunInsOrder(OleDbCommand cmd)
      {
          for (int i = 0; i < dgvOrder.Rows.Count - 1; i++)
          {
              str = "select p.ProjectId from Project p INNER JOIN Machine m on m.MachineId=p.MachineId where p.DrawingNo='"+dgvOrder.Rows[i].Cells[1].Value+"' AND p.PartName='"+dgvOrder.Rows[i].Cells[2].Value+"'AND m.MachineName='"+dgvOrder.Rows[i].Cells[0].Value+"'";
              int ProjectId = dc.RetutnID(str);
              str="Select OrderRepairId from CustOrder where OrderNo="+txtOrderNo.Text+" AND OrderType='"+cmbOrderType.Text+"'";
              int OrderRepairId = dc.RetutnID(str);
              if (cmbOrderNo.Visible == false)
              {
                  cmd.CommandText= "insert into OrderDetails(OrderType,OrderRepairId,OrderDate,ProjectId,MachineName,Quantity)values('"+cmbOrderType.Text+"'," + OrderRepairId.ToString()+ ",'" + dtpDate.Value.ToString("yyyy-MM-dd") + "'," + ProjectId.ToString() + ",'" + dgvOrder.Rows[i].Cells[3].Value + "'," + dgvOrder.Rows[i].Cells[4].Value + ")";
              }
              else
              {
                  cmd.CommandText = "insert into OrderDetails(OrderType,OrderRepairId,OrderDate,ProjectId,MachineName,Quantity)values('" + cmbOrderType.Text + "'," + cmbOrderNo.SelectedValue.ToString() + ",'" + dtpDate.Value.ToString("yyyy-MM-dd") + "'," + ProjectId.ToString() + ",'" + dgvOrder.Rows[i].Cells[3].Value + "'," + dgvOrder.Rows[i].Cells[4].Value + ")";
              }
              cmd.ExecuteNonQuery();
          }
      }
Posted
Updated 21-Aug-13 19:53pm
v3
Comments
_Asif_ 22-Aug-13 2:01am
   
It seems that you are inserting data in a child table but parent table does not have the related parent entries. The order of insertion should be first insert data in parent tables and then appropriately insert child tables with correct foreign keys.
Sadique KT 22-Aug-13 2:43am
   
I Agree
NABIN SEN 22-Aug-13 6:28am
   
i am inserting data in parent table then child table but it doesn't work......

1 solution

Reason here[^]
   

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900