jQuery Grid Plugin - jqGridForum

Forum

November 2nd, 2014

A A A

Topic RSS

Integrating jqGrid 3.6 into Classic ASP

10/11/2009
00:18

davidbemenderfer

Member

Members

Forum Posts: 17

Member Since:
10/11/2009

Offline

I am trying to integrate jqGrid into classic ASP. I have successfully tested using the array method for populating but I need to have a source of up to 150,000 records and think XML would be more efficient. I am open to ANY suggestions someone may have. I am working on a proof of concept and been unsuccessful for two days. .NET is not a short term option. There is a plan to convert to .NET in the next year.

The error I am receiving is "Error: Object Required" referring to line 641 of the debug jquery-1.3.2.js file.

Thanks for any insight or assistance in advance.

-David

My grid file ("BaseGrid.asp"):

<html>
<head>
  <title>Test999.html jqGrid Demo</title>
  <link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.7.1.custom.css" />
  <link rel="stylesheet" type="text/css" media="screen" href="css/ui.jqgrid.css" />
  <script src="jquery-1.3.2.js" type="text/javascript"></script>
  <script src="jquery.jqGrid.js" type="text/javascript"></script>

My XML generation file ("party.xml.asp"):

<% @Language=VBScript %>
<% 'OPTION EXPLICIT' %>
<% Response.Buffer=TRUE %>

'response.write " Begin: " & NOW()'
Function Ceiling(intNumber)
Dim dblNumber
dblNumber = CDbl(intNumber)

If Int(dblNumber * 10) MOD 10 > 0 Then
Ceiling = Int(dblNumber) + 1
Else
Ceiling = Int(dblNumber)
End If
End Function

Dim strPage, strLimit, strIdx, strOrd, strCount, strTotalPages, strStart
Dim strSQL
Dim strSearchOn, strField, strFieldData, strSearchOper, strWhere

strPage = cLng(Request("page"))
strLimit = cLng(Request("rows"))
if strLimit = "" or strLimit = 0 then
strLimit = 20
end if
strIdx = Request("sidx")
strOrd = Request("sord")

strSearchOn = Request("_search")
If (strSearchOn = "true") Then
strField = Request("searchField")
If (strField = "party_id" Or strField = "first" Or strField = "last") Then
  strFieldData = Request("searchString")
  strSearchOper = Request("searchOper")
  'construct where'
  strWhere = " Where " & strField

  Select Case strSearchOper
  Case "bw" : 'Begin With
   strFieldData = strFieldData & "%"
   strWhere = strWhere & " LIKE '" & strFieldData & "'"
  Case "eq" : 'Equal
   If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " = " & strFieldData
   Else
     strWhere = strWhere & " = '" & strFieldData & "'"
   End If
  Case "ne": 'Not Equal
    If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " <> " & strFieldData
    Else
     strWhere = strWhere & " <> '"& strFieldData &"'"
    End If
  Case "lt": 'Less Than
    If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " < " & strFieldData
    Else
     strWhere = strWhere & " < '"& strFieldData &"'"
    End If
  Case "le": 'Less Or Equal
    If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " <= " & strFieldData
    Else
     strWhere = strWhere & " <= '"& strFieldData &"'"
    End If
  Case "gt": 'Greater Than
    If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " > " & strFieldData
    Else
     strWhere = strWhere & " > '"& strFieldData &"'"
    End If
  Case "ge": 'Greater Or Equal
    If(IsNumeric(strFieldData)) Then
     strWhere = strWhere & " >= " & strFieldData
    Else
     strWhere = strWhere & " >= '"& strFieldData &"'"
    End If
  Case "ew" : 'End With
   strWhere = strWhere & " LIKE '%" & strFieldData & "'"
  Case "cn" : 'Contains
   strWhere = strWhere & " LIKE '%" & strFieldData & "%'"
  End Select
End if
End If

strConn = "Provider=SQLOLEDB.1;User ID=XXXXXX;password=XXXXXX;Initial Catalog=XXXXXX;Data Source = XXXXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open strConn

strSQL = "SELECT TOP 50 COUNT(*) AS Count FROM [party] "&strWhere&" "
'response.write strSQL'
Set rs = dbConn.Execute(strSQL)

strCount = cLng(rs("count"))

If (strCount > 0) Then
strTotalPages = strCount / strLimit
Else
strTotalPages = 0
End If

strTotalPages = Ceiling(strTotalPages)

If (strPage > strTotalPages) Then
strPage = strTotalPages
End If

strStart = strLimit * strPage - strLimit

If (strStart < 0) Then
strStart = 0
End If

strSQL = "SELECT TOP 50 * FROM [party] "&strWhere&" "

Set rs = dbConn.Execute(strSQL)
'response.Write strSQL'

strXMLString = ""
strXMLString = strXMLString & "<?xml version='1.0' encoding='utf-8'?>"
strXMLString = strXMLString & "<rows>"
strXMLString = strXMLString & "<page>"&strPage&"</page>"
strXMLString = strXMLString & "<total>"&strTotalPages&"</total>"
strXMLString = strXMLString & "<records>"&strCount&"</records>"

dblCounter = 1
dblFlushCounter = 0
Do While Not rs.Eof
if dblFlushCounter = 200 then
  response.write strXMLString
  response.flush
  dblFlushCounter = 0
  strXMLString = ""
end if

strXMLString = strXMLString & "<row id='"& dblCounter &"'>"
  strXMLString = strXMLString & "<cell>" & rs("Party_ID") &"</cell>"
  strXMLString = strXMLString & "<cell>" & rs("first") &"</cell>"
  strXMLString = strXMLString & "<cell><![CDATA[" & rs("last") & "]]></cell>"
strXMLString = strXMLString & "</row>"
rs.MoveNext
dblCounter = dblCounter + 1
dblFlushCounter = dblFlushCounter + 1
Loop

strXMLString = strXMLString & "</rows>"
'Response.contenttype = "text/xml"'
Response.Write strXMLString
response.flush

'response.write " End: " & NOW()'
%>

10/11/2009
00:44

davidbemenderfer

Member

Members

Forum Posts: 17

Member Since:
10/11/2009

Offline

Issue resolved:

Adding the following code to party.xml.asp resolved the issue.

Response.contenttype = "text/xml;charset=utf-8"

When addressing the entire 150,000 records, it is still slow generating the XML. Are there any suggestions? Is JSON any faster?

10/11/2009
08:45

eljaywilson

Member

Members

Forum Posts: 22

Member Since:
09/11/2009

Offline

I use jQuery with classic asp and use ajax requests via JSON returns. It is very fast and much cleaner (IMO) than XML. There is a JSON ASP Project that is really nice.

For example, this is how clean the JSON return can be:

Set member = jsArray()

While Not (rs.EOF Or rs.BOF)

Set member(Null) = jsObject()

For Each col In rs.Fields

member(Null)(col.Name) = col.Value

rs.MoveNext

Wend

member.Flush 'Writes out (returns) the object in JSON format to the calling script

Here is where you get it:

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

I love it.

HTH

11/11/2009
23:05

davidbemenderfer

Member

Members

Forum Posts: 17

Member Since:
10/11/2009

Offline

Thank you so much. That worked beautifully and the JSON performance is incredible. Do you have any examples of adding rows, editing, and saving via asp? I'm sort of at a loss at where to begin. It's probably not that hard.

I'm using inline editing.

My goal is that the add button creates a blank line, the save button obviously saves (either on a line by line basis or the records that have been changed), and the delete button obviously deletes the line. I'm just looking for where to start.

Thanks so much,

-David

16/11/2009
11:37

eljaywilson

Member

Members

Forum Posts: 22

Member Since:
09/11/2009

Offline

You would use the same methods as for PHP. There will be POST and/or GET server vars passed and you just need to grab those and pass those along as params to an SP or use them in a direct SQL statement (I would go the SP route to keep it secure). You can trust the POST vars, but not any custom GET vars you might be passing.

HTH,

19/11/2009
15:05

eljaywilson

Member

Members

Forum Posts: 22

Member Since:
09/11/2009

Offline

Also - a great tool for debugging request vars (and lots of other stuff) is the Firebug plugin for Firefox. If you ain't using it, you need to be.

19/11/2009
15:24

davidbemenderfer

Member

Members

Forum Posts: 17

Member Since:
10/11/2009

Offline

Thanks. I am using Firebug. Very nice tools. I'm slowly getting up to speed on how to use this tool. I have a JSON table with subtable working now. I have add, update, and delete all working. I have noticed some erratic behavior (the subtable disappears) in ie 7 and ie8 comptability mode (ie7) but great stability in standard ie8 and firefox when adding records to a subtable.

Thanks again.

-David

26/02/2010
19:31

partoo

Member

Members

Forum Posts: 3

Member Since:
25/02/2010

Offline

davidbemenderfer said:

Thanks. I am using Firebug. Very nice tools. I'm slowly getting up to speed on how to use this tool. I have a JSON table with subtable working now. I have add, update, and delete all working. I have noticed some erratic behavior (the subtable disappears) in ie 7 and ie8 comptability mode (ie7) but great stability in standard ie8 and firefox when adding records to a subtable.

Thanks again.

-David

Hi,David~

Can you send me your solution?

My Email:partoo.cn@gmail.com

Thank you so much!

28/02/2010
09:06

partoo

Member

Members

Forum Posts: 3

Member Since:
25/02/2010

Offline

Job done! Laugh

ASP for EditURL:

<%Option Explicit%>

'db conn/open/close

Dim strOper, strID, strNickName, strTitle, strPwd

strOper   = Request("oper")
strID     = Replace(Request("Id"),"'","''")
strTitle  = Replace(Request("Title"),"'","''")
strNickName  = Replace(Request("NickName"),"'","''")
strPwd    = Replace(Request("Pwd"),"'","''")

Select Case strOper
Case "add":
  strSQL = "Insert Into Admin (Title, NickName, Pwd,LastLoginTime) Values('"&strTitle&"', '"&strNickName&"', '"&strPwd&"',Now()) "
Case "edit":
  strSQL = "Update Admin Set Title = '"&strTitle&"', NickName = '"&strNickName&"', Pwd = '"&strPwd&"' Where id = "&strID
Case "del":
  strSQL = "Delete From Admin Where id = "&strID
  End Select

'response.Write strSQL
Dim strSQL,rs

Call OpenDB()
Set rs = Conn.Execute(strSQL)
Call CloseDB()

All RSS

Forum Timezone: Europe/Sofia

Most Users Ever Online: 715

Currently Online:
49 Guest(s)

Currently Browsing this Page:
1 Guest(s)