Don't need to insert on left join table

Don't need to insert on left join table

montoyammontoyam Posts: 568Questions: 136Answers: 5

I am using a left join just to show a value on the parent table. when I do an insert on the parent table, however, editor is trying to insert on the left join table as well.

I tried .set(false) on all the fields on the left join but that didn't seem to help

when doing an insert where a leftJoin exists, is there a way for the edit/insert only to happen on the 'main' table (JobTitles in this case.

    public class JobTitlesController : ApiController
    {
        [Route("api/JobTitles")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult JobTitles()
        {
            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, "JobTitles", "JobTitleID")
                    .Model<JobTitlesModel>("JobTitles")
                    .LeftJoin("JobTitleRates", "JobTitles.JobTitleID", "="
                        , "JobTitleRates.JobTitleID and '" + AsOfCookie + "' Between JobTitleRates.EffectiveDate and isnull(JobTitleRates.ExpireDate,getdate())")
                        .Field(new Field("JobTitleRates.HourlyRate").Set(false))
                        .Field(new Field("JobTitleRates.JobTitleID").Set(false))
                        .Field(new Field("JobTitleRates.JobTitleRateID").Set(false))
                        .Field(new Field("JobTitleRates.EffectiveDate").Set(false))
                        .Field(new Field("JobTitleRates.ExpireDate").Set(false))
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Does your client-side Editor have any fields configured for the joined table - e.g. JobTitleRates.HourlyRate or something like that?

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    no, just the one field, jobTitle.

            var JobTitlesEditor = new $.fn.dataTable.Editor({
                ajax: 'api/JobTitles',
                table: '#JobTitles',
                fields: [
                    { label: "Job Title:", name: "JobTitles.JobTitle" }
                ]
            });
    
            JobTitlesTable = $('#JobTitles').DataTable({
                dom: 'B<"RatesDialog">frtip',
                ajax: 'api/JobTitles',
                columns: [
                    { data: "JobTitles.JobTitle", title: "JobTitle" },
                    { data: "JobTitleRates.HourlyRate", title: "Current Hourly Rate", render: $.fn.dataTable.render.number(',', '.', 2, '$') }
                ],
                select: { style: 'single' },
                autoWidth: false,
                responsive: true,
                buttons: {
                    buttons: [
                        { extend: 'create', editor: JobTitlesEditor, text: '<span class="fa fa-plus-circle fa-2x icon-purple"></span>', className: 'btn', titleAttr: 'Add Job Title' },
                        { extend: 'edit', editor: JobTitlesEditor, text: '<span class="fa fa-edit fa-2x icon-purple"></span>', className: 'btn', titleAttr: 'Edit Job Title' }
                    ],
                    dom: {
                        button: { tag: 'i', className: '' }
                    }
                }
            });
    
  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    How odd! I don't have an immediate answer for this I'm afraid. Could you change:

                    .Process(request)
                    .Data();
    

    to be:

                    .Process(request)
                    .Debug(true)
                    .Data();
    

    And then try inserting a row while your browser's Network inspector is open and then copy / paste the JSON response from the server so I can see what is happening please?

    Thanks,
    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    odd...debug seems to return null??

    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Value cannot be null.\r\nParameter name: key","fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":null,"cancelled":[]}
    
        public class JobTitlesController : ApiController
        {
            [Route("api/JobTitles")]
            [HttpGet]
            [HttpPost]
            public IHttpActionResult JobTitles()
            {
                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, "JobTitles", "JobTitleID")
                        .Model<JobTitlesModel>("JobTitles")
                        .LeftJoin("JobTitleRates", "JobTitles.JobTitleID", "="
                            , "JobTitleRates.JobTitleID and '" + AsOfCookie + "' Between JobTitleRates.EffectiveDate and isnull(JobTitleRates.ExpireDate,getdate())")
                            .Field(new Field("JobTitleRates.HourlyRate").Set(false))
                            .Field(new Field("JobTitleRates.JobTitleID").Set(false))
                            .Field(new Field("JobTitleRates.JobTitleRateID").Set(false))
                            .Field(new Field("JobTitleRates.EffectiveDate").Set(false))
                            .Field(new Field("JobTitleRates.ExpireDate").Set(false))
                        .Process(request)
                        .Debug(true)
                        .Data();
    
                    return Json(response);
                }
            }
        }
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ah, the .Debug(true) needs to be before the .Process(request)

    here you go...

    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Value cannot be null.\r\nParameter name: key","fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[{"Query":"DECLARE @T TABLE ( insert_id int ); INSERT INTO  [JobTitles]  ( [JobTitle] ) OUTPUT INSERTED.JobTitleID as insert_id INTO @T VALUES (  @JobTitle ); SELECT insert_id FROM @T","Bindings":[{"Name":"@JobTitle","Value":"test2","Type":null}]}],"cancelled":[]}
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    and here is the debug when I get rid of the left join table:

    [{"Query":"DECLARE @T TABLE ( insert_id int ); INSERT INTO  [JobTitles]  ( [JobTitle] ) OUTPUT INSERTED.JobTitleID as insert_id INTO @T VALUES (  @JobTitle ); SELECT insert_id FROM @T","Bindings": [{"Name":"@JobTitle","Value":"test3","Type":null}]},{"Query":"SELECT  [JobTitleID] as 'JobTitleID', [JobTitle] as 'JobTitle' FROM  [JobTitles] WHERE [JobTitleID] = @where_0 ","Bindings":[{"Name":"@where_0","Value":"25","Type":null}]}],"cancelled":[]}
    
  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Is JobTitleID an auto incrementing column?

    Also is JobTitleID in your model for the JobTitles table? If so add:

    .Field(new Field("JobTitles.JobTitleID").Set(false))
    

    just after your .Model(...) call.

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    yes, it is an auto increment id field. The insert record works fine when I get rid of the left join.

    However, I tried adding the line you mentioned above and I still got the error:

    Value cannot be null. Parameter name: key
    

    which, if it is talking about the key in the left joined table is JobTitleRateID, which I already have .Set(false).

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin
    Answer ✓

    Apologies - I missed before that you had a Join condition more complex than normal. Could you use:

    LeftJoin(
      "JobTitleRates",
      "JobTitles.JobTitleID = JobTitleRates.JobTitleID and '" + AsOfCookie + "' Between JobTitleRates.EffectiveDate and isnull(JobTitleRates.ExpireDate,getdate())"
    )
    

    The problem with it as it was before was that the "value" part would have been "bound" stopping it from working.

    Is that the only issue? I'm not certain! If it doesn't fix it - could you add .TryCatch(false) just before the .Debug(true) line and that should give us a bit more information when it errors out.

    Thanks,
    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited July 2021

    When I use the syntax you provided above I get the error

    C7036: There is no argument given that corresponds to the required parameter 'op' of Editor.LeftJoin(string,string,string,string)
    

    here is the information from the trycatch (using my original LeftJoin statement):

    +       $exception  {"Value cannot be null.\r\nParameter name: key"}    System.ArgumentNullException
    
    System.ArgumentNullException
      HResult=0x80004003
      Message=Value cannot be null.
    Parameter name: key
      Source=mscorlib
      StackTrace:
       at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
       at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
       at DataTables.Editor._InsertOrUpdate(Object id, Dictionary`2 values) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 2257
       at DataTables.Editor._Insert(Dictionary`2 values) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 1499
       at DataTables.Editor._Process(DtRequest data) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 1283
       at DataTables.Editor.Process(DtRequest data) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 877
       at DataTables.Editor.Process(NameValueCollection data, String culture) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 933
       at DataTables.Editor.Process(HttpRequest request, String culture) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/Editor.cs:line 959
       at Billing.Controllers.JobTitlesController.JobTitles() in V:\SBT\APPL\Application Development\dataTables\Billing\Billing\Controllers\SDPlusController.cs:line 318
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
    
    
  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    What version of the DataTables.dll are you using? The error:

    C7036: There is no argument given that corresponds to the required parameter 'op' of Editor.LeftJoin(string,string,string,string)

    Suggests you might be using one from before v2.0.0? This is the commit that added support for complex joins in DataTables.dll and it landed for v2.0.0.

    Regards,
    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ah, yes, I have not upgraded to 2.0 yet.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited July 2021

    I just did the upgrade to 2.0.4 and I am not getting an error on the .LeftJoin.

    However, none of the dataTables on my pages load anymore. I am getting the error:

    System.MissingMethodException
      HResult=0x80131513
      Message=Method not found: 'System.String[] System.String.Split(Char, System.StringSplitOptions)'.
      Source=DataTables-Editor-Server
      StackTrace:
       at DataTables.DatabaseUtil.Sqlserver.Query._Prepare(String sql)
       at DataTables.Query._Select()
       at DataTables.Query.Exec(String sql)
       at DataTables.Editor._Get(Object id, DtRequest http)
       at DataTables.Editor._Process(DtRequest data)
       at DataTables.Editor.Process(DtRequest data)
       at DataTables.Editor.Process(NameValueCollection data, String culture)
       at DataTables.Editor.Process(HttpRequest request, String culture)
       at Billing.Controllers.JobTitlesController.JobTitles() in V:\SBT\APPL\Application Development\dataTables\Billing\Billing\Controllers\SDPlusController.cs:line 323
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
       at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
    
    

    I brought in the correct dll, and have the correct .js and .css files (as far as I can see).

  • allanallan Posts: 61,433Questions: 1Answers: 10,049 Site admin

    Apologies - a string split which isn't available in all builds of .NET slipped into our last release!

    The latest dll with the fix can be downloaded here.

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited July 2021

    now i am getting the error:

    property 'rebuild' of undefined
    

    which i see has been reported before as an issue with SearchPane library?? what do i need to do to resolve this error?

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    Seems like you are referring to this thread. Make sure you are running SearchPanes 1.2.1 or later. I think its at 1.3.0 now. I would load the latest. IIRC that error occurs when loading the SearchPanes library but not enabling it.

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    uh oh, I have one main page that has all of my js and css includes. Then, I load all the dataTables into a div of that main page depending what they select from the top navigation. Not all the dataTables have searchPanes, but many do. And, I can have several dataTables on one page with some of them not having searchPanes but others on that same page would.

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    i uploaded my configuration data to debug.datatables.net. the code is: abaxox

    I used the Generator tool to create the js files after I upgraded to 2.0.4

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    I'm not a Datatables employee so don't have access to the debug trace. Does the error occur on every Datatable or just the one's without SearchPanes? It could be a different problem. Please provide more details or maybe a link to the page with the error.

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    i just realized I didn't grab the latest searchPanes and such, so I just updated to this:

            <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.6.0/jq-3.3.1/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.25/b-1.7.1/b-html5-1.7.1/date-1.1.0/sp-1.3.0/sl-1.3.3/datatables.min.js"></script>
    

    But now I am getting a different error:

    Uncaught TypeError: Cannot read property 'indexOf' of undefined
        at w.fn.init.w.fn.load (datatables.min.js:14)
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    I changed it back to my previous code and updated only searchPanes to 1.3.0. I am not getting the 'indexOf' error, but back to that 'rebuild' error.

    <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jqc-1.12.4/moment-2.18.1/jszip-2.5.0/pdfmake-0.1.36/dt-1.10.21/b-1.6.2/b-html5-1.6.2/r-2.2.5/sp-1.3.0/sl-1.3.1/datatables.min.js"></script>
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    interesting..i get the indexOf when I change from jqc-1.12.4 to jq-3.3.1

    1.12.4 works where 3.3.1 does not

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ah, I see, I still had a seperate reference to Searchpanes in my html

    <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/searchpanes/1.2.0/js/dataTables.searchPanes.min.js"></script>
    
  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ok. all is good in the world of DataTables now. :)

    The insert is working with the complex leftJoin.

Sign In or Register to comment.