New Classic ASP Server-side script for DataTables 1.10.20

SlavTheClaw

Hello Everyone,

I just wanted to share my Classic ASP server-side script that works with the current 1.10.20 DataTables - you may use it without major adjustments, except SQL query and connection data.
It fully supports pagination, ordering and searching through table.
Just make an ajax call for the file with below code and it will just work.

To generate Json I'm using below library:
http://www.webdevbros.net/2007/04/26/generate-json-from-asp-datatypes/
But you may change it if you wish.
IF you will not use this library, you will not need:

at the top of the file

<%@ EnableSessionState = false 
LANGUAGE="VBSCRIPT" CODEPAGE="65001"
%>
<%Option explicit%>
<!--#include file="./JSON.asp"-->
<%
'Initial variables - you may not need them:
Response.CodePage = 65001
Response.CharSet = "utf-8"
Response.AddHeader "X-XSS-Protection", "1"

'Declaration of all variables. If you don't want to declare everything - remove "Option explicit" above.
Dim  strFiltered, strEdit, strWhere, strSearch, strPaging, strOrder, StartRecord, PageNumber, RowsPerPage, draw, JsonAnswer, JsonHeader, rTotal, rFiltered, OrderCol, OrderDir, dbConn, rsReport, strReport, cmd, strCN

'So in simple way - you may start your file here by removing all above and putting <% here.

'Reading ordering data nd settin to empty if none. Empty should never happen, but it's just for safety.
OrderCol = Cint(Request("Order[0][column]"))
OrderDir =  Request("Order[0][dir]")
if not OrderCol = "" and not OrderDir = "" then 

'WHERE clause uses columns number, like e.g: ORDER BY 1 DESC, you may add translations to column names here, like e.g.: OrderCol = Replace(OrderCol,"0","Col1")
'We are adding 1 here, because DataTables indexes columns starting from 0
strOrder=" Order By " &OrderCol+1&" "&OrderDir 
else 
strOrder="" 
end if

'reading numbers sent by DataTables and setting them to defaults in case of empty (which should never happen):
draw = Cint(Request("draw"))

StartRecord = Cint(Request("start"))
if StartRecord = "" then StartRecord=0

RowsPerPage = Cint(Request("length"))
if RowsPerPage = "" then RowsPerPage=10

PageNumber = (StartRecord/RowsPerPage)

'reading search phrase - this one may be empty
strSearch = (Request("search[value]"))

'if not empty, then gerenate 'WHERE' Clause. Here you should edjust query to your DB.
if not strSearch ="" then
strWhere = " WHERE Col1 LIKE '%" & strSearch & "%' OR "
strWhere = strWhere & " Col2 LIKE '%" & strSearch & "%' OR "
strWhere = strWhere & " Col3 LIKE '%" & strSearch & "%' OR "
strWhere = strWhere & " Col4 LIKE '%" & strSearch & "%' OR "
strWhere = strWhere & " Col5 LIKE '%" & strSearch & "%'"
else
strWhere=""
end if

'generating pagination part of query:
strPaging = " OFFSET ("&PageNumber&")*("&RowsPerPage&") ROWS " &_
            "FETCH NEXT "&RowsPerPage&" ROWS ONLY"


'here is the most simple DB connection data and opening example - you should edit it as per your needs:
strCN = "Provider=MSOLEDBSQL;Data Source=.\SQL; Initial Catalog=shortcuts; Persist Security Info=False;User ID=ID; Password=Pass;"

Set dbConn = Server.CreateObject("ADODB.Connection")
Set rsReport = Server.createObject("ADODB.Recordset")
set cmd  = Server.CreateObject("ADODB.Command")

dbConn.mode = 3
dbConn.cursorlocation = adUseClient
rsReport.cursorlocation = adUseClient

dbConn.Open strCN
'connection is open here

'first query to count total number of records
strReport = "select COUNT(*) as Total from main"
rsReport.Open strReport, dbConn
'assigning value to variable
rTotal = rsReport("Total").value

'closing recordset and creating and executing new query
rsReport.Close
'first query to get data from DB to table with WHERE, ORDER and 'PAGINATION' clauses
strReport = "select * from main" & strWhere  & strOrder & strPaging
rsReport.Open strReport, dbConn

'Here is generated JSON from first query result. Using this library: http://www.webdevbros.net/2007/04/26/generate-json-from-asp-datatypes/
'You may here iterate through recordset and generate JSON manually.
JsonAnswer=(new JSON).toJSON("data", rsReport, false)

rsReport.Close
'second query to get number of results
strFiltered = "select Count(*) as Filtered from main" & strWhere 
rsReport.Open strFiltered, dbConn
'Assign number of results to variable:
rFiltered=rsReport("Filtered").value

'Closing and clearing connection - you may do it your way here:
set cmd.activeConnection = nothing
set cmd = nothing
dbConn.close
set dbConn=nothing
rsReport.close
set rsReport=nothing        

'finalizing JSON response - preparing header:
JsonHeader = "{ ""draw"": "& draw &", "& vbcrlf
JsonHeader = JsonHeader & """recordsTotal"": " & rTotal &", "& vbcrlf
JsonHeader = JsonHeader & """recordsFiltered"": " & rFiltered &", "& vbcrlf
 
'removing from generated JSON initial bracket { and concatenating all toghether.
JsonAnswer=right(JsonAnswer,Len(JsonAnswer)-1)
JsonAnswer = JsonHeader & JsonAnswer

'writing a response:
response.write(JsonAnswer)

%>

SlavTheClaw

EDIT:
You will not need:

<!--#include file="./JSON.asp"-->

at the top of the file of course.
It seems that it became a HTML comment on this site

colin

Excellent, thanks for sharing.

Colin

PieterCooreman

This is a great solution. Many thanks for posting this solution. I rewrote some parts of your solution and used it here: https://demo.asplite.com/demo.asp?e=datatables_ssp

I added your script to the demo showcase of aspLite, a new framework for ASP/VBScript developers. I hope you don't mind I used parts of your script. I plan to rewrite it completely though, so it can easily be used for all sorts of queries.

PieterCooreman

@SlavTheClaw : I have created a full-featured solution for Classic ASP:

https://demo.asplite.com/demo.asp?e=datatables_ssp

Not only it loads 50.000 records via Json (what you did already), I also added the CRUD functions for Classic ASP.

You can download everything on https://github.com/PieterCooreman/aspLite

It's a bit of a mess at this point, so I will have to rewrite it to create some reusable and extendable.