Issue while loading the data table

siva88 · January 2017

Hi,

Am new to .Net web application, datatables and editor. I went through the basic examples in the website. I am facing an issue. Hope someone can throw some light on this.

Please find below the code that i use and error message i get.

HTML:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="xxx.cs" Inherits="xxx" %>

<!DOCTYPE html>

<title>test</title>



<link href="../DataTables/Bootstrap-3.3.7/css/bootstrap.css" rel="stylesheet" type="text/css" /><!-- Do not edit -->
<link href="../DataTables/Bootstrap-3.3.7/css/bootstrap-theme.css" rel="stylesheet" type="text/css" /><!-- Define theme changes here. -->
<link href="../DataTables/DataTables-1.10.13/css/dataTables.bootstrap.css" rel="stylesheet" type="text/css" />
<link href="../DataTables/Buttons-1.2.4/css/buttons.bootstrap.css" rel="stylesheet" type="text/css" />
<link href="../DataTables/Select-1.2.0/css/select.bootstrap.css" rel="stylesheet" type="text/css" />
<link href="../DataTables/Editor-1.6.1/css/editor.bootstrap.css" rel="stylesheet" type="text/css" />
<link href="../Styles/test.css" rel="stylesheet" type="text/css" />



<script src="../DataTables/datatables.min.js" type="text/javascript"></script>
<script src="../Scripts/test.js" type="text/javascript"></script>
<!-- End JavaScript Includes -->

</head>
<body>

test

col1

col2

col3

col4

col5

</body>

</html>

Javascript:

(function () {

var editor;


$(document).ready(function () {


    editor = new $.fn.dataTable.Editor({
        ajax: {
        url: "URL to DB service",
        contentType: "application/json; charset=utf-8",
        dataType: "json" },
        table: "#Grid-01",
        fields: [

        {
        "label": "col 1",
        "name": "col1"
    },
        {
            "label": "col 2",
            "name": "col2"
        },
        {
            "label": "col 3",
            "name": "col3"
        },
        {
            "label": "col 4",
            "name": "col4"
        },
        {
            "label": "col 5",
            "name": "col5"
        }

    ],
    formOptions: {
        inline: {
            onBlur: 'submit'             }
    }
});

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: "d"
    },
    columns: [

                    { "data": "col1" },
                    { "data": " col2" },
                    { "data": " col3" },
                    { "data": " col4" },
                    { "data": " col5" }
        ]
        });

// Display the buttons
new $.fn.dataTable.Buttons(table, [
        { extend: "create", editor: editor },
        { extend: "edit", editor: editor },
        { extend: "remove", editor: editor }
    ]);

//Add the buttons to the appropriate Bootstrap class
table.buttons().container()
        .appendTo($('.col-sm-6:eq(0)', table.table().container()));



$('#Grid-01').on('click', 'tbody td:not(:first-child)', function (e) {
    editor.inline(this);
});

});

} ());

Output of DB service:

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

Error message :

Datables warning: table id = Grid-01 – Requested unknown parameter ‘col1’ for row 0, column 0.

Thanks!

kthorngren · January 2017

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 · February 2017

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 · 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 · February 2017

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 · 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 · February 2017

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

Allan

siva88 · 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 · February 2017

Try the manual.
https://editor.datatables.net/manual/net/index#.NET-libraries-manual

siva88 · February 2017

Sure. I will check it out. Thanks!

allan · February 2017

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

Allan

siva88 · 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 · 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 · 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 · 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 · 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 · 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 · 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 · February 2017

You need to follow the diagnostic steps in the tech-notes link and report your findings.

siva88 · 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 · 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 · 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 · 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 · 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 · 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

Issue while loading the data table

Issue while loading the data table

test

Answers

Howdy, Stranger!

Categories

DataTables

Issue while loading the data table

Issue while loading the data table

test

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables