ASP SQL Server Used Function Database Query |
ASP SQL Server Used Function Database Query ตัวอย่างนี้ผมได้ยกตัวอย่างการเขียน ASP ติดต่อกับ SQL Server โดยใช้ Function เข้ามาช่วยจัดการในด้านการเพิ่ม/ลบ/แก้ไข เพื่อความสะดวกและง่ายต่อการแก้ไขหรือพัฒนาโปรแกรมครับ
ตัวอย่าง
AspSQLServerFunctionDatabase.asp
<%
'*** By ThaiCreate.Com ***'
'*** By @W_IN ***'
Const strHost = "localhost"
Const strUserID = "sa"
Const strPassword = ""
Const strDatabase = "mydatabase"
'*** Connection String ***'
Function ConnectionString()
ConnectionString = "Driver={SQL Server};Server="&strHost&"; " & _
"Database="&strDatabase&";UID="&strUserID&";PWD="&strPassword&";"
End Function
'*** Excute & objRec (Dynamic RecordSet) ***'
Function fncExecuteSQLDynamic(strSQL,objRec)
Dim Conn
Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open ConnectionString,strUserID,strPassword
Set objRec = Server.CreateObject("ADODB.Recordset")
objRec.Open strSQL,Conn,1,3
Open.Close()
Set Open = Nothing
End Function
'*** Excute & objRec (Static RecordSet) ***'
Function fncExecuteSQLStatic(strSQL,objRec)
Dim Conn
Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open ConnectionString,strUserID,strPassword
Set objRec = Conn.Execute(strSQL)
Open.Close()
Set Open = Nothing
End Function
'*** Excute & Return (True/False) ***'
Function fncExecuteSQL(strSQL)
On Error Resume Next
Dim Conn,objExec
Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open ConnectionString,strUserID,strPassword
Set objExec = Conn.Execute(strSQL)
If Err.Number = 0 Then
fncExecuteSQL = True
Else
fncExecuteSQL = False
End If
Set objExec = Nothing
Open.Close()
Set Open = Nothing
End Function
'*** Function Insert Record ***'
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 ***'
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 ***'
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 ***'
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 ***'
Function fncDeleteRecord(strTable,strCondition)
On Error Resume Next
Dim strSQL
strSQL = "DELETE FROM "&strTable&" WHERE "&strCondition
fncDeleteRecord = fncExecuteSQL(strSQL)
End Function
%>
AspSQLServerUsedFunctionDatabase.asp
<% Option Explicit %>
<html>
<head>
<title>ThaiCreate.Com ASP & SQL Server Tutorial</title>
</head>
<body>
<!--#include file="ASPSQLServerFunctionDatabase.asp"-->
<%
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 = 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 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 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 = 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 = fncDeleteRecord(strTable,strCondition)
If objDelete = True Then
Response.write("Record deleted.<br>")
Else
Response.write("Record not delete.<br>")
End IF
%>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 02
