options for select drop-down are blank

montoyam

I have three datatables on a page, two of which have left joins and have a pull-down field. One, the options are getting created correctly. The other one, no options array is created:

This one works:

                public class LineItemsController : ApiController
                {
                    [Route("api/LineItems")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult LineItems()
                    {
                        var request = HttpContext.Current.Request;
                        var settings = Properties.Settings.Default;

                        using (var db = new Database(settings.DbType, settings.DbConnection))
                        {
                            var response = new Editor(db, "InvoiceStructure_LineItems", "LineItemID")
                                .Model<LineItemsModel>("InvoiceStructure_LineItems")
                                .Model<LineItemCategoryModel>("InvoiceStructure_Categories")
                                .Field(new Field("InvoiceStructure_LineItems.LineItemID"))
                                .Field(new Field("InvoiceStructure_LineItems.LineItem")
                                    .Validator(Validation.NotEmpty())
                                )
                                .Field(new Field("InvoiceStructure_LineItems.CategoryID")
                                    .Validator(Validation.NotEmpty())
                                    .Validator(Validation.Numeric())
                                    .Options("InvoiceStructure_Categories","CategoryID","Category")
                                )
                                .Field(new Field("InvoiceStructure_LineItems.sortby")
                                    .Validator(Validation.NotEmpty())
                                )
                                .LeftJoin("InvoiceStructure_Categories", "InvoiceStructure_LineItems.CategoryID", "=", "InvoiceStructure_Categories.CategoryID")
                                .Where("InvoiceStructure_LineItems.CategoryID", request.Form["InvoiceStructure_LineItems.CategoryID"])
                                .Process(request)
                                .Data();

                            return Json(response);
                        }
                    }
                }

correctly returns:

{draw: null,…}
draw: null
data: [{DT_RowId: "row_12",…}, {DT_RowId: "row_13",…}, {DT_RowId: "row_14",…}, {DT_RowId: "row_15",…},…]
0: {DT_RowId: "row_12",…}
1: {DT_RowId: "row_13",…}
2: {DT_RowId: "row_14",…}
3: {DT_RowId: "row_15",…}
4: {DT_RowId: "row_16",…}
DT_RowId: "row_16"
InvoiceStructure_LineItems: {LineItemID: 16, LineItem: "GIS Direct", CategoryID: "3", sortby: "5"}
LineItemID: 16
LineItem: "GIS Direct"
CategoryID: "3"
sortby: "5"
InvoiceStructure_Categories: {CategoryID: 3, category: "Direct Charges (Variable Costs)", sortby: "3"}
CategoryID: 3
category: "Direct Charges (Variable Costs)"
sortby: "3"
5: {DT_RowId: "row_17",…}
recordsTotal: null
recordsFiltered: null
error: null
fieldErrors: []
id: null
meta: {}
options: {InvoiceStructure_LineItems.CategoryID: [{value: 2, label: "Direct Charges (Fixed Costs)"},…]}
InvoiceStructure_LineItems.CategoryID: [{value: 2, label: "Direct Charges (Fixed Costs)"},…]
0: {value: 2, label: "Direct Charges (Fixed Costs)"}
1: {value: 3, label: "Direct Charges (Variable Costs)"}
2: {value: 1, label: "Indirect Charges"}
3: {value: 4, label: "Project Charges"}
4: {value: 5, label: "Telecom - Phone lines"}
5: {value: 7, label: "Telecom - Purchase / Labor Charges"}
6: {value: 6, label: "Telecom - Recurring Charges"}
files: {}
upload: {id: null}
debug: null
cancelled: []

This one does not

                public class BillingSetup_UnitRatesController : ApiController
                {
                    [Route("api/BillingSetup_UnitRates")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult BillingSetup_UnitRates()

                    {
                        var request = HttpContext.Current.Request;
                        var settings = Properties.Settings.Default;

                        using (var db = new Database(settings.DbType, settings.DbConnection))
                        {
                            var response = new Editor(db, "BillingSetup_UnitRates", "UnitRateID")
                                .Model<BillingSetup_UnitRatesModel>("BillingSetup_UnitRates")
                                .Model<LineItemsModel>("InvoiceStructure_LineItems")
                                .Field(new Field("BillingSetup_UnitRates.effectivedate"))
                                .Field(new Field("BillingSetup_UnitRates.expiredate"))
                                .Field(new Field("BillingSetup_UnitRates.unitrate"))
                                .Field(new Field("BillingSetup_UnitRates.LineItemID")
                                    .Validator(Validation.NotEmpty())
                                    .Validator(Validation.Numeric())
                                    .Options("InvoiceStructure_LineItems","LineItemID","LineItem")
                                 )
                                .LeftJoin("InvoiceStructure_LineItems", "InvoiceStructure_LineItems.LineItemID", "=", "BillingSetup_UnitRates.LineItemID")
                                .Where("BillingSetup_UnitRates.LineItemID", request.Form["BillingSetup_UnitRates.LineItemID"])
                                .Process(request)
                                .Data();
                            return Json(response);
                        }
                    }
                }

returns no options

{draw: null, data: [{DT_RowId: "row_3",…}], recordsTotal: null, recordsFiltered: null, error: null,…}
draw: null
data: [{DT_RowId: "row_3",…}]
0: {DT_RowId: "row_3",…}
DT_RowId: "row_3"
BillingSetup_UnitRates: {LineItemID: "16", effectivedate: "7/1/2012 12:00:00 AM", expiredate: null, unitrate: "307.41"}
LineItemID: "16"
effectivedate: "7/1/2012 12:00:00 AM"
expiredate: null
unitrate: "307.41"
InvoiceStructure_LineItems: {LineItemID: 16, LineItem: "GIS Direct", CategoryID: "3", sortby: "5"}
LineItemID: 16
LineItem: "GIS Direct"
CategoryID: "3"
sortby: "5"
recordsTotal: null
recordsFiltered: null
error: null
fieldErrors: []
id: null
meta: {}
options: {}
files: {}
upload: {id: null}
debug: null
cancelled: []

colin

The only different I could see was the ordering in the LeftJoin, maybe switch

                .LeftJoin("InvoiceStructure_LineItems", "InvoiceStructure_LineItems.LineItemID", "=", "BillingSetup_UnitRates.LineItemID")
                .Where("BillingSetup_UnitRates.LineItemID", request.Form["BillingSetup_UnitRates.LineItemID"])

to be

                .LeftJoin("InvoiceStructure_LineItems", "BillingSetup_UnitRates.LineItemID", "=", "InvoiceStructure_LineItems.LineItemID")
                .Where("BillingSetup_UnitRates.LineItemID", request.Form["BillingSetup_UnitRates.LineItemID"])

and see if that helps.

Colin

montoyam

that didn't fix it.

allan

Are you possibly using different versions of the DataTables.dll?

Could you try using:

new Field("BillingSetup_UnitRates.LineItemID")
    .Options(new Options()
        .Table("InvoiceStructure_LineItems")
        .Value("LineItemID")
        .Label("LineItem")
    );

I'd recommend using the Options class if possible. That said, the old style string values should still work and I'll check that, but for now, it would be worth trying the Options class.

Allan

montoyam

no, that format doesn't work either. I actually tried that way first. In the datatable above this one I have an options list that works correctly. Not sure what is different about this second one.

montoyam

when I take away the 'select' option from the javascript, it shows the correct value. So the value is there, just no drop-down of options.

allan

Could you add .Debug(true) immediately before the .Process(...) call please? Then show me the JSON return from the server or use the debugger to upload a trace (send me the 6 character debug code).

Thanks,
Allan

montoyam

well, unfortunately the table name got changed. I changed the code in the controller to point to the new table name but now I am getting an Axax error, like my other post I still have open.

This MVC way is just not working for me at all!!!

montoyam

something very odd is going on. i changed the table name back to the way it was when i submitted my partially working code so i got past that ajax error but then it got hung up looking for an old field name (that has since changed in sql and in the controller/model, etc.) It was giving an error message that the field (the old field name that doesn't exist in the project code ANYWHERE) does not exist. So I change the field name back to the in SQL and in the controller/model and it was ok. It was like it was stuck somewhere wanting the old field name. I tried to clear cache but that wasn't it. Is data stored somewhere else?

montoyam

i went ahead and started a whole new VS project. I then copied and pasted the code from the old to the new. Everything worked perfectly. The code was right the whole time...it is just something with the project was not refreshing with the new controllers!!!

allan

Odd - but very happy to hear it is working now. Thanks for the update!

Allan