Answers

• kthorngren Posts: 21,336Questions: 26Answers: 4,951

  January 2017 Answer ✓

  I think in your json response the object should be data not d to match with the column definitions.

  {"data":"[{\"col1\":\"12345\",\"col2\":\"123\",\"col3\":\"123\",\"col4\":\"100\",\"col5\":\"test_1]"}
  

  The error is indicating the returned data does not match what datatables is configured for. The URL in the error message will help with further troubleshooting.

  Kevin
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017 Answer ✓

  more than that, your d is a string, not an array. Can you return just that array without it being turned into a string?

  The Editor .NET libraries should work that way when used with Web API.

  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Thanks for the quick response. Appreciate it!

  Please find below the code used by the web service. Am not sure if you want me to return an array from the service or write some javascript to convert string to array.

  Thanks!

  [WebMethod]
  [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
  public string GetdataFunction()
  {

          string Get = string.Empty;
  
  
          try
          {
              using (var db = new DBLookupEntities(Globals.Instance.getdbconnstring()))
              {
                  var me = (from m in db.table1
                            join d in db.table2 on m.table1id equals d.table2id
                            select new {
  
                                col1 = m.col1,
                                col2= m.col2,
                                col3= m.col3,
                                col4= m.col4,
                                col5= d.col5                                 
  
                            }).ToList();
  
                  if (me != null)
  
                      Get = JsonConvert.SerializeObject(me);
              }
          }
          catch (Exception ex)
          {
              var error = ex.ToString();
  
          }
          return Get;
      }
  
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017 Answer ✓

  There is actually another option (sorry I didn't mention it before - was using a crappy keyboard and trying to do as little typing as possible (hopefully without being rude!). Have it return the format as shown above and we'll have DataTables parse it and use it through the ajax.dataSrc option:

  dataSrc: function ( json ) {
    return JSON.parse( json.d );
  }
  

  should do it.

  Basically the ajax.dataSrc option gives you the option of processing the data returned from the server before DataTables uses it. In this case we can take the array string, make it a real array and return that for DataTables to use.

  I've never understood why .NET likes to make the JSON data a string in an object with a d property...

  Regards,
  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Hi Allan,

  I made the changes as you suggested. Please find the code below. Am still getting the same issue. I am getting the error message and two empty rows in the web page.

  var table = $('#Grid-01').DataTable({
  lengthChange: false,
  order: [],
  ajax: {
  url: "URL to DB service",
  contentType: "application/json; charset=utf-8",
  dataType: "json",
  type: "GET",
  dataSrc: function ( json ) {
  return JSON.parse( json.d );
  }
  },
  columns: [

                  { "d": "col1" },
                  { "d": " col2" },
                  { "d": " col3" },
                  { "d": " col4" },
                  { "d": " col5" }
      ]
      });
  
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017 Answer ✓

  There is no columns.d option - it should be columns.data.

  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  It worked like a charm!!! Thanks for the quick response.

  Datatables and Editor looks awesome. I was trying to get some examples of Datatables and editor using asp.net. I was not able to find them.

  Can you share some links to understand how editor and datatables work with .net? I am looking for examples of some GET and POST methods for creating new records, deleting and editing the existing records in datatable.
• tangerine Posts: 3,365Questions: 39Answers: 395

  February 2017 Answer ✓

  Try the manual.
  https://editor.datatables.net/manual/net/index#.NET-libraries-manual
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Sure. I will check it out. Thanks!
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017 Answer ✓

  You can also download the Editor .NET package which contains all of the examples on the site, for .NET.

  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Hi,

  I am facing issue while trying to POST back to the web service. Please find below the error message.

  {"Message":"Invalid JSON primitive: action.","StackTrace":" at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializePrimitiveObject()\r\n at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeInternal(Int32 depth)\r\n at System.Web.Script.Serialization.JavaScriptObjectDeserializer.BasicDeserialize(String input, Int32 depthLimit, JavaScriptSerializer serializer)\r\n at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize(JavaScriptSerializer serializer, String input, Type type, Int32 depthLimit)\r\n at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)\r\n at System.Web.Script.Services.RestHandler.GetRawParamsFromPostRequest(HttpContext context, JavaScriptSerializer serializer)\r\n at System.Web.Script.Services.RestHandler.GetRawParams(WebServiceMethodData methodData, HttpContext context)\r\n at System.Web.Script.Services.RestHandler.ExecuteWebServiceCall(HttpContext context, WebServiceMethodData methodData)","ExceptionType":"System.ArgumentException"}

  I tried looking into the .NET examples but am not able to figure out. I normally have two separate functions for Get and Post. Not sure how to call the post function separately in editors.

  Thanks!
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017

  Not sure how to call the post function separately in editors.

  Set the DataTables ajax option to point at your GET path, and Editor ajax option to point at the POST path.

  Can you show us your controller code please? I've not seen an error like that before. Perhaps it is expecting JSON to be submitted rather than HTTP body parameters?

  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Hi,

  I created separate webservice call in the editor ajax option. Corresponding webservices are getting called but am not sure how to pass the value of the selected rows. I have 5 rows and I selected two rows from them. Each row has an Id column that is the primary key. I need to pass each of the ID's to the webservice and delete those two rows from the table. I am not sure how to get the ID values of the selected column from the data table.
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  [WebMethod]
  [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
  public void DeleteData(int id)
  {

          try
          {
              sql delete statement to delete from db using the id as input.
          }
  

  I am trying to pass the id value to this webservice when i select a row and hit delete button. I was trying to do row by row first but eventually i would like to delete multiple rows at once.
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  new $.fn.dataTable.Editor({
  ajax: {
  remove: {
  type: "POST",
  url: "../service.asmx/DeleteData"
  }
  },

  This is the ajax call to webservice. I tried using the "data:" option but am not sure how to pass the id value of the selected row and when I select multiple rows, should i pass the id value as a object to the webservice and write code in webservice to read the object??
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017 edited February 2017

  If you are using Editor this document describes the parameter that are being submitted to the server.

  If you want to submit them as JSON rather than HTTP parameters, you can do so using the ajax.data option (last example on that page).

  Allan

  edit Corrected link
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017 edited February 2017

  Hi,

  I tried the option that you suggested. Please find below the code i use. I am not able to figure out how to pass the value.

  remove: {
  type: "POST",
  url: "web service call",
  dataType: 'json',
  data: function (d) {
  d.Id = JSON.stringify($("#Id").val());
  }
  }

  Id - is the name of a column in datatable( I am not showing that value in the UI though). When I hard code with a number like shown below, the parameter gets passed to the web service.

  d.Id = JSON.stringify(8);

  Now the web service looks for Id value with 8 in the table and deletes that row. I am not sure how to pass the selected rows from the UI as parameter.

  Also after delete am returning "Deleted" as return value from the web service call but after the delete operation, i am not getting any response and the error message redirects me to the below page.

  https://datatables.net/manual/tech-notes/12

  I know am doing something wrong here. Can you please help?

  thanks!
• tangerine Posts: 3,365Questions: 39Answers: 395

  February 2017

  You need to follow the diagnostic steps in the tech-notes link and report your findings.
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  System.InvalidOperationException: Missing parameter: Id.
  at System.Web.Services.Protocols.ValueCollectionParameterReader.Read(NameValueCollection collection)
  at System.Web.Services.Protocols.UrlParameterReader.Read(HttpRequest request)
  at System.Web.Services.Protocols.HttpServerProtocol.ReadParameters()
  at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()

  This is the error message i get.

  Thanks!
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017 edited February 2017

  I was looking into this further. Please find below the code and the string that is being passed to the web service.

  editor = new $.fn.dataTable.Editor({

          ajax: {
              remove: {
                  type: "POST",
                  url: "webservice url",                    
                  dataType: 'json',
                  data:                     
                  function (d) {
                      d.Id = JSON.stringify(d);
                  }
              },
  

  for the above call for remove http action, the data that is passed to the webservice call is

  {"action":"remove",
  "data":
  {"4":{"Id":4,
  "Extension":"12345",
  "Extension2":"133",
  "Number":"133",
  "TOD":"105",
  "Name":"test1"},
  "10":{"Id":10,
  "Extension":"12346",
  "Extension2":"134",
  "Number":"134",
  "TOD":"106",
  "Name":"test2"}}}

  Delete button click:
  My requirement is to pass the id 4 and 10 to the webservice call as a list to delete both the id from the database. The input parameter for the webservice is defined as list. I am ok with changing the input parameter datatype to any other format if needed.

  Edit button click:
  Also when i update, i need to pass all the values to the webservice call to update the details in the database(id, extension,extension2, number,TOD,name). I have not tested this yet but will need solution for this once i complete the delete operation.

  I am getting the System.InvalidOperationException: Missing parameter: Id. when i execute the above code. Not sure what am missing here.

  If i hardcode the value as below,

  function (d) {
  d.Id = JSON.stringify(4);
  }

  the row 4 gets deleted from the db but the UI shows the same error message after it comes out of the webservice call.

  Thanks for your help!
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017

  d.Id = JSON.stringify(d);

  I'm not entirely convinced that that makes sense . Save the string version of the object to itself?

  The last example on the page I linked to uses return JSON.stringify( d );. That will send the JSON string to the server as the request body. If your server is expecting JSON in the request body, that is how to do it.

  Allan
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017

  Hi,

  I finally figured out a way to send the data to webservice call. I am able to update the database now. But I am not sure how to refresh the datatable with the updated data from DB.

  The return value from webservice call has entire table data in JSON format. How do I send this data in the editor webservice call and reload the datatable?
• siva88 Posts: 17Questions: 3Answers: 0

  February 2017 edited February 2017

  this is the ajax call I made to delete the seleted row.

  editor = new $.fn.dataTable.Editor({
  ajax: {
  remove: {
  type: "POST",
  url: "webservice url",
  dataType: 'json',
  data:
  function (d) {
  return JSON.stringify(d);
  }
  },

  The above call sends entire selected row data. I get the required values and pass them to my database query and delete the row from database. Then I wrote a select query to send the table data to the webservice.

  This is the value i get from the database after deleting the selected row (I have only one olumn in the database table. The other row got deleted as expected)

  {"d":"[{\"col1\":\"12345\",\"col2\":\"123\",\"col3\":\"123\",\"col4\":\"100\",\"col5\":\"test_1]"}
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  February 2017

  This page describes the JSON that Editor expects in return.

  If it isn't possible to return data in that format, you could use the postSubmit event handler to transform the data returned into that which is required by Editor.

  Allan