Cannot Delete Records

samevedzi

Hi,

I have some blocks of code in MVC C# whose structures are similar. I am unable to delete records (it will delete from the table, but will show up again after a refresh of the page).

I have tried to follow the suggestions in the post in the following link but cannot pin point why the second is not deleting:
https://datatables.net/forums/discussion/56002/editor-php-working-for-data-loading-action-remove-is-returning-empty-json

The script and debugger response for one of them are as follows:

Script:

                using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
                {
                    var editor = new Editor(db, "Employee.PaymentInstitutionBranch", "ID")
                        .Model<PaymentInstitutionBranch>("Employee.PaymentInstitutionBranch")
                        .Model<PaymentMode>("Employee.PaymentMode")
                        .LeftJoin("Employee.PaymentMode", "PaymentMode.ID", "=", "PaymentInstitutionBranch.PaymentModeID")
                        .Field(new Field("Employee.PaymentInstitutionBranch.PaymentModeID")
                            .Options("Employee.PaymentMode", "ID", "Description")
                            .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                        )
                        .Model<PaymentInstitution>("Employee.PaymentInstitution")
                        .LeftJoin("Employee.PaymentInstitution", "PaymentInstitution.ID", "=", "PaymentInstitutionBranch.PaymentInstitutionID")
                        .Field(new Field("Employee.PaymentInstitutionBranch.PaymentInstitutionID")
                            .Options("Employee.PaymentInstitution", "ID", "Description")
                            .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                        )
                        .Model<PaymentBranch>("Employee.PaymentBranch")
                        .LeftJoin("Employee.PaymentBranch", "PaymentBranch.ID", "=", "PaymentInstitutionBranch.PaymentBranchID")
                        .Field(new Field("Employee.PaymentInstitutionBranch.PaymentBranchID")
                            .Options("Employee.PaymentBranch", "ID", "Description")
                            .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                        )
                        .Where("Employee.PaymentInstitutionBranch.DatabaseID", Database_ID, "=")
                        .Field(new Field("Employee.PaymentInstitutionBranch.CreatedBy").Set(Field.SetType.Create).SetValue(User_ID))
                        .Field(new Field("Employee.PaymentInstitutionBranch.DateCreated").Set(Field.SetType.Create).SetValue(DateTime.Now))
                        .Field(new Field("Employee.PaymentInstitutionBranch.ModifiedBy").Set(Field.SetType.Both).SetValue(User_ID))
                        .Field(new Field("Employee.PaymentInstitutionBranch.DateModified").Set(Field.SetType.Both).SetValue(DateTime.Now));

                    editor.Debug(true);
                    DtResponse response = editor.Process(formData).Data();
                    JsonResult json = Json(response, JsonRequestBehavior.AllowGet);
                    json.MaxJsonLength = int.MaxValue;

                    return json;
                }

Debugger Response:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[],"cancelled":[]}

Kindly assist.

Thanks in advance,
---Stephen

allan

Hi,

Is the JSON you show there the response from the server when you trigger the submit for the the delete action?

What you describe will happen if the SQL DELETE command doesn't happen or if there is an error, but for whatever reason the Editor libraries aren't reporting an error. There is no SQL information in the response there, which is surprising if that is the response to the delete submission.

Thanks,
Allan

samevedzi

Hi Allan,

Thanks for the quick response.

Yes, the JSON I showed is the response from the server when I trigger the submit for the the delete action.

Thanks,
Stephen

allan

Hi Stephen,

Are you able to give me a link to the page so I can take a look at the client-side code please? You can PM it to me by clicking my forum user name and then Send message.

Thanks,
Allan

samevedzi

Hi Allan,

I sent you the PM as requested.

Did you receive it?

Thanks,
---Stephen

allan

Hi Stephen,

Yes indeed - I've just been reading over it. I think we are going to need to do a little debugging on the server-side here I'm afraid. What .NET version are you using here? (e.g. Core 2.1, Framework 4.7, etc)? I'll build a special version of the dll for your platform and we'll get to the bottom of it that way.

I have a suspicion it will be something to do with the use of the schema name as well as the table name.

Allan

samevedzi

Hi Allan,

Thanks once again for the response.

I am using .NET Framework 4.5.2

Regards,
Stephen

allan

Super - thanks! Could you try this dll: http://datatables.net/dev/Editor-debug.zip ? It will chuck some information into the JSON return for the delete action that will hopefully let us track what is going on.

Allan

samevedzi

Hi Allan,

Thanks once again.

Below is the JSON response returned:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{},"searchBuilder":{"options":{}},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":["Preping to delete rows: 1","Remove table: Employee.PaymentInstitutionBranch  pkey: Employee.PaymentInstitutionBranch.ID","No matches","No matches","No matches","No matches","No matches","No matches","No matches","No matches","No matches","No matches","No matches","No matches","Delete count is: 11",{"Query":"DELETE FROM  [Employee].[PaymentInstitutionBranch] WHERE ([Employee].[PaymentInstitutionBranch].[ID] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"27","Type":null}]}],"cancelled":[]}

Thanks,
Stephen

allan

Hi Stephen,

Thanks! That one looks like it might actually have completed successfully. Did it? The debug suggests that it deleted row ID 27 from Employee].[PaymentInstitutionBranch]Employee].[PaymentInstitutionBranch].

The original trace didn't include anything in the debug, so is it perhaps now working?

Allan

samevedzi

Hi Allan,

Thanks for the response and help.

I can confirm that the deletion is working fine now.

Thanks once again,
---Stephen

allan

Awesome. Thanks for letting me know.

What version of the DLL where you using before? That one isn't all that different from the current 2.0.10 release.

Allan

samevedzi

Hi,

Apologies for the delay in responding. I have been off a bit.

I think it was 1.9, not too sure.

How do I check this?

Regards,
---Stephen

allan

Are you using Visual Studio? Select the "DataTables.dll" in the "Resources" dropdown in the project inspector and it should show the version.

Allan

samevedzi

Hi,

I had version 2.0.5.0.

Thanks,
Stephen

allan

Ah! I suspect that 2.0.6 fixed the issue originally reported here with this point:

• Fix: Better handling of database table aliases

Great to know it is working now.

Allan