mjoin - Object reference not set to an instance of an object

mjoin - Object reference not set to an instance of an object

montoyammontoyam Posts: 568Questions: 136Answers: 5
edited November 2020 in DataTables

I have the following:

    public class EquipmentOnLoanController : ApiController
    {
        [Route("api/EquipmentOnLoan")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult EquipmentOnLoan()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;
            var AsOfCookie = request.Cookies.Get("AsOfDate").Value;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "udf_EquipmentOnLoan('" + AsOfCookie + "') as EquipmentOnLoan", "LOANID")
                    .Model<EquipmentOnLoanModel>("EquipmentOnLoan")
                    .Field(new Field("EquipmentOnLoan.LoanStartDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("EquipmentOnLoan.LoanEndDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
/*
                    .MJoin(new MJoin("EquipmentOnLoan_LoanRateLink")
                        .Link("EquipmentOnLoan_LoanRateLink.LoanID", "EquipmentOnLoan.LOANID")
                        .Field(new Field("EquipmentRateID"))
                    )
*/
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

If I comment out the .Mjoin it is fine. With the MJoin i get the error: Object reference not set to an instance of an object

However, this works just fine on its own:

    public class EquipmentOnLoan_LoanRateLinkController : ApiController
    {
        [Route("api/EquipmentOnLoan_LoanRateLink")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult EquipmentOnLoan_LoanRateLink()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;
            var AsOfCookie = request.Cookies.Get("AsOfDate").Value;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "EquipmentOnLoan_LoanRateLink", "EquipmentRateID")
                    .Field(new Field("EquipmentOnLoan_LoanRateLink.LoanID")
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.Numeric())
                        .Options("vw_EquipmentOnLoan", "LOANID", "LOANID")
                    )
                    .Field(new Field("EquipmentOnLoan_LoanRateLink.RateID")
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.Numeric())
                        .Options("EquipmentOnLoan_Rates", "LoanRateID", "RateName")
                    )
                    .Field(new Field("EquipmentOnLoan_LoanRateLink.EffectiveDate")
                        .Validator(Validation.NotEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("EquipmentOnLoan_LoanRateLink.ExpireDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .LeftJoin("vw_EquipmentOnLoan", "vw_EquipmentOnLoan.LOANID", "=", "EquipmentOnLoan_LoanRateLink.LoanID")
                        .Field(new Field("vw_EquipmentOnLoan.LOANID"))
                    .LeftJoin("EquipmentOnLoan_Rates", "EquipmentOnLoan_Rates.LoanRateID", "=", "EquipmentOnLoan_LoanRateLink.RateID")
                        .Field(new Field("EquipmentOnLoan_Rates.RateName"))

                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

I am not seeing anything wrong with the mjoin statement.

Answers

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I may have figured it out. I am getting multiple records for the pirmary key LOANID. I need to see why my function is doing that

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    No, I found the true primary key and changed it, but still same error :(

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ok. ignore. now I had misspelled the new primary key name. oh my.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ok, this is getting frustrating...I had left the mJoin commented out, that's why i was not getting the error.

    So, yes, I am still getting that error when uncomment the MJoin.

    What does that error usually mean? I can't provide a link to the page since it is on our intranet. The error only happens when I uncomment the Mjoin.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I don't have an immediate answer for you I'm afraid - but:

    1. Can you add .TryCatch(false) just before the .Process(...) call please? That should let the server return more information about exactly where the error is happening (even if it is no longer return valid JSON). What does it return when that is in place?
    2. Can you send me your model as well please? That way I can try to reproduce this exact case when I'm in the office on Monday.

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I see what it was...

    I did not have to specify an alias on line 15 and I did not need to use the table name in the fields in lines 16 through 22.

    I thought mJoin was the same as LeftJoin where you needed to identify the table name.

This discussion has been closed.