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 :
private void FunInsOrderDetails()
{
using (OleDbConnection connection = new OleDbConnection(DataConnection.constring))
{
OleDbTransaction transaction = null;
try
{
OleDbCommand command = connection.CreateCommand();
connection.Open();
transaction = connection.BeginTransaction();
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();
}
}