Database updated but not editor

airmaster

I think I can explain this on the server side, but essentially, the Datatable isn't updated after an edit...at least not immediately. For instance, lets say the original value is 234, and I change it to 235 and save, the value in the data on the datatable shows 234. I then change it to 236 and save, after that, the datatable shows 235.

The server is C#, MVC, with SQL server. I decide to insert a breakpoint in the PreEdit and PostEdit inside of the controller as well as breakpoint in each. Inside the PreEdit, and expanding 'e' I see that the correct number has been submitted, 235.

editor.PreEdit += (sender,e) =>
{
...
}

editor.PostEdit += (sender,e) =>
{
...
}

I set a breakpoint right before the data is returned to the view (which is the Datatable), after resuming the code from PreEdit and it shows the old value of 234 after I scroll to the record I am editing (#1247 - I have quite a few rows. I would have expected it would have grabbed the latest value from the database, which is the one I see in the preedit.

I also view PostEdit and look at the form data (expanding 'sender'), and it is correct as well. Odd, though, in expanding 'e', it shows the first row of the Datatable, not the one being edited.

Another breakpoint, right before the result is returned back to the view shows that the value is still 235. The dataset being sent back, instead of the 1200+ rows, is just the first one.

Anyway, TLDR: The correct information is being sent, to the MVC controller, and the database is being updated, but the old data is being sent back to the view.

Thoughts?

allan

So data is being sent back? If you have a look at the network inspector in your browser for the Ajax request, what is the response JSON?

Normally from what you describe I would expect data to be an empty array. But your last paragraph suggests that data is coming back, but its the old data.

If that is happening... I'm not sure what is going on! Possibly that the write is taking place in a difference transaction from the read - but I don't know what that would happen - there is nothing in the Editor code that would cause that as far as I am aware.

Regards,
Allan

airmaster

Okay, so for the background, I am editing record #1247. I am looking at the ajax call to "join", which is a MVC C# controller call. What is returned are the 1200+ rows (after the PreEdit -> response return). Obviously, the results are truncated, and my edit is the last one.

After PostEdit, it returns again with record #1, plus of course, the additional fields related to inner joins.

airmaster

I changed the where statement in the controller only return the record I was working on #1247. Now the json response is a fairly small, so I can read it; it returns back the old value from the database, 234, after the PreEdit.

A second response, after PostEdit, returns one record as before, and that contains the new value, 235. Since I limited the total records that could be returned to 1, it returns #1247 (as opposed the record #1, as previously posted). Datatables is approprately updated with the new value.

So, is the problem that the second response, after PostEdit, should return the record I have just edited, and it normally isn't?

airmaster

I mocked up another case, using an example I sent you previously as a basis. You see the same behavior, two sets of responses. The first being the entire dataset, carrying the old data. The second response is the new data, and it only contains a single record. In this second case, it returns the proper record, and it gets appropriately updated.

This seems to validate my earlier theory. It is returning the wrong record on the second response.

airmaster

I think I have found the problem. Its my where statements. If I eliminate them, the correct row is returned.

.Where(p =>
{
     p.Where("tblWinLoss.CityID", "(SELECT ID AS CityID FROM qryCityStateCountry WHERE " + queryRegion + ")", "IN", false);
     p.OrWhere("tblWinLoss.Access1", userLANID);
     p.OrWhere("tblWinLoss.Access2", userLANID);
     p.OrWhere("tblWinLoss.Access3", userLANID);
});

airmaster

To be more specific, the .OrWhere is causing the problem. If I eliminate them, the problem correct single row is returned. I do need those OrWhere's though...not sure how to proceed.

Another interesting thing...when I eliminate the OrWhere, I see the correct value displayed in the Datatable, just for a moment, then it is sometimes, replaced with an old value.

airmaster

I PM'ed you an update to a previous example I sent. It has the same problem with a .OrWhere.

allan

Very interesting - so for my clarification: the server is responding with data in the JSON object after an edit - it just isn't responding with the expected row!?

Let's try this:

.Where(p =>
{
     p.WhereGroup(q =>
     {
         q.Where("tblWinLoss.CityID", "(SELECT ID AS CityID FROM qryCityStateCountry WHERE " + queryRegion + ")", "IN", false);
         q.OrWhere("tblWinLoss.Access1", userLANID);
         q.OrWhere("tblWinLoss.Access2", userLANID);
         q.OrWhere("tblWinLoss.Access3", userLANID);
      });
});

What I'm thinking is happening is that the resulting query before is SELECT ... WHERE id = ... AND (SELECT...) OR Access1 = ... etc. What we want is SELECT ... WHERE id = ... AND ((SELECT ...) OR Access1 = ...).

If you also add .Debug(true) just before the .Process(...) call, it will show you the SQL that is being built in the JSON return which can confirm or deny if this is the case.

Regards,
Allan

airmaster

I simplified it to a singe OrWhere. Here is my "easier" code sample

            using (var db = new Database(dbType, connectionString))
            {
                var editor = new Editor(db, "MyModel", "ID")
                    .Model<MyModel>("MyModel")
                    .Field(new Field("MyModel.ID").Set(false))
                    .Where(p =>
                    {
                        p.Where("MyModel.ID", "0", ">");
                        p.OrWhere("MyModel.name", "XX", "!=");
                    })
                    .Debug(true);

                var response = editor.Process(formData).Data();
                return Json(response, JsonRequestBehavior.AllowGet);
            }

After I edit and change a value in record #5 to 1239, there are three queries

SELECT  [ID] as 'ID'
FROM  [MyModel]
WHERE [ID] = @where_0 

where_0 = 5

UPDATE  [MyModel] SET  [name] = @name, [number] = @number
WHERE [ID] = @where_0 

name = 'Yan'
number = 1239
where_0 = 5

"SELECT  [ID] as 'ID', [MyModel].[ID] as 'MyModel.ID', [MyModel].[name] as 'MyModel.name', [MyModel].[number] as 'MyModel.number'
FROM  [MyModel]
WHERE [MyModel].[ID] > @where_0 OR [MyModel].[name] != @where_1 AND [ID] = @where_2 "

where_0 = 0
where_1 = 'XX'
where_2 = 5

The 'data' output is 'row_1'

airmaster

It seems that we should have the bracket between the two OR values, which I think is what you said.

airmaster

Just a thought... the only where field that is necessary for the last query is the record #, since that uniquely identifies the record. I don't know how that would affect anything else.

allan

Thanks - I think that validates what I thought it was.

It seems that we should have the bracket between the two OR values, which I think is what you said.

Yes - using the WhereGroup will group the conditions into parenthesis which will address this.

Just a thought... the only where field that is necessary for the last query is the record #, since that uniquely identifies the record.

That is a fair point. The only edge case I can think of would be if you have a condition that intentionally filters out data based on a where condition. For example a "stock_intake" table with a "done" column. You might mark it as done and want it removed from the display, but not actually a DELETE from the database table.

Allan

airmaster

Do you have an example on how to use WhereGroup in this context? Also, the tooltip for the method says it is depreciated.

airmaster

My guess is

using (var db = new Database(dbType, connectionString))
{
    var editor = new Editor(db, "MyModel", "ID")
        .Model<MyModel>("MyModel")
        .Field(new Field("MyModel.ID").Set(false))
        .Where(p =>
        {
            p.Where( q=>
            {
              q.Where("MyModel.ID", "0", ">");
              q.OrWhere("MyModel.name", "XX", "!=");
            });
        });
        .Debug(true);
 
    var response = editor.Process(formData).Data();
    return Json(response, JsonRequestBehavior.AllowGet);
}

allan

Your Where call would become:

        .Where(p =>
        {
            p.WhereGroup( q=>
            {
              q.Where("MyModel.ID", "0", ">");
              q.OrWhere("MyModel.name", "XX", "!=");
            });
        });

i.e. the p.Where becomes p.WhereGroup.

Allan

airmaster

Thanks. It seems that .WhereGroup and the second .Where both work about the same. I don't know what the difference is.

Anyway, that mostly fixes the problem. I get the correct record sent back. Sometimes it contains the updated field, and sometimes it contains the field before its updated. Very odd. Its like the issues with the latest data from SQL server.

allan

The WhereGroup method can be thought of as just added parenthesis around whatever is done inside it. In this case it means AND (MyModel.ID > 0 OR MyModel.name != 'XX') is added to the query.

Allan