Updating 2nd table via a leftjoin

dynasoft

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem: Trying to work around issue given on https://datatables.net/forums/discussion/comment/152200/#Comment_152200. I have also 2 tables but only the 2nd table needs to have a row inserted with the 1st table's id. The 1st table is there just to show its data. User has to enter a value for rows from 1st table and 2nd table gets a field set with the 1st table's row id. Issue I have is the insert never happens. PostCreate line never gets triggered either. Here's my code:
`using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
{
editor = new Editor(db, "Csp", "Csp.id").Model<myDBModel.Csp>("Csp");
editor.Field(new Field("Csp.id")
.Set(false)
);

                editor.Field(new Field("Csp.CustomerID")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                    .Set(false)
                );
                editor.Field(new Field("Csp.Name")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
                    .Set(false)
                );
                editor.Field(new Field("Csp.Description")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
                    .Set(false)
                );
                editor.Field(new Field("CspRelations.ParamType")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                    .Set(false)
                );

                editor.Field(new Field("Dc.DistributorID")
                    .SetValue(lngDistIdx)
                );
                editor.Field(new Field("Dc.CustomerID")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                    .SetValue(lngCustIdx)
                );
                editor.Field(new Field("Dc.CoMmsiD")
                    .SetValue(lngSubPackIdx)
                );
                editor.Field(new Field("Dc.CoMmsComm")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                    .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                );

                editor.LeftJoin("Dc", "Csp.id", "=", "Dc.CoMmsiD");
                editor.LeftJoin("CspRelations", "Csp.id", "=", "CspRelations.SubPackID");

                Task t;
                editor.PostCreate += (sender, e) => t = Task.Run(() => UserFilesModel.Update(6, e.Values));

                editor.TryCatch(false);
                editor.Debug(true);
                editor.Process(formData);
            }`

Dc is 2nd table, Csp is 1st table

dynasoft

Editing an existing row works. I'm looking to create/insert row.

rf1234

The INSERT can't work this way because you do not want to insert anything into your first table. And there I have a question: How can you trigger a CREATE action if you always need to insert the id of your 1st table into your 2nd table? This cannot really work because it requires the entry in the 1st table to exist beforehand - which means you are editing and not creating it.

What you could do is make your 2nd table your 1st table and take it from there.

dynasoft

Thanks but if I swap the tables around then the DT shows those entries that are in table 2 only, not in table 1. Maybe I'm missing somthing.

editor = new Editor(db, "DC", "DC.id").Model<DCDBModel.DC>("DC");
...
editor.LeftJoin("CSP", "DC.CoMmsiD", "=", "CSP.id");

rf1234

Is it parent / child editing? I.e. are you selecting a parent record and then want to insert or edit the child record?

Then you might need a second Editor for this and even a "pseudo" Data Table for the child table. Pretty complex ... as described in this thread:
https://datatables.net/forums/discussion/comment/170337/#Comment_170337

Would work this way:
- You select a parent table record
- You click to create or edit the child record with a separate Editor (not the one for the parent table!)
- Done ...

I implemented this as well. For the sake of simplicity I only have one button for the child table. For that to work I already insert a record into the child table on "select" of the parent table. This is a dummy record only - and will later be automatically deleted if the record wasn't edited.

dynasoft

Thanks. I have used this elsewhere. Will modify the code.

dynasoft

.

dynasoft

Hi

Nearly managed to update all my code but I'm stuck on trying to delete a record from table DC with code below but it won't work.

`editor = new Editor(db, "DC", "DC.BIRVN").Model<DCDBModel.DC>("DC");
editor.Field(new Field("DC.id")
.Set(false)
);

editor.Field(new Field("DC.DistributorID")
.Set(true)
.SetValue(lngDistIdx)
);
editor.Field(new Field("DC.BIRVN")
.Xss(false)
.Set(true)
.SetValue(strCallGroup)
);
editor.Field(new Field("DC.BIRVC")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
.SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
);

editor.Field(new Field("Configuration.FieldData")
.GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
.Set(false)
);

editor.LeftJoin("Configuration", "DC.BIRVN", "=", "Configuration.FieldData");
editor.Where("Configuration.FieldName", "CallGroup%", "LIKE");
editor.Where("Configuration.FieldName", "CallGroupDefault", "!=");
editor.Where("Configuration.FieldName", "CallGroupsNumberOf", "!=");
//editor.TryCatch(false);
editor.Debug(true);
editor.Process(formData);`

I keep getting the reponse below:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":null,"fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":[{"Query":"DELETE FROM [DC] WHERE ([DC].[BIRVN] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"374","Type":null}]}],"cancelled":[]}

which is not right as the delete is done on configuration.id (where id = 374) but i want the delete done on Configuration.FieldData which matches DC.BIRVN

Thanks.

dynasoft

I was using a different key for loading the data hence the issue. Changed it to Configuration.FieldData and it now works.