Answers

• allan Posts: 63,578Questions: 1Answers: 10,483 Site admin

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020 edited February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  I PM'ed you an update to a previous example I sent. It has the same problem with a .OrWhere.
• allan Posts: 63,578Questions: 1Answers: 10,483 Site admin

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020 edited February 2020

  It seems that we should have the bracket between the two OR values, which I think is what you said.
• airmaster Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 63,578Questions: 1Answers: 10,483 Site admin

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020 edited February 2020

  Do you have an example on how to use WhereGroup in this context? Also, the tooltip for the method says it is depreciated.
• airmaster Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 63,578Questions: 1Answers: 10,483 Site admin

  February 2020

  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 Posts: 72Questions: 15Answers: 2

  February 2020

  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 Posts: 63,578Questions: 1Answers: 10,483 Site admin

  February 2020

  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