ambiguous column

ambiguous column

montoyammontoyam Posts: 568Questions: 136Answers: 5

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);
                        }
                    }
                }

            }

Answers

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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));
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    and it is for tableID: LineItems

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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);
                            }
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I will open this as a new thread

  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    turns out the solution for this second issue was:

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

This discussion has been closed.