Must declare the scalar variable - dapper with asp.net core

Question

This is my first dapper experience. I have read suggestions here but none seem to fix my issue.

Must declare the scalar variable "@tranAmount".

Some suggested if the variable in question is null. but I have checked, its not null.

I am hoping someone could help point out what I am doing wrong.

public void RunEndOfDays()
        {
            try
            {
                IEnumerable<Loan> loans = null;
                var currentMonth = DateTime.Now.Month; //current month
                var currentYear = DateTime.Now.Year;

                using (var conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();

                    loans = conn.Query<Loan>("Select * from Loan");

                    foreach (var loan in loans)
                    {
                        //now calculate the interest for this item
                        var interestInPercentage = loan.InterestRate / 100;
                        var interestPerDay = interestInPercentage / 365; //saved
                        var interestAmountPerDay = loan.AmountWrittenOff * interestPerDay;
                        var tranAmount = loan.AmountWrittenOff + interestAmountPerDay;//saved
                        var tranDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFF");
                        var loanAccountNo = loan.LoanAccountNo;
                        var originalLoanAmount = loan.OriginalLoanAmount;
                        var narration = loan.WrittenOffReason;


                        var addDailyInterest = @"INSERT INTO DailyInterest(LoanAccountNo,
                                                                        TranAmount,
                                                                        InterestRatePerDay,
                                                                        interestPerDay,
                                                                        AmountPerDay,
                                                                        OriginalLoanAmount,
                                                                        Narration,
                                                                        TranDate) VALUES(@loanAccountNo,
                                                                                         @tranAmount,
                                                                                         @interestInPercentage
                                                                                         @interestPerDay,
                                                                                         @interestAmountPerDay,
                                                                                         @originalLoanAmount,
                                                                                         @narration,
                                                                                         @tranDate)";
                        //conn.Open();
                        conn.Execute(addDailyInterest, loans);

                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.ToString());

            }

        }

Thanks

Answer 1

The property tranAmount does not exist in your Loan class (Which you are passing into the insert query).

conn.Execute(addDailyInterest, loans);

This actually passes the list you just queried back to the insert query, resulting in the exception you are seeing.

You should create a new anonymous object matching the @ params like this:

conn.Execute(addDailyInterest, new { loanAccountNo, tranAmount , interestInPercentage, ...etc });