Adding Data to Database (Error: System.Data.Entity.Infrastructure.DbUpdateException)

Question

I am trying to insert input data from a Windows form into a database and get this error

System.Data.Entity.Infrastructure.DbUpdateException

and when I checked the innerExeption I found this

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Tools_dbo.Locations_LocationId". The conflict occurred in database "TMDatabase", table "dbo.Locations", column 'LocationId'.

But and kind of confuse course I did every thing right I think what is really the problem please Help with how to resolve it thank you

There as my models:

public class Tools : BaseEntity
{
    public int ToolId { get; set; }
    public string Name { get; set; }
    public string Serial { get; set; }
    public int Quantity { get; set; }
    public bool IsCalibarted { get; set; }
    public DateTime DueDate { get; set; }
    public string Discription { get; set; }
    public byte[] Image { get; set; }

    //Reference Mapping
    public int LocationId { get; set; }
    public virtual Locations Locations { get; set; }
}

public class Locations : BaseEntity
{
    public int LocationId { get; set; }
    public string LocationCode { get; set; }
    public string LocationName { get; set; }
    public string Discription { get; set; }

    public virtual ICollection<Tools> Tools { get; set; }
}

I am using the Fluent API for my mapping and it goes like this

public class ToolsEntityConfig : EntityTypeConfiguration<Tools>
{
    public ToolsEntityConfig()
    {
        this.HasKey(t => t.ToolId);
        this.Property(t => t.Name).IsRequired();
        this.Property(t => t.Serial).IsRequired();
        this.Property(t => t.Quantity).IsRequired();
        this.Property(t => t.DueDate).IsOptional();
        this.Property(t => t.Discription).IsOptional();

        this.HasRequired(t => t.Locations)
            .WithMany(t => t.Tools)
            .HasForeignKey(t => t.LocationId)
            .WillCascadeOnDelete(false);
    }
}

This is my create button event handler code and am using a DTO for seeding into database

The DTO

public class ToolsDTO
{
    public ToolsDTO()
    {
    }

    public ToolsDTO(Tools tools)
    {
        this.ToolId = tools.ToolId;
        this.Name = tools.Name;
        this.Serial = tools.Serial;
        this.Quantity = tools.Quantity;
        this.DueDate = tools.DueDate;
        this.IsCalibrated = tools.IsCalibarted;
        this.Discription = tools.Discription;
        this.Image = tools.Image;

        //this.CategoryId = tools.CategoryId;
        this.LocationId = tools.LocationId;
        //this.LayerId = tools.LayerId;
        //this.SupplyId = tools.SupplyId;
    }

    public int ToolId { get; set; }
    public string Name { get; set; }
    public string Serial { get; set; }
    public int Quantity { get; set; }
    public DateTime DueDate { get; set; }
    public string Discription { get; set; }
    public bool IsCalibrated { get; set; }
    public byte[] Image { get; set; }

    //Reference Mapping
    public int CategoryId { get; set; }
    public int LocationId { get; set; }
    public int LayerId { get; set; }
    public int SupplyId { get; set; }
}

The service that does the insert

public ToolsDTO CreateTools(ToolsDTO tool)
{
        var tools = dtoToEntity(tool);
        UoW.Tools.Add(tools);
        UoW.Commit();

        return new ToolsDTO(tools);
}

private Tools dtoToEntity(ToolsDTO tool)
{
        var addtool = new Tools
        {
            ToolId = tool.ToolId,
            Name = tool.Name,
            Serial = tool.Serial,
            Quantity = tool.Quantity,
            DueDate = tool.DueDate,
            Discription = tool.Discription
        };

        return addtool;
}

This is the the editable object that communicates with the Windows form

public class ToolsObject : ToolsDTO, IEditableObject 
{
    private ToolsObject _OriginalObject;
    private bool _Editing;

    public ToolsObject(int Id, string serial, string name, int quantity, DateTime duedate, string desc, int locId)
    {
        this.ToolId = Id;
        this.Serial = serial;
        this.Name = name;
        this.Quantity = quantity;
        this.DueDate = duedate;
        this.Discription = desc;
        this.LocationId = locId;
    }

    public ToolsObject(int Id, string serial, string name, int quantity, DateTime duedate, bool IsCalib, string desc, byte[] image, int locId)
    {
        this.ToolId = Id;
        this.Serial = serial;
        this.Name = name;
        this.Quantity = quantity;
        this.DueDate = duedate;
        this.Discription = desc;
        this.IsCalibrated = IsCalib;
        this.Image = image;
        this.LocationId = locId;
    }

    public ToolsObject()
    {
    }

    public void BeginEdit()
    {
        if (!_Editing)
        {
            _Editing = true;
            _OriginalObject = this.MemberwiseClone() as ToolsObject;
        }
    }

    public void CancelEdit()
    {
        if (_Editing)
        {

            this.Name = _OriginalObject.Name;
            this.Serial = _OriginalObject.Serial;
            this.Quantity = _OriginalObject.Quantity;
            this.DueDate = _OriginalObject.DueDate;
            this.Discription = _OriginalObject.Discription;
            this.IsCalibrated = _OriginalObject.IsCalibrated;
            this.Image = _OriginalObject.Image;
            this.CategoryId = _OriginalObject.CategoryId;
            this.LocationId = _OriginalObject.LocationId;
            this.SupplyId = _OriginalObject.SupplyId;

            _Editing = false;
        }
    }

    public void EndEdit()
    {
        if (_Editing)
        {
            _Editing = false;
            _OriginalObject = null;
        }
    }
}

This is the create button event handler:

private void AddTool()
{
        MemoryStream ms1 = new MemoryStream();

        this.ptbTools.Image.Save(ms1, ImageFormat.Jpeg);

        try
        {
            ToolsObject newtool = new ToolsObject();

            if (String.IsNullOrWhiteSpace(this.txtToolName.Text) || String.IsNullOrWhiteSpace(this.txtSerial.Text) || this.txtQuantity.Value == 0 || this.cmbLocation.SelectedItem == null)
            {
                MessageBox.Show("All fields with (*) are required");
                return;
            }

            if (this.chbCalib.Checked == true)
            {
                if (this.dtpCalibrateDate.Value == DateTime.Today)
                {
                    MessageBox.Show("Set Calibration Due-Date");
                    return;
                }
                this.chbCalib.Checked = true;
                newtool.IsCalibrated = true;
            }
            else if (!this.chbCalib.Checked)
            {
                this.chbCalib.Checked = false;
                newtool.IsCalibrated = false;
                dtpCalibrateDate.Value = DateTime.Now;
            }

            var selectedLocationId = ((LocationObject)cmbLocation.SelectedItem).LocationId;

            newtool.Name = this.txtToolName.Text;
            newtool.Serial = this.txtSerial.Text;
            newtool.Quantity = (int)this.txtQuantity.Value;
            newtool.LocationId = selectedLocationId;
            newtool.Discription = this.txtDescription.Text;
            newtool.IsCalibrated = this.chbCalib.Checked;
            newtool.DueDate = this.dtpCalibrateDate.Value;
            newtool.Image = ms1.ToArray();

            var tool = toolService.CreateTools(newtool);

            MessageBox.Show(string.Format("You have successfully created {0} tool", tool.Name));

            toolsList.Add(new ToolsObject(tool.ToolId, tool.Serial, tool.Name, tool.Quantity, tool.DueDate, tool.IsCalibrated, tool.Discription, tool.Image, tool.LocationId));

            bsTools.ResetBindings(true);
            bsTools.ResumeBinding();

            ToggleUI(UIMode.Initial);

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

Please help I don't really understand where the issue is coming from

Answer 1

private Tools dtoToEntity(ToolsDTO tool)
{
        var addtool = new Tools
        {
            ToolId = tool.ToolId,
            Name = tool.Name,
            Serial = tool.Serial,
            Quantity = tool.Quantity,
            DueDate = tool.DueDate,
            Discription = tool.Discription
        };

        return addtool;
}

In your dtoToEntity, you seem to be missing your locationId, which would cause it to give you this error. Try inserting it in.

private Tools dtoToEntity(ToolsDTO tool)
{
    var addtool = new Tools
    {
        ....
        LocationId = tool.LocationId,
        ....
    };
    return addtool;
}