Posted by: harimanto | April 17, 2009

jqGrid Example with ASP Classic and MySQL

jqGrid Example with ASP Classic and MySQL

uomlist

Download jQuery Grid Plugin

tblmuom.sql

CREATE TABLE `tblmuom` (
`UOM` varchar(10) NOT NULL,
`Nama` varchar(50) default NULL,
`Keterangan` varchar(50) default NULL,
`CreatedBy` varchar(50) default NULL,
`CreatedDate` datetime default NULL,
`LastUpdatedBy` varchar(50) default NULL,
`LastUpdatedDate` datetime default NULL,
PRIMARY KEY  (`UOM`)
)

dbconfig.asp

<script LANGUAGE="VBScript" RUNAT="Server">
Dim rs, dbConn, strConn

Function OpenDB()
  Set dbConn = Server.CreateObject("ADODB.Connection")
  strConn = "DRIVER={MySQL ODBC 5.1 Driver};"
  strConn = strConn & "SERVER=" & "localhost" & ";PORT=3306;"
  strConn = strConn & "DATABASE=" & "yourdatabase" & ";"
  strConn = strConn & "UID=" & "root" & ";"
  strConn = strConn & "PASSWORD=" & "yourpassword"& ";"
  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

</script>

UOMList.html

< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>UoM List</title> 
<style>
html, body {
	margin: 0;			/* Remove body margin/padding */
	padding: 0;
	overflow: auto;	/* Remove scroll bars on browser window */	
	font: 12px "Lucida Grande", "Lucida Sans Unicode", Tahoma, Verdana;
}
</style>
<link rel="stylesheet" type="text/css" media="screen" href="themes/basic/grid.css" /> 
<link rel="stylesheet" type="text/css" media="screen" href="themes/jqModal.css" /> 
<script src="jquery.js" type="text/javascript"></script> 
<script src="jquery.jqGrid.js" type="text/javascript"></script> 
<script src="js/jqModal.js" type="text/javascript"></script> 
<script src="js/jqDnR.js" type="text/javascript"></script> 
<script type="text/javascript"> 
jQuery(document).ready(function(){ 
jQuery("#list").jqGrid({ 
url:'UOMList.asp', 
datatype: 'xml',
mtype: 'GET', 
colNames:['ID','Name', 'Description'], 
colModel :[ 
{name:'uom', index:'uom', width:120,editable:true,editoptions:{readonly:false,size:20},editrules:{required:true}}, 
{name:'nama', index:'nama', width:300, editable:true,editoptions:{size:80},editrules:{required:true}}, 
{name:'keterangan', index:'keterangan', width:200, editable:true,editoptions:{size:80}}], 
pager: jQuery('#pager'), 
rowNum:10, rowList:[10,20,30], 
sortname: 'uom', 
sortorder: "asc", 
viewrecords: true, 
imgpath: 'themes/basic/images', 
caption: 'Unit of Measurement (UoM) List',
height:300,
multiselect:false,
editurl:"UOMUpdate.asp" 
}).navGrid('#pager',{}, //options
{height:150,width:500,reloadAfterSubmit:true}, // edit options
{height:150,width:500,reloadAfterSubmit:true}, // add options
{reloadAfterSubmit:true}, // del options
{} // search options
); 
}); 
</script> 
</head> 
<body> 
<table id="list" class="scroll"></table> 
<div id="pager" class="scroll" style="text-align:right;"></div> 
</body> 
</html>

UOMList.asp

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

<!-- #include file ="dbconfig.asp" -->

< %
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 	= CInt(Request("page"))
strLimit	= CInt(Request("rows"))
strIdx		= Request("sidx")
strOrd		= Request("sord")		

strSearchOn	= Request("_search")
If (strSearchOn = "true") Then
strField	= Request("searchField")
	If (strField = "uom" Or strField = "nama" Or strField = "keterangan") 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

Call OpenDB()

strSQL = "Select Count(*) As count From TblMUOM"
'response.write strSQL
Set rs = dbConn.Execute(strSQL)

strCount = CInt(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 uom, nama, keterangan From TblMUOM "&strWhere&" "
strSQL = strSQL & "ORDER BY "&strIdx&" "&strOrd&" LIMIT "&strStart&" , "&strLimit&" "
Set rs = dbConn.Execute(strSQL)
'response.Write strSQL

Response.Write ("<?xml version='1.0' encoding='utf-8'?>")
Response.Write "<rows>" 
Response.Write "<page>"&strPage&"</page>"
Response.Write "<total>"&strTotalPages&"</total>"
Response.Write "<records>"&strCount&"</records>"

Do While Not rs.Eof

Response.contenttype = "text/xml" 
Response.Write "<row id='"& rs("uom") &"'>"
Response.Write "<cell>" & rs("uom") &"</cell>"
Response.Write "<cell>" & rs("nama") &"</cell>"
response.Write "<cell>< ![CDATA[" & rs("keterangan") & "]]></cell>"
Response.Write "</row>"

rs.MoveNext
Loop

Response.Write "</rows>"
Call CloseDB()
%>

UOMUpdate.asp

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

<!-- #include file ="dbconfig.asp" -->

< %

Dim strOper, strID, strNama, strKeterangan, strUOM, strEditor, strHost
Dim strSQL

strOper			= Request("oper")
strID			= Replace(Request("id"),"'","''")		
strUOM			= Replace(Request("uom"),"'","''")
strNama			= Replace(Request("nama"),"'","''")
strKeterangan	= Replace(Request("keterangan"),"'","''")		
strEditor 		= Session("SUserID")
strHost			= Request.ServerVariables("REMOTE_HOST")
strEditor 		= strEditor &" @ "& strHost

Select Case strOper
	Case "add": 'Add Record
		strSQL = "Insert Into TblMUOM (UOM, Nama, Keterangan, CreatedBy, LastUpdatedBy, CreatedDate, LastUpdatedDate) "
		strSQL = strSQL & "Values('"&strUOM&"', '"&strNama&"', '"&strKeterangan&"', '"&strEditor&"', '"&strEditor&"', Now(), Now()) "
	Case "edit": 'Edit Record
		strSQL = "Update TblMUOM Set Nama = '"&strNama&"', Keterangan = '"&strKeterangan&"', "
		strSQL = strSQL & "LastUpdatedBy = '"&strEditor&"', LastUpdatedDate = Now() "
		strSQL = strSQL & "Where UOM = '"&strID&"' "
	Case "del": 'Delete Record
		strSQL = "Delete From TblMUOM Where UOM = '"&strID&"' "
End Select

'response.Write strSQL

Call OpenDB()
Set rs = dbConn.Execute(strSQL)
Call CloseDB()
%>

About these ads

Responses

  1. Congratulations on your first POST.
    Great.

    I “copy” the part of select case search
    I think this is the first (complet)e example of classic ASP + JQUERY.

  2. Yes, great job. i haven’t tried your codes yet but, i think you worked a lot for this job. So, thanks, really!

  3. Muchisimas gracias me sirvio de gran ayuda.

  4. Hello there ! Thank you for great code. I was trying to use your code for sql server 2005 and it somehow does not work. May i contact you with questions ?

  5. very nice! Can you provide a sample with JSON instead of xml? Thanks in advance!

  6. Greetings…
    I figured out a way to do the same thing for SQL Server. Thought I’d share.

    Three things to change.
    1. DB connection string to SQL Server

    In UOMList.asp

    2. Change Line 38 from: strWhere = ” Where ” & strField

    to: strWhere = ” and ” & strField

    3. Change your SQL String:
    strSQL = “select uom, nama, keterangan, sq.rn”
    strSQL = strSQL & ” from ( select top “& (strLimit+strStart) &” t.*, row_number() over (order by uom) rn”
    strSQL = strSQL & ” from TblMUOM t”
    strSQL = strSQL & ” order by uom”
    strSQL = strSQL & ” ) sq ”
    strSQL = strSQL & “where sq.rn > “& strStart & strWhere

    The LIMIT function does not work in SQL Server

    Hope this helps all of you who are pushing SQL Server 2005 or 2008.

    Cheers.

    • Hi I tried your SQL query for MS SQL Server 2008…it kind of work, in the sense that it does return the limit, but how do u get it to paginate? When I use ur version of the query, it only returns 10 records (the limit), but not all the records? What I’m i missing?

      • nevermind…i got it to work…ur query works perfectly – thanks 4 sharing!

  7. Hola e intentado levantar este demo y no e logrado hacerlo funcionar.
    Tu me podrias dar un ejemplo de este funcionando??

    Saludos

    • el ejemplo esta aqui mismo…danos tu “code” para ver lo que pasa.

  8. Hola Trate de hacerlo funcionar pero no pude
    subi el ejemplo a esta direccio ojala me puedan dar una mano gracias!
    http://www.megaupload.com/?d=1MSHCK20

  9. keep getting this error message when trying to load uomlist.html – any idea of what it means ?

    ===============================
    jQuery(“#list”).jqGrid({url: “UOMList.asp”, datatype: “xml”, mtype: “GET”, colNames: ["ID", "Name", "Description"], colModel: [{name: "uom", index: "uom", width: 120, editable: true, editoptions: {readonly: false, size: 20}, editrules: {required: true}}, {name: "nama", index: "nama", width: 300, editable: true, editoptions: {size: 80}, editrules: {required: true}}, {name: "keterangan", index: "keterangan", width: 200, editable: true, editoptions: {size: 80}}], pager: jQuery(“#pager”), rowNum: 10, rowList: [10, 20, 30], sortname: “uom”, sortorder: “asc”, viewrecords: true, imgpath: “themes/basic/images”, caption: “Unit of Measurement (UoM) List”, height: 300, multiselect: false, editurl: “UOMUpdate.asp”}).navGrid is not a function
    ============================

  10. Could you please add a sample download here? Will be great .. as some css, js can’t find

  11. Great items from you, man. I have keep in mind your stuff prior to and you are simply extremely wonderful.
    I actually like what you’ve received here, certainly like what you are saying and the way during which you assert it. You’re making it enjoyable and you still take care of to stay it wise.
    I cant wait to read far more from you. This is actually a great site.

  12. Hey there, You have done an excellent job. I’ll definitely digg it and personally suggest to my friends. I am confident they’ll
    be benefited from this site.

  13. good … but did you think about sql injection when you use the searched value as a string? Exemple, i can call this from internet explorer and insert in some request something like this :
    x'; DROP TABLE TblMUOM;
    or if i drop your aspnet_user table …
    To avoid this use sqlParameter and stored procedure when adding or edditing row from a table …

    sql Injection is explain here :
    http://www.unixwiz.net/techtips/sql-injection.html


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: