I want to be able to enter data to amount based on whether the social number is correct. I always get the error message SqlException: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.. I have tried to make a sql command to the database and set Identity to ON, but I still get the same error. Is it possible to do what I want to do?

My Class

public class AddAmount : IAddAmount
    {
        private readonly ApplicationDbContext _db;

        public AddAmount(ApplicationDbContext db)
        {
            _db = db;
        }

        public void Insert(int number)
        {
            Console.WriteLine("Add your amount you would like to insert: ");

                try
                {

                var addAmount = _db.Customers.SingleOrDefault(x => x.SocialNumber == number);
                addAmount.Amount = Convert.ToInt32(Console.ReadLine());
                 _db.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT dbo.Customers ON");
                _db.Add(addAmount);
                _db.SaveChanges();

                }
                catch (Exception)
                {

                    throw;
                }
        }

    }

CodePudding user response：

It depends on what you want your program to do exactly.

I understand that you have a Customer table. It is already filled with customer-data. You want to update the amount. In this case there is no need to Add (and thus insert) a record. You should simply omit this statement

_db.Add(addAmount);

It could be, that you wish to insert a new record with this amount (that is not logical in this case, because that would lead to duplicate 'customers' in your table). Then you should first fetch a customer from your database. Create a new copy of this object, with all the fields you need for a customer (but without the ID field copied). Change the amount on this copy, and then add the record.