ASP Ajax Project dengan teknologi Microsoft

Pada project ini kita akan mengambil database yang berada pada SQL Server
dalam bentuk recordset dengan asp.
Pemanggilan Stored Procedure dilakukan dengan object ADO
Agar data ini dapat dikirim ke browser client, kita mengubah recordset ke format XML.
Setelah data sampai di browser client, data xml dapat dimodifikasi dengan mudah
melalui XML.DOM.

Untuk membuat form maupun grid di html dinamis, kita menggunakan file template
kita berinama "customer.xml"

File template yang di bawa ke browser client, diproses oleh JavaScript.
Javascript akan membuat grid beserta dengan pengisian datanya.

1. Membuat Stored Procedure pada SQL Server

CREATE PROCEDURE USP_CUSTOMER_GET
@KODE_CUST VARCHAR(4)
AS

SELECT isnull(M.KODE_CUST,'') AS KODE_CUST ,isnull(M.NAMA_CUST,'') AS NAMA_CUST ,isnull(M.ALAMAT_CUST,'') AS ALAMAT_CUST ,isnull(M.CPERSON,'') AS CPERSON ,isnull(M.ATTN,'') AS ATTN ,isnull(M.TEL,'') AS TEL ,isnull(M.HP,'') AS HP ,isnull(M.SALDO,0) AS SALDO FROM CUSTOMER M

2. Mengambil data dari SQL Server dengan Stored Procedure, melakukan konversi data
recordset menjadi data forma XML dan mengirimnya ke browser cilent.

<%

'----- server side coding asp script
option explicit

Const adUseClient = 3
Const adCmdStoredProc = &H0004
Const adVarChar = 200
Const adParamInput = &H0001
dim lRow, lCol, s

dim oXMLConfig : set oXMLConfig = server.CreateObject("Microsoft.xmlDOM")
oXMLConfig.load server.MapPath("customer.xml")

sub getXMLConfig
   Response.Write oXMLConfig.xml
end sub

Public Function getXMLDB1Par(ByVal sSPName, ByVal sParName, ByVal sParValue)

dim oConn : set oConn = server.CreateObject("ADODB.Connection")
oConn.Open"Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User ID=sa;Initial Catalog = MyDB;DataSource= .\SQLEXPRESS"

dim oCmd : set oCmd = server.CreateObject("ADODB.Command")
dim oRs: set oRs = server.CreateObject("ADODB.recordset")
oConn.cursorLocation= adUseClient
dim oPar1 : Set oPar1 = ocmd.CreateParameter(sParName, adVarChar, adParamInput, 1000, sParValue)
ocmd.ActiveConnection = oConn
ocmd.CommandText = sSPName
ocmd.CommandType = adCmdStoredProc
ocmd.Parameters.Append oPar1
> set ors = ocmd.execute
s = "<mydata>"
for lRow = 0 to oRs.RecordCount - 1
  s = s & "<customer>"
     for lCol = 0 to oRs.Fields.Count - 1
     s = s & "<" & oRs.Fields(lCol).Name & ">" & oRs.Fields(lCol) & "</" & oRs.Fields(lCol).Name & ">"
    next
s = s & "</customer>"
oRs.movenext
next
s = s & "</mydata>"

Response.Write s

End Function

%>

3. Mengambil data dari Script asp di server dan membawanya ke client, dalam format xml,
lalu melekatkannya pada halaman html

<html>
<body>
    <xml id="oXMLConfig"><%getXMLConfig%></xml>
    <xml id=oXMLData><%getXMLDB1Par "USP_CUSTOMER_GET","@KODE_CUST"%> </xml>
<html>
<body>

4. Javascript Script client yang berfungsi untuk membuat tabel dinamis sekaligus mengisi data
dari data format xml yang dibawa dari server.

<script type=text/javascript>

//--- Javascript client script

function Init(){

var oXML = document.getElementById("oXMLConfig");
   var oXMLD = document.getElementById("oXMLData");
   oXMLConfig.loadXML (oXML.innerHTML);
   oXMLData.loadXML (oXMLD.innerHTML);

   Fillgrid();
}

function FillGrid(){
var oNodeData = oXMLData.childNodes(0)
var sCaption = oXMLConfig.childNodes(0).childNodes(0).attributes(3).text
var oNodeConfig = oXMLConfig.childNodes(0).childNodes(0).childNodes(0).childNodes(0);
document.write ("<table border='1' cellspacing='0' cellpadding='0' bordercolor='green' border='0'>"); document.write ("<caption>");

document.write (sCaption)
document.write ("</caption>");
document.write ("<thead bgcolor='cyan'>");
document.write ("<tr>");
for ( var i = 0; i < oNodeConfig.childNodes.length; i++ ) {
      document.write ("<th width='" + oNodeConfig.childNodes[i].attributes[2].text + "'>");
      document.write("<font face='arial' size='2'>" + oNodeConfig.childNodes[i].attributes[1].text + "</font>");

      document.write ("</th>"); } document.write ("</tr>");
      document.write ("</thead>");
      //--- table body
      for ( var lRow = 0; lRow < oNodeData.childNodes.length; lRow++ ) {
          var x = lRow % 2
          var sC
          if (x == 0) {
              sC='yellow'
          }else{
              sC='white'
          }
          document.write("<tr bgcolor='" + sC +"'>");
              for ( var lCol = 0; lCol < oNodeData.childNodes[lRow].childNodes.length; lCol++ ) {
                  document.write("<td width='" + oNodeConfig.childNodes[lCol].attributes[2].text + "' align='" + oNodeConfig.childNodes[lCol].attributes[3].text + "'>");
                  document.write ("<font face='courier'>" + oNodeData.childNodes(lRow).childNodes(lCol).text + "<font>");
           document.write("</td>");
          }
           document.write("</tr>");
          }
          document.write("</table>");

}

</script>

5. File customer.xml yang diletakkandi server berfungsi sebagai template.

<template>
<search name="grid" pageSize="" MDetail="" Judul="C u s t o m e r">
    <grid>
      <columns>
        <column field="KODE_CUST" caption="KODE.CUST" width="1000" align="left" />
        <column field="NAMA_CUST" caption="NAMA.CUST" width="3000" align="left" />
        <column field="ALAMAT_CUST" caption="ALAMAT.CUST" width="4000" align="left" />
        <column field="CPERSON" caption="C.PERSON" width="2000" align="left" />
        <column field="ATTN" caption="ATTN" width="1000" align="left" />
        <column field="TEL" caption="TEL" width="1000" align="right" />
        <column field="HP" caption="HP" width="1000" align="right" />
        <column field="SALDO" caption="SALDO" width="1000" align="right" />
      </columns>
    </grid>
</search> < BR > < BR> <editform name="myform" TblName= "CUSTOMER" OrderFd="KODE_CUST" SPGET="USP_CUSTOMER_GET" SPDel="USP_CUSTOMER_DELETE" spUpdate= "USP_CUSTOMER_UPDATE" TransCode="CS" sKeyFd="KODE_CUST" dbName= "MyDB">< BR>    <control class="textbox" tag= "txtKODE_CUST" field="KODE_CUST" left="2000" top="550" width= "3500" lblCaption="KODE.CUST" dtType="dt_string" dtlength="4" AllowNull= "1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class="textbox" tag="txtNAMA_CUST" field= "NAMA_CUST" left="2000" top="900" width="3500" lblCaption= "NAMA.CUST" dtType="dt_string" dtlength="100" AllowNull="1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class= "textbox" tag="txtALAMAT_CUST" field="ALAMAT_CUST" left="2000" top="1250" width="3500" lblCaption="ALAMAT.CUST" dtType="dt_string" dtlength="100" AllowNull="1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class="textbox" tag="txtCPERSON" field="CPERSON" left="2000" top="1600" width="3500" lblCaption="CPERSON" dtType="dt_string" dtlength="100" AllowNull= "1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class="textbox" tag="txtATTN" field="ATTN" left="2000" top="1950" width="3500" lblCaption= "ATTN" dtType="dt_string" dtlength="100" AllowNull="1" format="" sqlDBType= "VARCHAR"/>   < BR>    <control class="textbox" tag="txtTEL" field="TEL" left= "2000" top="2300" width="3500" lblCaption="TEL" dtType="dt_string" dtlength="100" AllowNull="1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class= "textbox" tag="txtHP" field="HP" left="2000" top="2650" width="3500" lblCaption="HP" dtType="dt_string" dtlength="100" AllowNull="1" format="" sqlDBType= "VARCHAR"/>< BR>    <control class="textbox" tag="txtSALDO" field="SALDO" left="2000" top="2650" width="3500" lblCaption="SALDO" dtType="dt_number" dtlength="0" AllowNull= "1" format="" sqlDBType= "DECIMAL"/>  < BR> </editform>< BR> </template>< BR> < /FONT> < /P>