Answers

• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  September 2019

  This is the JSON response from the server - formatted for readability:

  {
      "JSON": [{
          "Key": "options",
          "Value": [{
              "Key": "MarketSegmentSub",
              "Value": [{
                  "value": 1,
                  "label": "Tuna"
              }, {
                  "value": 3,
                  "label": "Perch"
              }, {
                  "value": 4,
                  "label": "Swordfish"
              }]
          }]
      }],
      "Response payload": {
          "EDITOR_CONFIG": {
              "text": "[{\"Key\":\"options\",\"Value\":[{\"Key\":\"MarketSegmentSub\",\"Value\":[{\"value\":1,\"label\":\"Tuna\"},{\"value\":3,\"label\":\"Perch\"},{\"value\":4,\"label\":\"Swordfish\"}]}]}]",
              "mode": "application/json"
          }
      }
  }
  

  But the dependent() method needs JSON that would look like this:

  {
    "options": {
      "tblWinLoss.MarketSegmentSubID": [
        { "label": ..., "value": ... },
        ...
      ]
    }
  }
  

  i.e. an "options" object which contains the field names (the full name - matching what was used for fields.name) with the options for each field.

  You could use a custom Ajax call and then modify the JSON that you are currently returning if you'd rather do it that way than making a change on the server-side, but the easiest way would be to modify the server-side return I'd suggest.

  Regards,
  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  September 2019

  Thanks for the response. Yes, I was thinking that the return value didn't look right. I had just followed the server side instructions as per the link I followed. I guess the instructions on that link are no longer valid.

  I will look and see if I can construct my own server-side link. I am not that familiar with how to construct JSON objects in C#.
• airmaster Posts: 72Questions: 15Answers: 2

  September 2019

  One irritating thing is that it doesn't seem to be easy to include a arbitrary string, like "tblWinLoss.MarketSegmentID" as a key. It seems like it will take only keys w/o quotes when creating them.
• airmaster Posts: 72Questions: 15Answers: 2

  September 2019 edited September 2019

  Okay, I modified the code to return the string, which I think is in the correct format:

  {"options":{"tblWinLoss.MarketSegmentSubID":[{"label":"Tuna","value":1},{"label":"Perch","value":3},{"label":"Swordfish","value":4}]}}
  

  C# code, using entity framework for the actual DB calls. At the end, I need to do a string replace to actually get it into the required form. Unfortunately, in the popup data editor, it doesn't make any changes to the MarketSegmentSub. Isn't this supposed to happen automatically?

          [HttpPost]
          public ActionResult getMarketSegmentSub_datatables()
          {
              var request = System.Web.HttpContext.Current.Request;
              var marketSegmentID = Convert.ToInt32(request.Form["rows[0][tblWinLoss][MarketSegmentID]"]);
              var marketSegmentSubList = _context.tblMarketSegmentSubs.Where(p => p.MarketSegmentID == marketSegmentID).ToList();
  
              dynamic dyamicMarketSegmentSubJSON = new List<dynamic>();
              for (int i = 0; i < marketSegmentSubList.Count(); i++)
              {
                  dyamicMarketSegmentSubJSON.Add(new { label = marketSegmentSubList[i].MarketSub , value = marketSegmentSubList[i].ID });
              }
  
              var json = new JavaScriptSerializer().Serialize(new
              {
                  options = new { MarketSegmentSubID = dyamicMarketSegmentSubJSON } 
              });
  
              return Json(json.Replace("MarketSegmentSubID", "tblWinLoss.MarketSegmentSubID"));
  
          }
  
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  September 2019

  Isn't this supposed to happen automatically?

  Yes absolutely. So:

  {
      "options": {
          "tblWinLoss.MarketSegmentSubID": [{
              "label": "Tuna",
              "value": 1
          }, {
              "label": "Perch",
              "value": 3
          }, {
              "label": "Swordfish",
              "value": 4
          }]
      }
  }
  

  is what is returned from the server in response to the /winloss/getMarketSegmentSub_datatables request? I can't recall if I've asked you before (sorry) could you give me a link to your page / app so I can take a look and see what is going wrong with that please?

  Thanks,
  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  September 2019 edited September 2019

  Yes, it is. The page is internal, though. Below is the return from Firefox debug network response.

  

  I mocked up a temporary table, so you have to ignore the TODELETE, but that is what is returned from the call.

  Obviously, I have the TODELETE inside my javascript as well, or it wouldn't work.
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019

  I threw a project together, maybe it will help determine what is wrong. Go to the /winloss url.

  I mailed you the link to the project. Hopefully it represents the problem.
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  October 2019

  Perfect - thank you. That screenshot shows that the return is a string rather than plain JSON.

  In the code above you seralise the object into a string (var json) and then use return Json(json...); which I think is likely the issue.

  You probably need to deserialise the JSON string...

  var j2 = JsonConvert.DeserializeObject<JObject>(json.Replace(...));
  
  return Json(j2);
  

  Or you could return your own response object:

      var response = Request.CreateResponse(HttpStatusCode.OK);
      response.Content = new StringContent(json, Encoding.UTF8, "application/json");
      return response;
  

  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019

  I tried both of your suggestions. Only the second one worked.

  1st Suggestion

  Note I did this in a class derived from Controller, not ApiController...maybe that could have made the difference.

  The first one is of some dynamic looking object type, which, when returned (as above), looks like this.

  

  The object before the return (in C# code) is:
  

  The only way I could get it to return an object in the right form is to use a real object on deserialize.

      public class OptionsObject
      {
          public MarketSubSegmentObject options { get; set; }
      }
  
  
      public class MarketSubSegmentObject
      {
          public List<ValueLabelObject> MarketSegmentSubID { get; set; }
      }
  
      public class ValueLabelObject
      {
          public int value { get; set; }
          public string label { get; set; }
      }
  

  and then in the controller

  OptionsObject j2 = JsonConvert.DeserializeObject<OptionsObject>(json);
  

  The problem is that the key is "MarketSegmentSubID", and not "tblWinLoss.MarketSegmentSubID". The latter is really an invalid key for the Json object, and I could not find a way to coerce it into the right form. Maybe I could use that [Deserialize] label on the object, found in the RestSharp package, but at this point, I wanted to move on to the second suggestion.

  2nd Suggestion
  On your second suggestion, I could not get it to work with and MVC controller, but had to implement a separate ApiController type, which isn't present in my application. It isn't my favorite way of doing this, hopefully will find a better solution.

  Regarding the code that I referred to at the beginning, it doesn't seem to work, as it doesn't return the json in the format you would expect. I had to roll my own to get it to the correct format, which you see in the code below.

  So, what was the end-state of my code? I am enclosing it below. I know that instead of the replace string, some people prefer adding a [JsonProperty (Name = "tblWinLoss.tblMarketSegmentSubID], but either way, that didn't get me to the object I wanted.

  Implementation of the ApiController

  namespace MyNameSpace.Controllers
  {
      public class MyAPIController : ApiController
      {
         [HttpPost]
         [HttpGet]
          public  HttpResponseMessage getMarketSegmentSub_datatables()
          {
              // I use entity framework for most of my database calls
              using (MySQLEntities _context = new MySQLEntities())
              {
                  var request = System.Web.HttpContext.Current.Request;
                  // Need to throw in a check for a blank value for the MarketSegmentID
                  var marketSegmentID =   Convert.ToInt32(request.Form["values[tblWinLoss.MarketSegmentID]"]);
                  var marketSegmentSubList = _context.tblMarketSegmentSubs.Where(p => p.MarketSegmentID == marketSegmentID).ToList();
  
                  dynamic dyamicMarketSegmentSubJSON = new List<dynamic>();
                  for (int i = 0; i < marketSegmentSubList.Count(); i++)
                  {
                      dyamicMarketSegmentSubJSON.Add(new { label = marketSegmentSubList[i].MarketSub, value = marketSegmentSubList[i].ID });
                  }
                  var json = new JavaScriptSerializer().Serialize(new
                  {
                      options = new { MarketSegmentSubID = dyamicMarketSegmentSubJSON }
                  });
  
                  json = json.Replace("MarketSegmentSubID", "tblWinLoss.MarketSegmentSubID");
  
                  var response = Request.CreateResponse(HttpStatusCode.OK);
                  response.Content = new StringContent(json, Encoding.UTF8, "application/json");
                  return response;
              }
          }
      }
  }
  

  and then to call it

  editor.dependent('tblWinLoss.MarketSegmentID', '/api/MyAPI/getMarketSegmentSub_datatables');
  

  I have a mixed route config, due to use of both Controller (MVC) and ApiController, so you can follow these directions to get both working in the same code.

  If I go back to the beginning, where I looked at the example you provided, and I showed in the link, and for the #2 example here, they both seem to require the ApiController, and don't work with the MVC controller. So, the example is not .NET MVC, but .NET Web API. I went back to the linked example, and I sorta got it working, but it does respond with the Json object that you would expect. Of course, it still has problems, due to the inability to have a "." in the key, so I prefer my method.
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  October 2019

  Wow - thank you for your research into this and posting back your findings. The key issue I guess is that . is not valid in a C# variable name, which really messes with our use of dot delimited object notation in a string to match field names. It might be that we need to look into allowing nested objects such as:

  {
      "options": {
          "tblWinLoss": {
              "MarketSegmentSubID": [{
                  "label": "Tuna",
                  "value": 1
              }, {
                  "label": "Perch",
                  "value": 3
              }, {
                  "label": "Swordfish",
                  "value": 4
              }]
          }
      }
  }
  

  I didn't realise it would be so hard to put a . into a JSON parameter name in C#!

  There is one other option thinking about it which is to rename the data sent to the client from tblWinLoss.MarketSegmentID to be just MarketSegmentID (assuming there is no conflicting name from other tables). That can be done with the optional second parameter for Field:

  .Field(new Field("tblWinLoss.MarketSegmentID", "MarketSegmentID")
  

  Then on the client-side just refer to the field / column as MarketSegmentID. This does preclude using Model() on the server-side, which you might or might not want to do - each field needs to be listed as a new Field() rather than with a model.

  That way there wouldn't be any need to mess around with . in property names.

  Regards,
  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019

  I think the problem with the optional second parameter is that I can no longer access the field name for the dropdown, that being tblMarketSegment.Market.

  I don't really see an option right now, other than relying on the ApiController. Its interesting that, using pretty much the same C# code, that each controller will output different json looking objects. I know that ApiController is more designed for such a thing, but really?

  Anyway, I think for the sake of being compliant with the json key constraints, some type of aliasing will be required. You can't get away from the need to refer to object properties with the "." notation.

  For the future, do you think it would be possible to modify the dependent field?

  editor.dependent('tblWinLoss.MarketSegmentID', '/api/CompetitiveAPI/getMarketSegmentSub_datatablesY');
  

  to allow aliasing?

  editor.dependent('tblWinLoss.MarketSegmentID', 'MYALIAS', '/api/CompetitiveAPI/getMarketSegmentSub_datatablesY');
  

  I guess in your dll code, it would somehow then massage the information into the correct fields?

  On another note, it seems to update correctly, but the updated row disappears from the table. I have to reload the page to get it to show up. I don't know if this is a bug, or I need to make some type of additional call to refresh that row?
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  October 2019

  If you alias tblMarketSegment.Market as well - e.g.:

  .Field( new Field("tblMarketSegment.Market", "Market") )
  

  then you should be able to access it as simply Market. So yes, if you start aliasing properties, you'd probably need to alias all of them.

  do you think it would be possible to modify the dependent field?

  Its a nice idea, but the dependent method will actually submit information about all fields in the form and it also allows all fields to be controlled in the JSON return. So all fields would need to be aliased, which basically brings us back to the alias suggestion above.

  updated row disappears from the table. I have to reload the page to get it to show up.

  That suggests that the server is not returning data about the updated row. It should return the data for the row.

  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019

  I hate to necro this, but finally getting around to replacing the ApiController. Am I right when you say that I would have to abandon using the model on tblWinLoss and have to alias all of the properties from that? I have quite a few of them.

  I can't seem to bind the dropdown for MarketSegment. I wanted to ask before I really dove into this and see if I can come up with a working example of what you suggested...and if I even want to do that.

  I assume that a different solution for this isn't very high on the priority list?
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  October 2019

  Am I right when you say that I would have to abandon using the model on tblWinLoss and have to alias all of the properties from that?

  You don't have to, but it you want to be able to access them using a non-nested property (e.g. Market rather than tblMarketSegment.Market) then yes, you'd need to nest them.

  I assume that a different solution for this isn't very high on the priority list?

  Ideally you'd be able to return something like:

  {
      "options": {
          "tblWinLoss.MarketSegmentSubID": [{
              "label": "Tuna",
              "value": 1
          }, {
              "label": "Perch",
              "value": 3
          }, {
              "label": "Swordfish",
              "value": 4
          }]
      }
  }
  

  That would allow the JSON return to work with Editor as is. It looks like it is possible using JsonProperty.

  I'll try experimenting a little locally.

  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019 edited October 2019

  Allan,

  Looking at that link, it gave me some further direction. One reason that I am doing this is that I am considering making a server-side search instead. Things get really messy there with the ".".

  Anyway, here is what I am working on now, with some good success so far, but I am not willing quite yet to declare victory.

          public class Options
          {
              [JsonProperty("tblMarketSegment.ID")]
              public List<JoinMarketSegment> MarketSegment_ID { get; set; }
          }
  
  

  List<JoinMarketSegment> tblMarketSegment = ......  // call to Entity Framework
  var options = new Options { MarketSegment_ID = tblMarketSegment};
  

  So, you would think that this would work with the standard JsonResult conversion, so I did this

   JsonResult { Data = new { draw = model.draw, recordsTotal = 1, recordsFiltered = 1, data = res, options = options } };
  

  Nope, still had the underscore in MarketSegment_ID.

  Then I found someone who wrote their own JsonResult action.

  So I did this:

              JsonNetResult jsonNetResult = new JsonNetResult();
              jsonNetResult.Formatting = Formatting.Indented;
              jsonNetResult.Data = new { draw = model.draw, recordsTotal = 1, recordsFiltered = 1, data = res, options = options };
  
             return JsonNetResult;
  

  And it seems to work.

  I believe that this is because JsonProperty is part of the (Newtonsoft) Json package (which JsonNetResult utilizes), which will then utilize JsonProperty, but JsonResult is part of MVC, which uses the JavascriptSerializer class, which was discussed here and therefore will never use JsonProperty.

  In case his site ever goes down, I am including his custom ActionResult derived class here:

          public class JsonNetResult : ActionResult
          {
              public Encoding ContentEncoding { get; set; }
              public string ContentType { get; set; }
              public object Data { get; set; }
              public JsonSerializerSettings SerializerSettings { get; set; }
              public Formatting Formatting { get; set; }
  
              public JsonNetResult()
              {
                  SerializerSettings = new JsonSerializerSettings();
              }
              public override void ExecuteResult(ControllerContext context)
              {
                  if (context == null)
                      throw new ArgumentNullException("context");
                  HttpResponseBase response = context.HttpContext.Response;
                  response.ContentType = !string.IsNullOrEmpty(ContentType)
                    ? ContentType
                    : "application/json";
  
                  if (ContentEncoding != null)
                      response.ContentEncoding = ContentEncoding;
                  if (Data != null)
                  {
                      JsonTextWriter writer = new JsonTextWriter(response.Output) { Formatting = Formatting };
                      JsonSerializer serializer = JsonSerializer.Create(SerializerSettings);
                      serializer.Serialize(writer, Data);
                      writer.Flush();
                  }
              }
          }
  
  

  I will update you later if I can indeed declare victory.
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  October 2019

  Good grief. I'm genuinely surprised it is so difficult in C#, but I'm also very much appreciative of your posting your findings here for others - thank you!

  Allan
• airmaster Posts: 72Questions: 15Answers: 2

  October 2019 edited October 2019

  Okay, I think we have it. This addresses the original question, which was how do I get cascading dropdown lists working, once the option for the first list is picked.

  Models

      public class MarketSubSegmentObject
      {
          [JsonProperty("tblWinLoss.MarketSegmentSubID")]
          public List<ValueLabelObject> MarketSegmentSubID { get; set; }
      }
      public class ValueLabelObject
      {
          public int value { get; set; }
          public string label { get; set; }
      }
  

  Controller method to return Json

          [HttpPost]
          public ActionResult getMarketSegmentSub_datatables()
          {
              var request = System.Web.HttpContext.Current.Request;
              string marketSegmentString = request.Form["values[tblWinLoss.MarketSegmentID]"];
              List<ValueLabelObject> marketSegmentSubList;
              if (marketSegmentString != "")
              {
                  int marketSegmentID = Convert.ToInt32(marketSegmentString);
                  marketSegmentSubList = (from s in _context.tblMarketSegmentSubs
                                          where s.MarketSegmentID == marketSegmentID
                                          select new ValueLabelObject
                                          {
                                              value = s.ID,
                                              label = s.MarketSub
                                          }).ToList();                                       
              }
              else
              {
                  marketSegmentSubList = new List<ValueLabelObject>();
              }
              MarketSubSegmentObject options = new MarketSubSegmentObject { MarketSegmentSubID = marketSegmentSubList };
              return new JsonNetResult { Data = new { options = options } };
          }
  
  

  And to be complete, the function which calls it, where is the controller.

                  editor.dependent('tblWinLoss.MarketSegmentID', '/winloss/getMarketSegmentSub_datatables');
  

  So, I just need to include a bit more error checking, and then we are good. The Json has the nicely placed tag of "tblWinLoss.MarketSegmentSubID" instead of MarketSegmentSubID, which means that JsonProperty did its job.

  Overall, this is a pretty nice solution. Its only dependent on one external package, Newtonsoft Json, which is extremely popular, so you aren't relying on something obscure. You just need the relatively simple JsonNetResult object (source code above), and the JsonProperty tag.

  My suggestion is that, given this, there is no reason to make any changes to the Datatables platform, unless there are other underlying reasons. For a pure MVC solution, it might be a good idea to include this as an option here.

  Now that this is solved, I may go back to the example above this one, and continue working on a server-side solution.
• allan Posts: 63,540Questions: 1Answers: 10,476 Site admin

  November 2019

  That's excellent - thank you!