Editor How To Update Values In a Table Not Referenced

puffster

I'm in my Evaluation period of Editor and trying to decide if it will work for me. I'm currently using DataTables and absolutely love it, so using the Editor extension seemed like a logical move. However, I've been going through the examples and I'm not sure if Editor will work for me.

Below is my stripped down pseudo-code to show what I'm doing...which is using a SQL Server database, in that database I have a stored procedure that reads from two tables to determine a max score, and stores the max score into a third table. "Table 2" is the table that will need to eventually be updated with new scores.

Stored Procedure 1:
Insert
Into Table3 (testID, Score) Values (
Select textID, max(score)
From (
Select testID, max(Score)
From Table1 //Official test scores, readonly
Group by testID
UNION
Select testID, max(Score)
From Table2 //user inputted test scores, table that needs to be updated from Editor
Group by TestID
) src
Group by testID
)

I have a second stored procedure that I use to push the data to my .net application, it's reading from Table 3, so my code never actually sees Table 2:

Stored Procedure 2:
Select personID, testID, Score
From Table3

I'm using Web Services in my .net application (an older Web Application project) to read in the data and send to DataTables javascript:
[WebMethod]
public List<scores> getLocationDetail2018(string schoolID)
{
List<scores> lld = new List<scores>();

            string mySQL = "exec Stored Procedure 2";
            string cf = System.Configuration.ConfigurationManager.ConnectionStrings["DM_DMC"].ConnectionString;

            SqlConnection connection = new SqlConnection(cf);
            SqlCommand command = new SqlCommand(mySQL, connection);

            connection.Open();

            SqlDataReader dr = command.ExecuteReader();

            int idx = 1; 
            while (dr.Read())
            {
                scores ld = new scores();

                ld.DT_RowId = "row_" + idx++.ToString();
                ld.personID = dr["personID"].ToString();
                ld.testID = dr["testID"].ToString();
                ld.testScore = dr["Score"].ToString();

                lld.Add(ld);
            }

            return lld;
}

And my javascript setup looks like this:

function getData() {
    $.ajax({
        type: "POST",
        contentType: "application/json; charset=UTF-8",
        url: "WebServices/ccr.asmx/getLocationDetail2018",
        data: JSON.stringify(parm),
        dataType: "json",
        success: function (results) {
            var det = results.d;

                editor = new $.fn.dataTable.Editor({
                    data: det,
                    table: '#ccr2018',
                    fields: [
                        {
                            label: 'personID',
                            name: 'personID',
                            type: 'readonly',
                            submit: false
                        },
                        {
                            label: "Test ID",
                            name: "testID",
                        },
                        {
                            label: "Test Score",
                            name: "testScore",
                        },
                    ]
                });

                var dt = $('#ccr2018').DataTable({
                    buttons: [
                       { extend: 'edit',   editor: editor },
                    ],
                    dom: "fBrti",
                    data: det,
                    select: true, 
                    columns: [
                        {
                            className: "detailRow text-center",
                            data: "personID",
                        },
                        {
                            className: "detailRow text-center",
                            data: "testID",
                        },
                        {
                            className: "detailRow text-center",
                            data: "testScore"
                        },
                    ]
                })
            }
        }
    })
}

So is it possible to use Editor to update Table 2? Before I started the evaluation I assumed (hah!) that I could just extend the function created by Editor that would be performing the update, but after reading various questions in the forums that does not sound like a possibility. Any suggestions?

rf1234

I could have asked: Why aren't you starting with something more straight forward? But anyway ...

Yes, you can use Editor and read table 3 with it without updating by setting the fields to "false". In addition you can update / insert etc. table 2. You can even do that dynamically on "validatedEdit" for example. Using that event handler you could do the entire field validation based on the user inputs, avoid updating the table 3 fields by dynamically setting them to false and then do the update of table 2 manually using Editor's or your own db handler.

I can only provide an example in PHP but I am sure you'll find the syntax for .NET in the docs as well.

->on('validatedEdit', function ( $editor, $id, $values ) {   
    //make sure table 3 fields don't get updated
    $editor->field('table3.field1')->set( false );
    $editor->field('table3.field2')->set( false );
    //update table 2 fields
    $editor->db()->raw()
       ->bind( ':field1', $values['table3']['field1'] )
       ->bind( ':field2', $values['table3']['field2'] )
       ->bind( ':id', $id ) //provided tables 3 and 2 have the same id ...
       ->exec( 'UPDATE table2   
                   SET field1 = :field1, 
                       field2 = :field2
                 WHERE id = :id' );
})

Alternatively you could left join table 2 with table 3, set the table 3 fields to "false" and use setFormatters to set the table 2 fields with the values of table 3.

puffster

Thank you! Just to answer your question about why didn't I do it more straightforward, as I mentioned this is stripped down pseudocode. "Table 3" is a cache table that has approximately 30 fields from a variety of other tables - "Table 1" and "Table 2" are just a couple of those tables. The report runs much quicker by pulling in data from the single cache table than if all those other tables had to do their queries and joins to produce the data. But I'm always open to suggestion on better ways to do it!! Again, thanks!!