ASP Classic Server-side processing with SQL Server 2000

sstrycker

I recently worked out a server side script for DataTables that is running on a windows server 2003, IIS6 and SQL Server 2000. It uses the JSON serializer aspjson.

http://code.google.com/p/aspjson/

This is based on the script by diondu (thank you diondu).

Just wanted to post it in hopes it can help others still having to use classic asp :-)

Sean.

[code]
<!--#include file = "JSON_2.0.4.asp"-->
<%
Dim sEcho, iDisplayLength, iDisplayStart, sSearch, iTotal, sql, conn, rs, rs1, a, Products_cmd, Products, arrProducts
Dim iTotalDisplayRecords, strWhere, strOrderBy, order

Set a = jsArray()

sEcho = Cint(Request.QueryString("sEcho"))
iDisplayLength = Cint(Request.QueryString("iDisplayLength"))
iDisplayStart = Cint(Request.QueryString("iDisplayStart"))
sSearch = Request.QueryString("sSearch")

'*********** Searching ******************

strWhere = " (engine LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " browser LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " platform LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " version LIKE '%" & sSearch & "%' OR "
strWhere = strWhere & " grade LIKE '%" & sSearch & "%')"

'*********** Ordering ******************

for k=0 to 3
if Request.QueryString("bSortable_" & k)="true" then
order = order & Request.QueryString("iSortCol_" & k) & " " & Request.QueryString("sSortDir_" & k)
end if
next

order = Replace(order,"0",", engine")
order = Replace(order,"1",", browser")
order = Replace(order,"2",", platform")
order = Replace(order,"3",", version")
order = Replace(order,"4",", grade")

order = Right(order, Len(order)-1)

strOrderBy = "ORDER BY " & order

'*********** DB Connection ***************

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "FILEDSN= your file dsn connection string;"

'*********** iTotalRecords & iTotalDisplayRecords ***************

Set rs = conn.Execute("SELECT count(*) FROM ajax")

Set rs1 = conn.Execute("SELECT count(*) FROM ajax" & strWhere)

'************ ADODB Recordset **************

Set Products_cmd = Server.CreateObject ("ADODB.Command")
Products_cmd.ActiveConnection = conn
Products_cmd.CommandText = "SELECT engine, browser, platform, version, grade FROM ajax" & strWhere & strOrderBy

Products_cmd.Prepared = true

Set Products = Products_cmd.Execute

'************ Pagination **************

Products.Move(iDisplayStart)

'************ Display Length **************

arrProducts = Products.GetRows(iDisplayLength)

'************ ASP ARRAY to jsArray **************

For i = 0 to ubound(arrProducts,2)
Set a(Null) = jsArray()
a(Null)(Null) = arrProducts(0,i)
a(Null)(Null) = arrProducts(1,i)
a(Null)(Null) = arrProducts(2,i)
a(Null)(Null) = arrProducts(3,i)
a(Null)(Null) = arrProducts(4,i)
next

'************ ASPJSON ARRAY **************
response.write("{""sEcho"":"& sEcho &",""iTotalRecords"":"& rs(0) &",""iTotalDisplayRecords"":"& rs1(0) &",""aaData"":")

a.Flush

response.write("}")

conn.close
Set conn = nothing
%>
[/code]

allan

Hi Sean,

NIce one! Thanks very much for sharing this with us :-)

Regards,
Allan