Server-side script - Example using Classic ASP
Server-side script - Example using Classic ASP
Here is my example using Classic ASP and allan's database format.
[code]
<%
'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")
'SEARCH - here we make the Where clause that will be used in the SQL querry. You only put here the fields you want to search
strWhere = " WHERE 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
'passes through all cols and first check if the column is sortable, if yes then construct
'the variable "order" that list in order the sequence of ordering
for k=0 to 4
if Request("bSortable_" & k)="true" then
order = order & Request("iSortCol_" & k) & " " & Request("sSortDir_" & k)
end if
next
'here we replace the number corresponding the column position by the corresponding name of the column in the database
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")
'here we eliminate the first comma of the variable "order"
order = Right(order, Len(order)-1)
'here we create the variable "strOrderBy" that will be used in the SQL querry
strOrderBy = "ORDER BY " & order
'here we create the connection with de database (I used Microsoft SQL Server 2008)
Set Session("objConn") = Server.CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Server};Server=SERVER-NAME;Initial Catalog=DATABASE-NAME;User Id=LOGIN;Password=PASSWORD;"
Session("objConn").open strConnection
'here we create the SQL querry using the variables "strWhere" and "strOrderBy"
SQL = "SELECT * FROM ajax " & strWhere & strOrderBy
Set rs = Session("objConn").Execute(SQL)
'here is counted how many records we have in the querry using the search criterion and call this as "iTotalDisplayRecords"
iTotalDisplayRecords = 0
if rs.eof = false then
do while not rs.eof
iTotalDisplayRecords = iTotalDisplayRecords + 1
rs.movenext
loop
rs.movefirst
end if
'here we just count how many records we have in the table, WITHOUT any search criterion and call this as "iTotalRecords"
SQL2 = "SELECT count(*) FROM ajax"
Set rs2 = Session("objConn").Execute(SQL2)
iTotalRecords = rs2(0)
'here we begin to mount the ajax reponse
%>
{"sEcho": <%=sEcho%>, "iTotalRecords": <%=iTotalRecords%>, "iTotalDisplayRecords": <%=iTotalDisplayRecords%>, "aaData": [
<% i= 0
'PAGINATION
'First we move the recordset for the first record of the page that is being displayed in the table using the parameters(iDisplayStart)
'then we create a loop wich the limits are the parameters (iDisplayLength) or the end of the querry
if rs.eof = false then
rs.move(iDisplayStart)
do while i < iDisplayLength and not rs.eof
'we create the variable "aaData" that has the data that will be displayed. This variable is in a format known by the DataTable
aaData = aaData + "['" & rs("engine") & "','" & rs("browser") & "','" & rs("platform") & "','" & rs("version") & "','" & rs("grade") & "'],"
'here we replace the single quotes by double quotes (chr(34))
aaData = Replace(aaData,"'",chr(34))
rs.movenext
i=i+1
loop
'here we eliminate the last comma in the aaData
aaData = Left(aaData,Len(aaData)-1)
else
'if the querry result has no records the aaData will be empty and the Table will give an answer that no record was find
aaData = ""
end if
'here we finishes to mount the response
response.write aaData & "] }"%>[/code]
[code]
<%
'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")
'SEARCH - here we make the Where clause that will be used in the SQL querry. You only put here the fields you want to search
strWhere = " WHERE 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
'passes through all cols and first check if the column is sortable, if yes then construct
'the variable "order" that list in order the sequence of ordering
for k=0 to 4
if Request("bSortable_" & k)="true" then
order = order & Request("iSortCol_" & k) & " " & Request("sSortDir_" & k)
end if
next
'here we replace the number corresponding the column position by the corresponding name of the column in the database
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")
'here we eliminate the first comma of the variable "order"
order = Right(order, Len(order)-1)
'here we create the variable "strOrderBy" that will be used in the SQL querry
strOrderBy = "ORDER BY " & order
'here we create the connection with de database (I used Microsoft SQL Server 2008)
Set Session("objConn") = Server.CreateObject("ADODB.Connection")
strConnection = "Driver={SQL Server};Server=SERVER-NAME;Initial Catalog=DATABASE-NAME;User Id=LOGIN;Password=PASSWORD;"
Session("objConn").open strConnection
'here we create the SQL querry using the variables "strWhere" and "strOrderBy"
SQL = "SELECT * FROM ajax " & strWhere & strOrderBy
Set rs = Session("objConn").Execute(SQL)
'here is counted how many records we have in the querry using the search criterion and call this as "iTotalDisplayRecords"
iTotalDisplayRecords = 0
if rs.eof = false then
do while not rs.eof
iTotalDisplayRecords = iTotalDisplayRecords + 1
rs.movenext
loop
rs.movefirst
end if
'here we just count how many records we have in the table, WITHOUT any search criterion and call this as "iTotalRecords"
SQL2 = "SELECT count(*) FROM ajax"
Set rs2 = Session("objConn").Execute(SQL2)
iTotalRecords = rs2(0)
'here we begin to mount the ajax reponse
%>
{"sEcho": <%=sEcho%>, "iTotalRecords": <%=iTotalRecords%>, "iTotalDisplayRecords": <%=iTotalDisplayRecords%>, "aaData": [
<% i= 0
'PAGINATION
'First we move the recordset for the first record of the page that is being displayed in the table using the parameters(iDisplayStart)
'then we create a loop wich the limits are the parameters (iDisplayLength) or the end of the querry
if rs.eof = false then
rs.move(iDisplayStart)
do while i < iDisplayLength and not rs.eof
'we create the variable "aaData" that has the data that will be displayed. This variable is in a format known by the DataTable
aaData = aaData + "['" & rs("engine") & "','" & rs("browser") & "','" & rs("platform") & "','" & rs("version") & "','" & rs("grade") & "'],"
'here we replace the single quotes by double quotes (chr(34))
aaData = Replace(aaData,"'",chr(34))
rs.movenext
i=i+1
loop
'here we eliminate the last comma in the aaData
aaData = Left(aaData,Len(aaData)-1)
else
'if the querry result has no records the aaData will be empty and the Table will give an answer that no record was find
aaData = ""
end if
'here we finishes to mount the response
response.write aaData & "] }"%>[/code]
This discussion has been closed.
Replies
Nice one! Thanks for sharing this with us. Could you confirm that I've read the code correctly with the following supported features, and I'll post it up on the server-side scripts 'gallery' ( http://datatables.net/development/server-side/ ):
Compatibility - DataTables 1.6
Pagination - yes
Filtering - yes
Sorting - yes
Multi-column sorting - no
Individual column filter - no
Column filter enabled check (bSearchable_) - no
Column sort enabled check (bSortable_) - no
Regex support for filtering - no
Regards,
Allan
Compatibility - DataTables 1.6
Pagination - yes
Filtering - yes
Sorting - yes
Multi-column sorting - yes
Individual column filter - no
Column filter enabled check (bSearchable_) - no
Column sort enabled check (bSortable_) - yes
Regex support for filtering - no
I forgot to increment individual filtering, but this is quite easy. It can be implemented just writing if clauses in the strWhere string.
A couple questions:
(1) Do you need to setup anything on the client side (html) for this service side example to work?
For example, here in my test code:
[code]
$(document).ready(function () {
$('#listing').dataTable({
"sPaginationType": "full_numbers",
"bLengthChange": false,
"bFilter": false,
"bSort": false,
"bInfo": true,
"bAutoWidth": true
});
});
[/code]
(2) Is there anyway to format the data displayed? in this asp example, the variable "aaData" lays out a simple line of text?
Would it be possible convert this asp code (on the server side) that I need to add paging to?
[code]
do until rs.EOF
iArticleID = rs("ArticleID")
strArticleTitle = rs("Title")
strDescription = rs("Description")
strArticleRegion = rs("Region")
iAllowComments = rs("AllowComments")
strOriginCountry = rs("OriginCountry")
dbArticleDate = rs("date")
sTempLink=LinkMe("/press/render.asp","articleid="&iArticleID&",cid="&session("hcid")&",hcid="&session("hcid"),0)
response.write ""
response.write "<!-- Start main content-->"
response.write ""
response.write "" &strArticleTitle &""
response.write("" & strDescription & "")
response.write("")
response.write ""
response.write ""
response.write "<!-- Start Meta-->"
response.write("")
s_date = FormatDateTime(dbArticleDate, 1)
s_firstComma = instr(s_date,",") + 2
s_today = mid(s_date, s_firstComma , 22)
strMonthName = left(s_today,instr(s_today, " "))
s_today = replace(s_today,left(s_today,instr(s_today, " ")), trim(TranslateMe(strMonthName, iLanguageID)) & " ")
if session("hcid") = 15 or session("hcid") = 7 or session("hcid")=28 then ' Different format to display Japan date.
s_today = year(dbArticleDate) & "?" & month(dbArticleDate) & "?" & Day(dbArticleDate) & "?"
's_today = monthName(rs("date"))
end if
if session("hcid") = 35 then ' Different format to display Canada French date.
s_today = "Le " & Day(dbArticleDate) & " " & lcase(TranslateMe(monthName(month(dbArticleDate)),iLanguageID)) & " " & year(dbArticleDate)
end if
response.write "" & s_today & "|"
' Only show region if this is a global search
If strOriginCountry="" Or IsNull(strOriginCountry) Then
response.write "" & strArticleRegion & ""
Else
response.write "" & strOriginCountry & ""
End If
response.write(""&TranslateMe("Share",session("lid"))&"|")
'response.write("Read more")
if iAllowComments = "True" then
response.write("" & TranslateMe("Comments", session("lid")) & "")
end if
response.write ""
response.write("")
rs.MoveNext
loop
response.write ""
response.write ""
end if
[/code]
(3) Lastly, I am using a stored procedure to handle the paging, how do I incorporate it?
Example:
[code]
CREATE PROCEDURE
GetTowns ( @OutTotalRecCount INT OUTPUT, @CurrentPage INT, @PageSize INT )
AS
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY TownName) AS Row,
TownId,
TownName
FROM Towns ) AS TownsWithRowNumbers
WHERE Row >= (@CurrentPage - 1) * @PageSize + 1 AND Row <= @CurrentPage*@PageSize
SELECT @OutTotalRecCount = COUNT(*) FROM Towns
[/code]
http://code.google.com/p/aspjson/downloads/detail?name=JSON_2.0.4.asp
lemme know if anyone wants to collaborate :-)