DataTables Column Searching

david.j.meyer2@boeing.com

I am implementing column searching on a table that does server side processing. When i add the text to the column it is only performing "case sensitive" searches.

dataTable.columns(0).search("1N");
dataTable.draw()

That only matches the specific case.

kthorngren

Your server script is responsible for the search. You will need to look at your search query to determine why its case sensitive. Are you using one of the server side processing scripts supplied by Datatables?

Kevin

david.j.meyer2@boeing.com

I'm implementing in C#, i have included the code below.

            var response = new Editor(db, "archive_table", "archive_table.DT_ROWID")
                .Model<ArchiveTable>("archive_table")
                .Process(request)
                .Data();

kthorngren

Looks like that code initializes the Editor. That is not the part of the code to look at. In your Datatables portion of the code you are performing some sort of data query from the Datatables Ajax requests. That is the code to look at.

Kevin

david.j.meyer2@boeing.com

So My code is pretty simple for my table setup. I have created a custom button that reads input from the user in a textbox and applies it to the table. It works but it is case sensitive.

        var table = $('#archive').DataTable({
            dom: "frtip",
            autoWidth: true,
            ajax: {
                url: "/api/archive",
                type: "POST"
            },
            lengthMenu: [[15, 50, 100, 250, 1000], [15, 50, 100, 250, 1000]],
            processing: false,
            serverSide: true,
            columns: [
                { data: "archive_table.ATNO"},
                { data: "archive_table.ATTYPE" },
                { data: "archive_table.ATSHT", searchable: false },
                { data: "archive_table.ATREV", searchable: false },
                { data: "archive_table.ATCAGE" }
            ],
            select: true
        });

        var tableButtons = new $.fn.dataTable.Buttons($('#archive'), {
            buttons: [
                { extend: "print" },
                { extend: "pdfHtml5" },
                { extend: "excel" },
                { extend: "colvis" },
                { extend: "pageLength" },
                {
                    text: 'Filter',
                    action: function (e, dt, node, config) {
                        $('#archive').DataTable().columns(0).search($("#filterAtno").val());
                        $('#archive').DataTable().columns(1).search($("#filterAttype").val());
                        $('#archive').DataTable().draw();
                    }
                }
            ]
        }).container().appendTo($('#buttons'));

I'm getting frustrated, any help is appreciated.

thanks,

kthorngren

With serverSide: true, the search is performed by your server script. Your client code has not control over how the server script performs the search. When the search occurs Datatables is going to send an Ajax request to the URL you defined, url: "/api/archive",. You will need to look at the server script called at /api/archive to see what it is doing with the column searches.

This explains the protocol used by server side processing:
https://datatables.net/manual/server-side

If you want to post your server script something may stand out. I'm not familiar with C# but others may be.

Kevin

kthorngren

Not sure what your data source is but it the DB might be setup as case sensitive. Or it could be your queries are performing case sensitive searches.

Kevin

david.j.meyer2@boeing.com

The code i posted first instantiating the editor is all that is being done on the ajax call.

We are working with an oracle database with a table that has 350K rows. Not sure what I'm missing. Your first reply implied that I shouldn't be using the editor, is there a "DataTable" object i should be using instead?

kthorngren

Your first reply implied that I shouldn't be using the editor, is there a "DataTable" object i should be using instead?

Again I'm not familiar with C# so I may not understand the var response = new Editor(db.... code you posted. The point is that you have server script that handles the Ajax requests from Datatables to support the parameters sent by the server side processing mode.

Anyway, you said you have Oracle. That is also something I'm not familiar with but I found this SO Thread that states "string comparisons are case-sensitive". Maybe this is the problem you are seeing.

Datatables doesn't control whether the server performs case sensitive or insensitive searches. @allan or @colin may have some ideas for you but it sounds like you either need to find a way to make your Oracle query statements use case insensitive searches (maybe convert the search term and field value in the SELECT to upper case, like this) or change settings of your DB to default to case insensitive searches.

Kevin

allan

It looks like you need to use NLS_COMP to do a case insensitive sort in Oracle (or use regex).

So you could try:

db.Sql("ALTER SESSION SET NLS_COMP=LINGUISTIC;");

before you initialise the Editor instance in C#.

Allan