DataTable SearchPanes error - "Unknown field: id (index 0)"

Kori

Hi,
I am trying to implement SearchPanes functionality for my datatable.
The error I am always getting(for two days now) is "Unknown field: id (index 0)"

This is how my JS side looks like for now:

$("#customerDatatable").DataTable({

        ajax: {
            url: "/api/customer",
            type: "POST"
        },
        columns: [
             { "data": "id", "name": "Id2", "autoWidth": true },
            { "data": "firstName", "name": "First Name", "autoWidth": true },
            { "data": "lastName", "name": "Last Name", "autoWidth": true },
            { "data": "contact", "name": "Country", "autoWidth": true },
            { "data": "email", "name": "Email", "autoWidth": true },
            { "data": "dateOfBirth", "name": "Date Of Birth", "autoWidth": true },
        ],
        columnDefs: [{
            searchPanes: {
                show: true,
            },
            targets: [1],
        }],
        dom: 'Plfrtip',
        serverSide: true,
        select: true
});

My Class looks like this:

[Table(Name = "CustomerTable")]
    public class Customer
    {
        [Column(Name = "Id")]
        public string Id { get; set; }
        [Column(Name = "FirstName")]
        public string FirstName { get; set; }
        [Column(Name = "LastName")]
        public string LastName { get; set; }
        [Column(Name = "Contact")]
        public string Contact { get; set; }
        [Column(Name = "Email")]
        public string Email { get; set; }
        [Column(Name = "DateOfBirth")]
        public string DateOfBirth { get; set; }

    }

My Controller looks like this:

var connection = new SQLiteConnection(@"Data Source=.\\SQLiteDBName.db;Version=3;");  
                                                                                                   
///try to get searchPanes result
                var SearchPaneResponse = new Editor(new Database("sqlite", @"Data Source=.\SQLiteDBName;"), "Customer","Id")
                        .Model<Customer>()
                        .Field(new Field("FirstName")
                            .SearchPaneOptions(new SearchPaneOptions())
                        )
                        .Process(Request)
                        .Data();
 // end of trying to get searchPane result

                var draw = Request.Form["draw"].FirstOrDefault();
                var start = Request.Form["start"].FirstOrDefault();
                var length = Request.Form["length"].FirstOrDefault();
                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
                var searchValue = Request.Form["search[value]"].FirstOrDefault();
                int pageSize = length != null ? Convert.ToInt32(length) : 0;
                int skip = start != null ? Convert.ToInt32(start) : 0;
                int recordsTotal = 0;
                 
                var context1 = new DataContext(connection);
                var companies = context1.GetTable<Customer>().AsQueryable();
                var customerData = companies;
                //
                customerData = customerData.OrderByDescending(d => d.FirstName);
                var result = customerData.GroupBy(item => item.DateOfBirth)
                      .Select(item => new
                      {
                          Name = item.Key,
                          Count = item.Count()
                      })
                      .OrderByDescending(item => item.Count) 
                      .ToList().Distinct();

                var filteredList = customerData.Where(x => x.DateOfBirth.Equals("NoData")).ToList();

                //
                if (!string.IsNullOrEmpty(searchValue))
                {
                    customerData = customerData.Where(m => m.FirstName.Contains(searchValue)
                                                || m.LastName.Contains(searchValue)
                                                || m.Contact.Contains(searchValue)
                                                || m.Email.Contains(searchValue));

                }
                recordsTotal = customerData.Count();
                var data = customerData.Skip(skip).Take(pageSize).ToList();
                
                var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data, searchPane = SearchPaneResponse };
                return Ok(jsonData);

I am able to render datatable with all records visible and searchPane (target 1)space is showing 'No data available in table' text..

I am really stuck with this one...

Thanks,
Steven

sandy

Hi @Kori ,

Which line is the error occuring on? Is it happening on the client side or the server side? Could you also include a small portion of the response that you are getting from the server?

Nothing is standing out to me. It may be easier if you can link to the page that you are working on?

Thanks,
Sandy

Kori

Hi @sandy,

it is happening on server side, controller when I am trying to get SearchPanes for Id or firstName fields.

 var SearchPaneResponse = new Editor(new Database("sqlite", @"Data Source=.\SQLiteDBName;"), "Customer","Id")
                        .Model<Customer>()
                        .Field(new Field("FirstName")
                            .SearchPaneOptions(new SearchPaneOptions())
                        )
                        .Process(Request)
                        .Data();

I am getting "Unknown field: id (index 0)" as SearchPaneResponse.error value

This is ASP.Net Razor so I have pasted my whole code here:
http://live.datatables.net/xejunezo/1/edit

This is how my response looks like...

And this is how my sqlite DB looks like:

And this is my whole response in the end:

Thankssss

sandy

Hi @Kori ,

We think that the issue is that you aren't overriding the default primary key value of id with your value of Id - see docs here. Could you try adding the third parameter and see if that fixes the issue?

If that doesn't work, could you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Sandy

Kori

Hi @ sandy,

yes, you are 'half-correct'. I have added third parameter (Id instead of id)
and had to add .Set(false) after field.
Thank you

Kori

@sandy
As I am using server side processing, is there an option to prevent reloading search panes when I choose some value from search pane?
I am using data from sqlite and I know that data cannot be edited for one day so I don't need search pane 'refresh' method. It is making me a problem to have custom html where I would 'hide' and 'show' search panes..
for example, this is how it looks like initially:

when I click on that section i get this(I just show that search pane):

if is choose some value, it renders again and this is what i see:

As you can see, custom styling totally dissapeared
Thanks

sandy

Hi @Kori ,

I'm afraid there isn't a way to do that without major changes to the source code. Your best bet would be to wait for SearchPanes to finish rebuilding and then perform further dom manipulation to put them back where you want.

Thanks,
Sandy