C#•11mo ago

I Keep On Getting An SQLException When Trying To Update My Database

I keep on getting an "SqlException: Cannot insert explicit value for identity column in table 'Loans' when IDENTITY_INSERT is set to OFF" even though I am not expliitlyl inserting a value on a identity column on the table. What I am using is using the ID from another table called Applicants & updating a column in Loans called ApplicantID with the ID from this table, I don't get this error. I am using ASP.NET Core MVC, I just got an internship & the company uses this framework & I've been trying to learn it on my own because this is the first time ever in my life touching C#. Please upgrade the appsettings.json to your configuration if you intend to download the solution and figure out what is wrong. I am also willing to explain every question about a function or class because I didn't document the code. Also I didn't push the migrations to the github page. https://github.com/inalelub/homeloan

GitHub

GitHub - inalelub/homeloan

Contribute to inalelub/homeloan development by creating an account on GitHub.

18 Replies

Jimmacle•11mo ago

where is the exception being thrown? we generally help with specific issues given an appropriate amount of detail, it's unlikely someone will clone your project (that is incomplete if migrations are missing) and try and figure out what you're talking about

Bulelani BotmanOP•11mo ago

okay, thank you for this clarification i will try to break it down The exception is being thrown when entity framework tries to save the entity changes. which mean that i can use

_context.Loans.Add(obj)

_context.Loans.Add(obj)

it runs perfectly when i save changes

_context.SaveChanges()

_context.SaveChanges()

it gives me an exception. The inner exception reads, "SqlException: Cannot insert explicit value for identity column in table 'Loans' when IDENTITY_INSERT is set to OFF." To add more context to this situation, I am not even trying to explicitly add a value on the column which is labeled a identity column

Jimmacle•11mo ago

Ok but where in your entire project is that happening Like file and line number

Bulelani BotmanOP•11mo ago

Its happening in the Controller named

LoanController

LoanController

in a function named

public IActionResult Calculate

public IActionResult Calculate

on line 130 The whole function

    public IActionResult Calculate(int id, HomeLoan loan)
    {
        Calculations calculations = new Calculations();
        var obj = _context.Applicants.FirstOrDefault(x => x.ID == id);
        _grossSalary = obj.GrossSalary;
        _creditScore = obj.CreditScore;
        _purchasePrice = obj.PurchasePrice;
        
        loan.MaximumInstallment = calculations.MaximumInstallment(_grossSalary);
        loan.HomeLoanPercentGranted = calculations.PercentageGranted(_creditScore);
        loan.DepositRequired = calculations.DepositRequired(_purchasePrice, _creditScore);
        loan.MonthlyInstallment = calculations.MonthlyInstallment(_creditScore, _purchasePrice);
        loan.ApplicationID = id;
        
        _context.Loans.Add(loan);
        _context.SaveChanges();
        
        return View();
    }

    public IActionResult Calculate(int id, HomeLoan loan)
    {
        Calculations calculations = new Calculations();
        var obj = _context.Applicants.FirstOrDefault(x => x.ID == id);
        _grossSalary = obj.GrossSalary;
        _creditScore = obj.CreditScore;
        _purchasePrice = obj.PurchasePrice;
        
        loan.MaximumInstallment = calculations.MaximumInstallment(_grossSalary);
        loan.HomeLoanPercentGranted = calculations.PercentageGranted(_creditScore);
        loan.DepositRequired = calculations.DepositRequired(_purchasePrice, _creditScore);
        loan.MonthlyInstallment = calculations.MonthlyInstallment(_creditScore, _purchasePrice);
        loan.ApplicationID = id;
        
        _context.Loans.Add(loan);
        _context.SaveChanges();
        
        return View();
    }

Jimmacle•11mo ago

have you used your debugger to check what the loan's ID is set to before calling SaveChanges?

Bulelani BotmanOP•11mo ago

I didn't but I did check what the

loan.ApplicationID

loan.ApplicationID

is set to & its set to what I wanted it to be. I just checked right now & its set to 1 because this is the first ever row that being created in the table Loans I have attached a screenshot so you can see

Jimmacle•11mo ago

can you help me out here and do what i asked instead of something unrelated to the reason you're getting an error? it shouldn't be 1 it should be 0 if it's 1, EF will try to insert it with an ID of 1 if you want the database to generate it, it needs to be the default value for the type which is 0

Bulelani BotmanOP•11mo ago

💔😔, even though i did?

Jimmacle•11mo ago

i didn't see it until i read past the other stuff

Bulelani BotmanOP•11mo ago

i don't know why its one but is there a reason behind it not being assigned to 0 at first?

Jimmacle•11mo ago

i don't know because i don't know where that HomeLoan instance is coming from it's being set to 1 before you even enter this method

Bulelani BotmanOP•11mo ago

should i rather create an object of the homeloan instance inside the method instead of passing it into the function? i think its because i am passing it through and its getting instantiated somewhere else where i don't know either

Jimmacle•11mo ago

it looks like you're using EF entities as DTOs which is a huge no-no in general you should have separate models that you use for the frontend and map those to EF entities

Bulelani BotmanOP•11mo ago

what are DTOs? mhhh! i have only 2 models & i am using both of them for the front end

Jimmacle•11mo ago

Data Transfer Objects, basically "dumb" classes that only exist for the purpose of moving data to and from your frontend

Bulelani BotmanOP•11mo ago

i used the HomeLoan instance as an argument because i saw on a tutorial that whenever you want to create an object to be created & added on the database you should pass the model which i did. this function here is a function for a create function in the controller

    public IActionResult Create(Applicant createApplicant)
    {
        if (!ModelState.IsValid)
        {
            return View();
        }
        _context.Applicants.Add(createApplicant);
        _context.SaveChanges();
        return RedirectToAction("Index");

    public IActionResult Create(Applicant createApplicant)
    {
        if (!ModelState.IsValid)
        {
            return View();
        }
        _context.Applicants.Add(createApplicant);
        _context.SaveChanges();
        return RedirectToAction("Index");

woah! so, that shouldn't be the case, mhh! it's getting late where i live & i'm calling it a night. i've been trying to fix this issue the whole day, i'm beyond tired

Jimmacle•11mo ago

ignoring the bad practice for now, you just need to figure out where you're getting that HomeLoan instance from because somewhere between it getting created and it getting passed to your method, its ID is getting changed

Bulelani BotmanOP•11mo ago

that's true, i need to figure out where its getting the 1 from! i will do that. THANK YOU, SO MUCH FOR YOUR ASSISTANCE

Gaming

Programming

I Keep On Getting An SQLException When Trying To Update My Database