Server side Datatables not working with Classic ASP

zokanzi

I have a table with 120.000 record. I try an example for server-side pagination. I looked for a sample written in ASP and I found it But it is not working. I am getting "DataTables warning: table id=example - Invalid JSON response" error.

My script codes:

$(document).ready(function() {
$('#example').DataTable( {
    processing: true,
    serverSide: true,
    ajax: {
        url: "musteri_list_load.asp",
        dataType: "json"
    }
} );
} );

musteri_list_load.asp codes:

If rs.RecordCount > 0 Then

output = "{ ""data"":["

Do While Not rs.EOF

output = output & "{""id"":""" & rs("ID") & """,""engine"":""" & rs("engine") & """},"

rs.MoveNext
Loop

output=Left(output,Len(output)-1)
output = output & "]}"
End If

response.write output

And musteri_list_load.asp output:

{ "data":[{"id":"1","engine":"Trident"},{"id":"2","engine":"Trident"},{"id":"3","engine":"Trident"},{"id":"4","engine":"Trident"},{"id":"5","engine":"Trident"},{"id":"6","engine":"Trident"},{"id":"7","engine":"Gecko"},{"id":"8","engine":"Gecko"},{"id":"9","engine":"Gecko"},{"id":"10","engine":"Gecko"},{"id":"11","engine":"Gecko"},{"id":"12","engine":"Gecko"},{"id":"13","engine":"Gecko"},{"id":"14","engine":"Gecko"},{"id":"15","engine":"Gecko"},{"id":"16","engine":"Gecko"},{"id":"17","engine":"Gecko"},{"id":"18","engine":"Gecko"},{"id":"19","engine":"Gecko"},{"id":"20","engine":"Gecko"},{"id":"21","engine":"Gecko"},{"id":"22","engine":"Gecko"},{"id":"23","engine":"Gecko"},{"id":"24","engine":"Gecko"},{"id":"25","engine":"Gecko"},{"id":"26","engine":"Gecko"},{"id":"27","engine":"Webkit"},{"id":"28","engine":"Webkit"},{"id":"29","engine":"Webkit"},{"id":"30","engine":"Webkit"},{"id":"31","engine":"Webkit"},{"id":"32","engine":"Webkit"},{"id":"33","engine":"Webkit"}]}

I valideted this output this link and it is OK.

I don't know what to do. Help me please.

Thanks for your helps...

ThomD

What does your HTML look like for your table?

zokanzi

My HMTL page looks like this.

ThomD

That's helpful. Your data is not in JSON format. It is an array.

"data":[[1,Trident]...

Compare that to the data format here

http://datatables.net/examples/ajax/simple.html

zokanzi

I tried this, but it didn't work: (I validated in JSONLint)

{data:[[1,Trident],[2,Trident],[3,Trident],[4,Trident],[5,Trident],[6,Trident],[7,Gecko],[8,Gecko],[9,Gecko],[10,Gecko],[11,Gecko],[12,Gecko],[13,Gecko],[14,Gecko],[15,Gecko],[16,Gecko],[17,Gecko],[18,Gecko],[19,Gecko],[20,Gecko],[21,Gecko],[22,Gecko],[23,Gecko],[24,Gecko],[25,Gecko],[26,Gecko],[27,Webkit],[28,Webkit],[29,Webkit],[30,Webkit],[31,Webkit],[32,Webkit],[33,Webkit]]}

I am using asp codes below for create this:

If rs.RecordCount > 0 Then
    i = 1
    
output = "{data:["

    Do While Not rs.EOF

    output = output & "[" & rs("ID") & "," & rs("engine") & "],"

    rs.MoveNext
    Loop
    
output=Left(output,Len(output)-1)
output = output & "]}"
End If

response.write output

ThomD

What you have is an JSON object with an array in it. That is similar to the example I linked to. Try removing the datatype option as shown in line 7 of your original post.

alexvremja

you have to make something like this:

    if not rsx.eof then
        json="{""error"":"&errorCode&",""draw"":"&draw&",""recordsTotal"":"&rsx("rc")&",""recordsFiltered"":"&rsx("rc")&",""data"":["
        while not rsx.eof
            json=json&"{"
            for each field in rsx.fields
                tmpv=field.value
                if isnull(tmpv) then tmpv=" "
                tmpv=replace(tmpv,chr(34),""")
                tmp=tmp&chr(34)&field.name&chr(34)&":"&chr(34)&tmpv&chr(34)&","
            next
            tmp=left(tmp,len(tmp)-1)&"},"
            json=json&tmp
            rsx.movenext()
        Wend
    end if

zokanzi

@ThomD, it didn't fix when i removed datatype. But I am sure that the fault is in this section. But I don't know where.

@alexvremja your codes are getting similar output to my codes. What is your $(document).ready(function() codes?

Thanks

alexvremja

var table=$('#dataTable').DataTable( {
    "processing": true,
    "serverSide": true,
    "buttons": [
        'columnsToggle',
        'csv',
        'copy'
    ],
    "stateSave": true,
    "stateSaveParams": function (settings, data) {
        delete data.search;
        for (var ii=0;ii<data.columns.length;ii++)
        {delete data.columns[ii].search};
      },
    "stateSaveCallback": function (settings, data) {
        var parameters={Table:tblName, User:'UID};
        $.extend(data,prmt);
        $.ajax( {
          "url": "../ajax/DTState.asp",
          "data": data,
          "dataType": "json",
          "type": "GET",
          "success": function () {}
        } );
      },
    "dom": 'BRrtiS<"bottom">f<"clear">',
    "pagination":false,
    "scrollY": wh+"px",
    "scrollX": true,
    "deferRender": true,
    "displayStart":0,
 /* "paging":false,
    "ajax": 
    {
        "url": "ajax/DTLoad.asp",
        "data": function (d) {
            d.myKey=T;
        }
    }
 */
    "ajax": $.fn.dataTable.pipeline( {
        url: "../ajax/DTLoad.asp",
        data: function (d) {d.myKey=T;},
        pages: 5 // number of pages to cache
        }),
    "drawCallback": function( settings ) {
        $('#control-wraps a').fontSizer();
        },
    "columns": [<%=tcolumns%>],
    "stateLoadCallback": function (settings) {
        var o;
        $.ajax( {
          "url": "../ajax/DTState?Table="+T,
          "async": false,
          "dataType": "json",
          "success": function (json) {
            o = json;
          }
        });
        return o;
        }
});

but from what I have understood "your" problem is on "":
You have to encapsulate every fieldName and FieldValue inside "":
to be more clear:

    output = output & "[" & rs("ID") & "," & rs("engine") & "],"

has to be

    output = output & "[" & """"& rs("ID") & """,""" & rs("engine") & """],"

zokanzi

@alexvremja your solution is working. It is worked when I changed output codes as you noted.

Thank you.

alexvremja

:-)

zokanzi

I have a new problem now. My paging and searching system is not working.

My script codes:

$(document).ready(function() {
    $('#example').DataTable( {
        processing: true,
        serverSide: true,
        lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "Hepsi"]],
        ajax: "musteri_list_load.asp"
   } );
 } );

My musteri_list_load.asp codes

'here we get some parameters passed by the DataTables using GET
sEcho = Cint(Request("sEcho"))
 iDisplayLength = Cint(Request("iDisplayLength"))
 iDisplayStart = Cint(Request("iDisplayStart"))
 sSearch = Request("sSearch")

 
set rs1=Server.CreateObject("ADODB.recordset")
rs1.Open "Select * from ajax", bag,1,3
iTotalRecords = rs1.RecordCount

set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from ajax WHERE engine like '%"  & sSearch & "%'  ORDER BY id ASC", bag,1,3
iTotalDisplayRecords = rs.RecordCount

    
output = "{""draw"": 1, ""recordsTotal"": "& iTotalRecords  &", ""recordsFiltered"": "& iTotalDisplayRecords &", ""data"":["
    i = 0

    if rs.eof = false then
 
        rs.move(iDisplayStart)
     
        do while i < iDisplayLength and not rs.eof

output = output & "[" & """"& rs("ID") & """,""" & rs("engine") & """],"

            rs.movenext
 
            i=i+1
 
        loop
        'here we eliminate the last comma in the aaData output=Left(output,Len(output)-1)
output = output & "]}"
End If

response.write output

Codes are working in this link and you can look at my error.

Thanks.

alexvremja

no idea about paging.. never used
but concerning filtering..
what means this?

<a href="//legacy.datatables.net/ref#oLanguage.sSearch">sSearch</a> = Request("sSearch")

and also this

rs.Open "Select * from ajax WHERE engine like '%"  & <a href="//legacy.datatables.net/ref#oLanguage.sSearch">sSearch</a> & "%'  ORDER BY id ASC", bag,1,3

is quite unusual.. do you have html tags into a database field?
in any case you are searching for the html string and not the content of sSearch..

for filtering I think you should use a different approach: something like:

<%
    sSearch=Server.HTMLEncode(request.querystring("sSearch")&request.form("sSearch")) 'for debugging is easier work with GET but in production is better to use POST

    ' Then assuming your db TABLE has the following Fields:
    ' ID, UserID, Name, Surname, City, Country, email, Phone, Preferences and want to filter result where sSearch value is Fields Name and Surname and City your SQL should be like this:
    sql="Select * from TABLE where UserID like '%"&sSearch&"%' AND Name like '%"&sSearch&"%' AND Surname like '%"&sSearch&"%' AND City like '%"&sSearch&"%' ORDER BY ID ASC" 
    rs.open sql,bag,3,3
    .....

    ' there are many different solution to retrieve recordcount, but I suggest you once you get rs1.RecordCount
    'to do:

    rs1.close
    set rs1=nothing

    'or better instead of instantiate rs and rs1
    'use rs to total records
    'rs.close
    'and use again for the filter query.
    '
    ' or put all together:
    RCT=0
    RCF=0
    sql="SELECT (SELECT COUNT(*) FROM table) AS RCT, COUNT(*) OVER() RCF, * FROM table WHERE userid LIKE '%"&sSearch&"%' AND name LIKE '%"&sSearch&"%' AND surname LIKE '%"&sSearch&"%' AND city LIKE '%"&sSearch&"%' ORDER BY id ASC"
    set rsx=bag.execute(sql)
    if not rsx.eof then
        RCT=rsx("RCT")
        RCF=rsx("RCF")
        while not rsx.eof
            'Read Records...
            rsx.movenext
        wend

    end if
    rsx.close
    set rsx=nothing
    '
    ' then you find in RCT the RecordCountTotal and in RCF the RecordCountFiltered

%>

But I think we are OT.. this is ASP issue not DT

good luck!

zokanzi

Oh ok. Thanks for your helps...

But if I know how to get search variable to my asp page, I will solve my problem. I only don't know selectbox names, textbox names and page number form elements names. Can I determine them in my script codes below?

$(document).ready(function() {
    $('#example').DataTable( {
        processing: true,
        serverSide: true,
        lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
        ajax: "musteri_list_load.asp"
   } );
 } );

These links were automatically added by forum editor.

<a href="//legacy.datatables.net/ref#oLanguage.sSearch">sSearch</a> = Request("sSearch")

My real codes:

sSearch = Request("sSearch")

And my script is working perfect when I make serverside: false.

But I need serverside for over 120.000 records.

zokanzi

Is there anyone who will help me for my problem pls?

alexvremja

you have to check which parameters does the ajax query pass to the new page:

just need click right on inspect element and check the parameters of call in network..