$('me').HELP();: JQGRID - JAVASCRIPT - CLASSIC ASP

Without the intention of wheel reinvention I'm pulling this post together to cover a solution to my specific set of circumstances. There is already a wealth of knowledge scattered across the four corners of the interweb, dipping into these repositories will most likely result in

(a) getting the exact result you were after,
(b) getting almost the right answer but needs tweaking (ie translating from one language to another: Javascript/Perl, French/English, Klingon/Chicken), or finally
(c) a vague wondering on what substances were fuelling some of the posts you've been reading.

In any case - you will most likely learn things along the way. So don't hold yourself back, get searching (but before you run off to do that, read this post first!)

I'm hoping this will mostly fall into the (a) category, but (b) is a likely candidate, but only you will know, so here goes.

Let's start with those annoying assumptions :

o you have IIS or similar environment
o jQuery and jqGrid installed to /js and /js/addons accordingly
o MS SQL 2005 or above
o Privalages to access all of the above

All good? Ok, now, what's next? We'll start with HTML - save it as something like [sampleGrid.html] in your webspace's root.

[CODE]

<html>
<head>
<title>Sample jqGrid</title>


<link rel="stylesheet" type="text/css" media="screen" href="js/themes/ui.jqgrid.css" />
<link rel="stylesheet" type="text/css" media="screen" href="js/themes/redmond/jquery-ui-1.8.16.custom.css" />


<script src="js/jquery-1.7.1.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script src="js/jquery-ui-1.8.16.custom.min.js" type="text/javascript"></script>


<script type="text/javascript" src="sampleGrid/js/main.js"></script>

</head>
<body>
<table id="mainGrid" class="a_grid">
</table>
<div id="mainGrid_pager">
</div>
</body>
</html>

[/CODE]

Pretty simple, eh? What does this give us? Not a lot really - in fact a blank window.

Make sure the file names match with your versions of jQuery, jQueryUI and jqGrid else we wouldn't get much further.

Now we need some Javascript. Put this in [sampleGrid/js/main.js]

[CODE]

//==========================================================================================================
// sampleGrid Javascript
//==========================================================================================================

$(document).ready(function () {

var mainGrid = $("#mainGrid");
mainGrid.jqGrid({
url: 'sampleGrid/asp/main.asp?sw=getMainGridData',
datatype: 'xml',
mtype: 'GET',
sortable: true,
height: 200,
width: 1000,

colNames: ['Sample Number','Date Created','Sample Status'],

colModel: [
{ name: 'SAMPLE_NUMBER', index: 'SAMPLE_NUMBER', width: 45, align: "center", sorttype: "text" },
{ name: 'CREATE_DATE', index: 'CREATE_DATE', width: 70, align: "center", sorttype: "text" },
{ name: 'STATUS', index: 'STATUS', width: 40, align: "center", sorttype: "text" },
],

rowNum: '20',
rowList: [50, 100, 200],
sortname: 'SAMPLE_NUMBER',
sortorder: 'asc',
viewrecords: true,
pager: '#mainGrid_pager',
gridview: true,
caption: "Main Grid"
}
$("#mainGrid").jqGrid('navGrid', '#mainGrid_pager'); // <-- Lets give it a pager
$("#mainGrid").jqGrid('filterToolbar',{stringResult: true,searchOnEnter : false}); // <-- Lets give it a filter

}

[/CODE]

Ok, this should give us a nice basic jqGrid with a filter along the top and pagination. But running this wouldn't us much either - we would get the empty framework of the grid. So we now need to go off and get some nice'n'juicy data from Mr Sql Server.

Before we start pulling the Classic ASP page together, we need to lay down some database access framework code. It'll become apparent why later on.

Put the into [sampleGrid/asp/dbconfig.asp]

<%
Dim rs, dbConn, strConn

Function OpenDB()
Set dbConn = Server.CreateObject("ADODB.Connection")

strConn = "Driver={SQL Server};Server=[YOUR SERVER NAME]" & _
"Database=[YOUR DATABASE NAME];" & _
"Uid=[YOUR USER NAME];" & _
"Pwd=[YOUR PASSWORD];"

dbConn.Open strConn
End Function

Function CloseDB()
Set rs = Nothing
If ucase(TypeName(dbConn)) = "OBJECT" Then
dbConn.Close
Set dbConn = Nothing
End If
End Function

%>

Not so bad. Don't forget to put your own details into [strConn]. Next we need the core Classic ASP code.

[CODE]

<%@Language=VBScript%>
<%Option Explicit%>
<%Response.Buffer=TRUE%>


<%

Dim strPage, strLimit, strIdx, strOrd, strCount, strTotalPages, strStart, sql, strEndNum
Dim strSearchOn, strField, strFieldData, strSearchOper, strWhere, strFilter, strWhereData, strSQL
Dim strCombo, blnIsFirst
Dim x

' Pull in variables from the grid
strPage = CInt(Request("page"))
strLimit = CInt(Request("rows"))
strIdx = Request("sidx")
strOrd = Request("sord")

strSearchOn = Request("_search")

If (strSearchOn = "true") Then

strFilter = Request("filters")

'=================
' Discard the prefix
'=================

'{"groupOp":"AND","rules":[{"field":"sample_number","op":"cn","data":"5"}]}

' Now cycle through the elements,pulling out each condition into an array

strWhere = ""

strWhereData = mid(strFilter,(instr(strFilter,"[")+1),instr(strFilter,"]")-(instr(strFilter,"[")+1))

' Now cycle through the elements,pulling out each condition into an array

Dim intCurPos, arrConditions(), strOp, intCurElement, intStartPos, intFieldLen, strData, intLoop

intCurPos = 0
intCurElement = 0

strOp = strWhereData

redim preserve arrConditions(0)

do while len(strOp) > 0

arrConditions(intCurElement) = left(strOp,instr(strOp,"}"))

intCurElement = intCurElement + 1

redim preserve arrConditions(intCurElement)

strOp = right(strOp,len(strOp)-instr(strOp,"}"))

if len(strOp) > 0 then strOp = right(strOp,len(strOp)-1) ' Knock off comma

Loop

for intLoop=0 to ubound(arrConditions)-1

intStartPos = instr(arrConditions(intLoop),"""field"":") + 9
intFieldLen = instr((right(arrConditions(intLoop),len(arrConditions(intLoop))-intStartPos)),chr(34))

strField = mid(arrConditions(intLoop), intStartPos, intFieldLen)

intStartPos = instr(arrConditions(intLoop),"""data"":") + 8
intFieldLen = instr((right(arrConditions(intLoop),len(arrConditions(intLoop))-intStartPos)),chr(34))

strData = mid(arrConditions(intLoop), intStartPos, intFieldLen)

strWhere = strWhere & " AND " & strField & " Like '%" & strData & "%'"

next

End If

strWhere = replace(strWhere,"'","''")

Dim strcontTYPE
strcontTYPE = Replace(Request("container_type"),"'","''")

if request.querystring("sw") <> "" then

'=================
' Initiate connection
'=================

Call OpenDB()

'================
' Initiate recordset
'================

set rs=Server.CreateObject("ADODB.recordset")

'Section switch
Select Case request.querystring("sw")

'=====================
Case "getMainGridData"
'=====================

sql = " [dbo].[sproc_GetMainGridData] '" & strWhere & "'," & strPage & ", " & strLimit & ", '" & strIdx & "', '" & strOrd & "'"

'response.write sql

rs.Open sql,dbConn

response.ContentType="text/xml"
response.Write("<?xml version='1.0' encoding='utf-8'?>")
response.Write("<rows>")
response.Write("<page>"&rs("page")&"</page>")
response.Write("<total>"&rs("total")&"</total>")
response.Write("<records>"&rs("records")&"</records>")

do until rs.EOF

response.write "<row id='"& rs("sample_number") &"'>"
response.write "<cell><![CDATA[" & isEmpty(rs("sample_number")) & "]]></cell>"
response.write "<cell><![CDATA[" & isEmpty(rs("date_created")) & "]]></cell>"
response.write "<cell><![CDATA[" & isEmpty(rs("status")) &" ]]></cell>"

response.Write "</row>"

rs.MoveNext

loop

response.Write "</rows>"

End Select

CloseDB()

end if

%>

[/CODE]

In short, the first part after the variable declarations is pulling out the sorting and filtering data sent from the Javascript and restructuring it into a 'where' clause to pass to the stored procedure (that we still need to write).

The switch later on handles the internal logic, so you can use one asp file for multiple grids and purposes.

In this case the switch is calling the stored procedure and then sending back all the info that the jqGrid needs to run correctly.

[CODE]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Paul Phillips>
-- Create date: <2012-07-25>
-- Description: <Base code for a stored procedure based jqGrid>
-- =============================================
CREATE PROCEDURE [dbo].[sproc_GetMainGridData]
(
@whereStr nvarchar(max)
,@strPage int
,@strLimit int
,@strIdx nvarchar(max)
,@strOrd nvarchar(max)
)

AS
BEGIN

SET NOCOUNT ON;
DECLARE @SQLStatement nvarchar(max)
DECLARE @strCount int
DECLARE @strTotalPages int
DECLARE @strStart int
DECLARE @strEndNum int

SET @SQLStatement =
'select
@output = count(*)
FROM
(
select
a.container_type
, a.volume
, a.container_id
, a.owner
, ROW_NUMBER() OVER(ORDER BY ' + @strIdx + ' ' + @strOrd + ' ) [RowNumber]
from

(select
container_type
, volume
, container_id
, owner
from x_containers
) as a where 1 = 1 ' + @whereStr + ') as b'

execute sp_executeSQL @SQLStatement, N'@output INT OUTPUT', @strCount OUTPUT

-- Determin pagination

IF (@strCount > 0)
BEGIN
set @strTotalPages = @strCount / @strLimit
END
ELSE
BEGIN
set @strTotalPages = 0
END

SET @strTotalPages = Ceiling(@strTotalPages)

If (@strPage > @strTotalPages)
BEGIN
set @strPage = @strTotalPages
END

set @strStart = @strLimit * @strPage - @strLimit

If (@strStart < 0)
BEGIN
set @strStart = 0
END

set @strEndNum = @strStart + @strLimit

--print @strCount

SET @SQLStatement =
'select
b.container_type
, b.volume
, b.container_id
, b.owner
, ' + convert(nvarchar,@strPage) + ' as page
, ' + convert(nvarchar,@strTotalPages) + ' as total
, ' + convert(nvarchar,@strCount) + ' as records
FROM
(
select
a.container_type
, a.volume
, a.container_id
, a.owner
, ROW_NUMBER() OVER(ORDER BY ' + @strIdx + ' ' + @strOrd + ' ) [RowNumber]
from
(select
container_type
, volume
, container_id
, owner
from x_containers
) as a where 1 = 1 ' + @whereStr + ') as b
where RowNumber between ' + convert(nvarchar,@strStart) + ' and ' + convert(nvarchar,@strEndNum)

exec (@SQLStatement)

END
[/CODE]

$('me').HELP();

Saturday, 23 March 2013

JQGRID - JAVASCRIPT - CLASSIC ASP - MS SQL 2005

4 comments: