ASP SQL Server Database Class |
ASP SQL Server Database Class ตัวอย่างนี้ผมได้ยกตัวอย่างการเขียน ASP ติดต่อกับ Access โดยใช้ Class เข้ามาช่วยจัดการในด้านการเพิ่ม/ลบ/แก้ไข เพื่อความสะดวกและง่ายต่อการแก้ไขหรือพัฒนาโปรแกรมครับ
ตัวอย่าง
AspSQLServerFunctionClassDatabase.asp
<%
'*** By ThaiCreate.Com ***'
'*** By @W_IN ***'
Const strHost = "localhost"
Const strUserID = "sa"
Const strPassword = ""
Const strDatabase = "mydatabase"
Class MyDatabase
Private Conn
'******** Function Database Access *******'
'*****************************************'
'*** Auto Onload Class (Auto Connection) ***'
Private Sub Class_Initialize()
Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open "Driver={SQL Server};Server="&strHost&"; " & _
"Database="&strDatabase&";UID="&strUserID&";PWD="&strPassword&";"
End Sub
'*** Excute & objRec (Dynamic RecordSet) ***'
Public Function fncExecuteSQLDynamic(strSQL,objRec)
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open strSQL,Conn,1,3
End Function
'*** Excute & objRec (Static RecordSet) ***'
Public Function fncExecuteSQLStatic(strSQL,objRec)
Set objRec = Conn.Execute(strSQL)
End Function
'*** Excute SQL (True/False) ***'
Public Function fncExecuteSQL(strSQL)
Dim objExec
Set objExec = Conn.Execute(strSQL)
If Err.Number = 0 Then
fncExecuteSQL = True
Else
fncExecuteSQL = False
End If
Set objExec = Nothing
End Function
'*** Auto OnUnload Class (Auto Disconnect) ***'
Private Sub Class_Terminate()
Conn.Close()
Set Conn = Nothing
End Sub
'*****************************************'
'*****************************************'
'******* Function Database Command *******'
'*****************************************'
'*** Function Insert Record ***'
Public Function fncInsertRecord(strTable,strField,strValue)
On Error Resume Next
Dim strSQL
strSQL = "INSERT INTO "&strTable&" ("&strField&") VALUES ("&strValue&")"
fncInsertRecord = fncExecuteSQL(strSQL)
End Function
'*** Function List Record ***'
Public Function fncListRecord(objRec,strTable,strCondition)
On Error Resume Next
Dim strSQL
strSQL = "SELECT * FROM "&strTable&" WHERE "&strCondition
Call fncExecuteSQLDynamic(strSQL,objRec)
End Function
'*** Function Select Record ***'
Public Function fncSelectRecord(objRec,strTable,strCondition)
On Error Resume Next
Dim strSQL
strSQL = "SELECT * FROM "&strTable&" WHERE "&strCondition
Call fncExecuteSQLStatic(strSQL,objRec)
End Function
'*** Function Update Record ***'
Public Function fncUpdateRecord(strTable,strCommand,strCondition)
On Error Resume Next
Dim strSQL
strSQL = "UPDATE "&strTable&" SET "&strCommand&" WHERE "&strCondition
fncUpdateRecord = fncExecuteSQL(strSQL)
End Function
'*** Function Delete Record ***'
Public Function fncDeleteRecord(strTable,strCondition)
On Error Resume Next
Dim strSQL
strSQL = "DELETE FROM "&strTable&" WHERE "&strCondition
fncDeleteRecord = fncExecuteSQL(strSQL)
End Function
'*****************************************'
'*****************************************'
End Class
%>
AspSQLServerUsedFunctionClassDatabase.asp
<% Option Explicit %>
<html>
<head>
<title>ThaiCreate.Com ASP & SQL Server Tutorial</title>
</head>
<body>
<!--#include file="ASPSQLServerFunctionClassDatabase.asp"-->
<%
Dim myDB
Set myDB = New MyDatabase
Dim strTable,strField,strValue
'**** Call to function insert record ****'
Dim objInsert
strTable = "customer"
strField = "CustomerID,Name,Email,CountryCode,Budget,Used"
strValue = " 'C005','Weerachai Nukitram','[email protected]','TH','2000000','0' "
objInsert = myDB.fncInsertRecord(strTable,strField,strValue)
If objInsert = True Then
Response.write("Record inserted.<br>")
Else
Response.write("Record already exist.<br>")
End IF
Response.write("<br>===========================<br>")
'**** Call to function list record ****'
Dim objList,strCondition
strTable = "customer"
strCondition = " 1=1 "
Call myDB.fncListRecord(objList,strTable,strCondition)
If objList.EOF Then
Response.write("Record not found<br>")
Else
Response.write("Customer List.<br>")
objList.MoveFirst
While Not objList.EOF
Response.write(""&objList.Fields("CustomerID").Value&"")
Response.write(", "&objList.Fields("Name").Value&"")
Response.write(", "&objList.Fields("Email").Value&"")
Response.write(", "&objList.Fields("CountryCode").Value&"")
Response.write(", "&objList.Fields("Budget").Value&"")
Response.write(", "&objList.Fields("Used").Value&"<br>")
objList.MoveNext
Wend
End If
objList.Close()
Set objList = Nothing
Response.write("<br>===========================<br>")
'**** Call to function select record ****'
Dim objSelect
strTable = "customer"
strCondition = " CustomerID = 'C005' "
Call myDB.fncSelectRecord(objSelect,strTable,strCondition)
If objSelect.EOF Then
Response.write("Record not found<br>")
Else
Response.write("Customer Detail.<br>")
Response.write("CustomerID = "&objSelect.Fields("CustomerID").Value&"<br>")
Response.write("Name = "&objSelect.Fields("Name").Value&"<br>")
Response.write("Email = "&objSelect.Fields("Email").Value&"<br>")
Response.write("CountryCode = "&objSelect.Fields("CountryCode").Value&"<br>")
Response.write("Budget = "&objSelect.Fields("Budget").Value&"<br>")
Response.write("Used = "&objSelect.Fields("Used").Value&"<br>")
End If
objSelect.Close()
Set objSelect = Nothing
Response.write("<br>===========================<br>")
'**** Call to function update record ****'
Dim strCommand,objUpdate
strTable = "customer"
strCommand = " BUDGET = '4000000' "
strCondition = " CustomerID = 'C005' "
objUpdate = myDB.fncUpdateRecord(strTable,strCommand,strCondition)
If objUpdate = True Then
Response.write("Record updated.<br>")
Else
Response.write("Error update record.<br>")
End IF
Response.write("<br>===========================<br>")
'**** Call to function delete record ****'
Dim objDelete
strTable = "customer"
strCondition = " CustomerID = 'C005' "
objDelete = myDB.fncDeleteRecord(strTable,strCondition)
If objDelete = True Then
Response.write("Record deleted.<br>")
Else
Response.write("Record not delete.<br>")
End IF
Set myDB = Nothing
%>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 03
