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.
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.
5. File customer.xml yang diletakkandi server berfungsi sebagai template.<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("" + oNodeConfig.childNodes[i].attributes[1].text + "");
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 ("" + oNodeData.childNodes(lRow).childNodes(lCol).text + "");
document.write("</td>");
}
document.write("</tr>");
}
document.write("</table>");
}
</script>
<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>
No comments:
Post a Comment
Post Comments
Do you have any suggestions ? Add comment. Do not spam!