Create or update if item exist

Question

I have a create form where if the specific Medicine exist, its number of supply will update or added with the new entry however if the specific Medicine doesn't exist, it will create a new batch of data.

Im having trouble at understanding how update works in MVC.

Here is the error:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

Here is my controller:

public ActionResult Create([Bind(Include = "SupplyID,MedicineID,Expiration,NumberOfSupply")] Supply supply)
    {
        if (ModelState.IsValid)
        {
            bool supplyExsist = db.Supplies.Any(x => x.Expiration == supply.Expiration && x.MedicineID == supply.MedicineID);

            if (supplyExsist)
            {
                var currentSupply = (from x in db.Supplies //get current supply
                                     where x.MedicineID == supply.MedicineID
                                     && x.Expiration == supply.Expiration
                                     select x.NumberOfSupply).First();

                db.Entry(supply).State = EntityState.Modified;
                supply.NumberOfSupply = currentSupply + supply.NumberOfSupply;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            else
            {
                db.Supplies.Add(supply);
                db.SaveChanges();
                return RedirectToAction("Index");
            }
        }

        ViewBag.MedicineID = new SelectList(db.Medicines, "MedicineID", "MedicineName", supply.MedicineID);
        return View(supply);
    }

Model:

public class Supply
{
    [Key]
    public int SupplyID { get; set; }

    [ForeignKey("Medicine")]
    public int MedicineID { get; set; }
    public Medicine Medicine { get; set; }

    [DataType(DataType.Date)]
    public DateTime Expiration { get; set; }

    [Display(Name = "Quantity")]
    [Range(1, int.MaxValue, ErrorMessage = "The value must be greater than 0")]
    public int NumberOfSupply { get; set; }
}

See similar problem here: https://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec. Often this error comes from forgot to insert PK input on a hidden field in view. — Tetsuya Yamamoto, Jul 19 '17 at 07:54
@TetsuyaYamamoto you are right. but now now my problem is, how can I update the data not using its ID because the ID is not specified in the view. — , Jul 19 '17 at 08:15
If the ID is not an identity column it is necessary to include the ID. If you want to not use ID column, either set the column as autogenerated identity column or use session variable to store the last used ID then add the data into DB using incremented value of that ID. — Tetsuya Yamamoto, Jul 19 '17 at 08:19

score 0 · Answer 1 · answered Jul 19 '17 at 07:54

0

just try this

db.Supplies.AddOrUpdate(h => h.medicineID,supply));

it will check if there is a row with the same medicine ID in db if not it adds a new one else it updates it

answered Jul 19 '17 at 07:54

RAHUL S R

1,569
1
11
20

score 0 · Answer 2 · answered Jul 19 '17 at 07:55

You should change your if block with following :

if (supplyExsist)
            {
                var currentSupply = (from x in db.Supplies //get current supply
                                     where x.MedicineID == supply.MedicineID
                                     && x.Expiration == supply.Expiration
                                     select x.NumberOfSupply).First();

                db.Supplies.Attach(supply);
                db.Entry(supply).State = EntityState.Modified;
                supply.NumberOfSupply = currentSupply + supply.NumberOfSupply;
                db.SaveChanges();
                return RedirectToAction("Index");
            }

Create or update if item exist

2 Answers2