Don't need to insert on left join table

montoyam

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

allan

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

Allan

montoyam

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

allan

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

montoyam

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

montoyam

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":[]}

montoyam

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":[]}

allan

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

montoyam

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).

allan

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

montoyam

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)

allan

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

montoyam

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

montoyam

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).

allan

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

montoyam

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?

kthorngren

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

montoyam

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.

montoyam

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

kthorngren

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

montoyam

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)

montoyam

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>

montoyam

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

montoyam

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>

montoyam

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

The insert is working with the complex leftJoin.