ambiguous column

montoyam

I added a left join and now am getting ambiguous column error. I thought I caught everywhere that the fields are being used to proceed them with the table name but still get the error. What am I missing?

/*
 * Model for DB table Categories
 * Created by http://editor.datatables.net/generator
 */
using System;

namespace Billing.Models
{


        public class LineItemCategoryModel 
        {
            public int CategoryID { get; set; }
            public string category { get; set; }
            public string sortby { get; set; }
        }

        public class LineItemsModel
        {
            public int LineItemID { get; set; }
            public string LineItem { get; set; }
            public string CategoryID { get; set; }
            public string sortby { get; set; }
        }

        public class UnitratesModel
        {
            public string LineItemID { get; set; }
            public string effectivedate { get; set; }
            public string expiredate { get; set; }
            public string unitrate { get; set; }
        }
    
}

            /*
             * Controller for DB table Categories
             * Created by http://editor.datatables.net/generator
             */
            
            using System;
            using System.Collections.Generic;
            using System.Net.Http.Formatting;
            using System.Web;
            
            using System.Web.Http;
            using DataTables;
            using Billing.Models;
            
            namespace Billing.Controllers
            {
                public class LineItemCategoriesController : ApiController
                {
                    [Route("api/LineItemCategories")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult Categories()
                    {
                        var request = HttpContext.Current.Request;
                        var settings = Properties.Settings.Default;
            
                        using (var db = new Database(settings.DbType, settings.DbConnection))
                        {
                            var response = new Editor(db, "Categories", "CategoryID")
                                .Model<LineItemCategoryModel>()
                                .Process(request)
                                .Data();

                            return Json(response);
                        }
                    }
                }

                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, "LineItems", "LineItemID")
                                .Model<LineItemsModel>()
                                .Field(new Field("LineItems.LineItemID"))
                                .Field(new Field("LineItems.LineItem")
                                    .Validator(Validation.NotEmpty())
                                )
                                .Field(new Field("LineItems.CategoryID")
                                    .Validator(Validation.NotEmpty())
                                    .Validator(Validation.Numeric())
                                    .Options(new Options()
                                        .Table("Categories")
                                        .Value("CategoryID")
                                        .Label("Category")
                                    )
                                )
                                .Field(new Field("LineItems.sortby")
                                    .Validator(Validation.NotEmpty())
                                )
                                .LeftJoin("Categories","LineItems.CategoryID","=","Categories.CategoryID")
                                .Where("LineItems.CategoryID", request.Form["LineItems.CategoryID"])
                                .Process(request)
                                .Data();

                            return Json(response);
                        }
                    }
                }

                public class UnitratesController : ApiController
                {
                    [Route("api/unitrates")]
                    [HttpGet]
                    [HttpPost]
                    public IHttpActionResult 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, "unitrates", "UnitRateID")
                                .Model<UnitratesModel>()
                                .Where("LineItemID", request.Form["LineItemID"])
                                .Process(request)
                                .Data();

                            return Json(response);
                        }
                    }
                }

            }

montoyam

here is the js

/*
 * Editor client script for DB table Categories
 * Created by http://editor.datatables.net/generator
 */

(function($){

    $(document).ready(function () {

    /**************************************************/
    var categoryEditor = new $.fn.dataTable.Editor( {
        ajax: '/api/LineItemCategories',
        table: '#Categories',
        fields: [
            {"label": "CategoryID:", "name": "CategoryID"},
            {"label": "Category:", "name": "category"},
            {"label": "SortBy:", "name": "sortby"}
        ]
    } );

    var categoryTable = $('#Categories').DataTable( {
        dom: 'Bfrtip',
        ajax: '/api/LineItemCategories',
        columns: [
            {"data": "CategoryID"},
            {"data": "category"},
            {"data": "sortby"}
        ],
        select: {style: 'single'},
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: categoryEditor },
            { extend: 'edit', editor: categoryEditor },
            { extend: 'remove', editor: categoryEditor }
        ]
    });

    /**************************************************/

    var lineItemEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: '/api/LineItems',
            data: function (d) {
                var selected = categoryTable.row({ selected: true });
                if (selected.any()) {
                    //d.LineItems.CategoryID = selected.data().CategoryID;
                }
            }
        },
            table: '#LineItems',
        fields: [
                {"label": "LineItemID:", "name": "LineItems.LineItemID" },
                { "label": "LineItem:", "name": "LineItems.LineItem"},
                { "label": "CategoryID:", "name": "LineItems.CategoryID"},
                { "label": "SortBy:", "name": "LineItems.sortby"}
            ]
        });

    var lineItemTable = $('#LineItems').DataTable({
        dom: 'Bfrtip',
        ajax: {
            url: '/api/LineItems',
            type: 'post',
            data: function (d) {
                var selected = categoryTable.row({ selected: true });
                if (selected.any()) {
                    //d.LineItems.CategoryID = selected.data().CategoryID;
                }
            }
        },
        columns: [
            { "data": "LineItems.LineItemID" },
            { "data": "LineItems.LineItem" },
            { "data": "LineItems.CategoryID"},
            { "data": "LineItems.sortby"}
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: lineItemEditor },
            { extend: 'edit', editor: lineItemEditor },
            { extend: 'remove', editor: lineItemEditor }
        ]
        });

    /**************************************************/

    var unitRateEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: '/api/unitrates',
            data: function (d) {
                var selected = lineItemTable.row({ selected: true });
                if (selected.any()) {
                    d.LineItemID = selected.data().LineItemID;
                }
            }
        },
        table: '#unitrates',
        fields: [
            { "label": "LineItemID:", "name": "LineItemID"},
            {"label": "EffectiveDate:", "name": "effectivedate"},
            {"label": "ExpireDate:", "name": "expiredate"},
            {"label": "UnitRate:", "name": "unitrate"}
        ]
    });

    var unitRateTable = $('#unitrates').DataTable({
        dom: 'Bfrtip',
        ajax: {
            url: '/api/unitrates',
            type: 'post',
            data: function (d) {
                var selected = lineItemTable.row({ selected: true });
                if (selected.any()) {
                    d.LineItemID = selected.data().LineItemID;
                }
            }
        },
        columns: [
            { "data": "LineItemID"},
            {"data": "effectivedate"},
            {"data": "expiredate"},
            {"data": "unitrate"}
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: unitRateEditor },
            { extend: 'edit', editor: unitRateEditor },
            { extend: 'remove', editor: unitRateEditor }
        ]
    });


/**************************************************/
    categoryTable.on('select', function (e) {
        lineItemTable.ajax.reload();

        lineItemEditor
            .field('LineItems.CategoryID')
            //.def(categoryTable.row({ selected: true }).data().CategoryID);
    });

    categoryTable.on('deselect', function () {
        lineItemTable.ajax.reload();
    });
/*
    lineItemEditor.on('submitSuccess', function () {
        categoryTable.ajax.reload();
    });
*/
    categoryEditor.on('submitSuccess', function () {
        lineItemTable.ajax.reload();
    });


/**************************************************/
    lineItemTable.on('select', function (e) {
        unitRateTable.ajax.reload();

        unitRateEditor
            .field('LineItemID')
            .def(lineItemTable.row({ selected: true }).data().LineItemID);
    });

    lineItemTable.on('deselect', function () {
        unitRateTable.ajax.reload();
    });
    /*
        lineItemEditor.on('submitSuccess', function () {
            categoryTable.ajax.reload();
        });
    */
    lineItemEditor.on('submitSuccess', function () {
        unitRateTable.ajax.reload();
    });
/**************************************************/

} );


}(jQuery));

montoyam

oh, by the way, I am getting the error that CategoryID and sortby are ambiguous.

montoyam

and it is for tableID: LineItems

montoyam

Sorry, I figured out the ambiguous issue. I needed to bring the model of the table I was joining:

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

                            return Json(response);
                        }

montoyam

but now I am having an issue getting the selected row data. I can't figure out the correct syntax. Error: 'CategoryID' of undefined

            data: function (d) {
                var selected = categoryTable.row({ selected: true });
                if (selected.any()) {
                    d.LineItems.CategoryID = selected.data().CategoryID;
                }
            }

I also tried:
d.LineItems.CategoryID = selected.data().Categories.CategoryID;
and
d.LineItems.CategoryID = selected.data().LineItems.CategoryID;

and none of these worked.

montoyam

I will open this as a new thread

allan

Hi,

Good to hear you got the SQL issue sorted out. For the issue on how to get the data -

 d.LineItemID = selected.data().LineItems.CategoryID;

I believe should do it.

But if it doesn't add console.log(selected.data()) into the code at that point and have a look at the data structure in the browser's console.

Allan

montoyam

turns out the solution for this second issue was:

d['LineItems.CategoryID'] = selected.data().CategoryID;