How to process data into my datatable when fetching data via classic asp page

Question

I am implementing a datatable into my project. I have to process data in classic asp and might not be able to send the JSON because of some additional data processing required. Because of that I am making the rows of the table and sending it back through my classic asp. In my Html page, I am initializing the datatable and then appending the return data into my table.

I am not sure but due to that my pagination, sorting, the search is not working.

I am even not sure if this is the right way to do. Sending a JSON in classic asp is a nightmare and I have customization on every column which needs to be done in my asp page and not sure if it's possible to send those custom columns via JSON.

$(document).ready(function() {
$('#example').DataTable( {
"ajax":{
  "url" : 'admin_manage.asp',
  "type": 'POST',
  "dataType":'html',
  success: function(result){
    $("#example tbody").append(result);
  }
}

});

server side:

SQL = "SELECT * FROM dbo.Nominations" 
cmdCRProc.CommandText = SQL  
Set rs = cmdCRProc.Execute 

if rs.eof = false then
do while not rs.eof

response.write "<tr><td>" & rs(0) & "</td>"
response.write "<td>" & rs(1) & "</td>"
response.write "<td>" & rs(2) & "</td>"
response.write "<td>" & rs(3) & "</td>"
response.write "<td>" & rs(4) & "</td></tr>"

rs.movenext

loop
end if

Answer 1

JSON is client side code, classic ASP/VBScript is server side and of course you can use Classic ASP to write JSON, just as it's possible to use it to generate HTML. eg

Dim myJSONarray
  myJSONarray = "{["
  do while not rs.eof
  if not rs.bof then 
    myJSONarray = myJSONarray & ","
  end if

    myJSONarray = myJSONarray &  "{""Field1name"":"""& rs(0) & """,""Field2name"":"""& rs(1) & """,""Field3name"":"""& rs(2) & """,""Field4name"":"""& rs(3) & """}"
  rs.movenext
  loop
  myJSONarray = myJSONarray & "]}"


Response.ContentType = "application/json"
Response.write myJSONarray

The quotation marks are very confusing I know, they follow exactly the same logic as escaping quotations in html attributes. I recommend viewing your output to make sure it is valid JSON before you try to consume it.

Also, take a look at this question

How to return a JSON object in classic ASP