VBScript 教程之數據庫篇，以 vbscript DBHelper 類的方式，封裝數據庫連接、查詢、基本的存儲過程訪問方法。 option Explicit
' 數據庫讀取選項
Public Const adOpenStatic = 3
Public Const adLockReadOnly = 1
Public Const adLockOptimistic = 3 Public Const adCmdStoredProc = 4
Public Const adInteger=3
Public Const adChar=129
Public Const adVarchar = 200
Public Const adDate=7
Public Const adParamInput=1
Public Const adParamReturnValue=4 Public Const LogTypeInfo = 0
Public Const LogTypeError = 1
Public Const LogTypeWarning = 2 Class DBHelper
??? Private oConn
??? Private Sub Class_Initialize
??????? Set oConn = Nothing
??? End Sub ' ***************************************************************************
??? ' 創建 ADODB.Connection 對象，連接數據庫
??? ' ***************************************************************************
??? Public Function Connect(server, database, uid, password)
??????? Dim sDSNRef
??????? Dim sMsg ' 創建 ADO 數據庫連接對象
??????? On Error Resume Next
??????? Set oConn = CreateObject("ADODB.Connection")
??????? If Err then
??????????? ShowMessage "錯誤 - 無法創建 ADODB.Connection 對象, 不能查詢 SQL Server: " & Err.Description & " (" & Err.Number & ")", LogTypeError
??????????? Set Connect = Nothing
??????????? Exit Function
??????? End If
??????? On Error Goto 0 ' 構建連接字符串
??????? sDSNRef = "Provider=SQLOLEDB;OLE DB Services=0;Data Source=" & server & ";Initial Catalog=" & database
??????? sDSNRef = sDSNRef & ";User ID=" & uid & ";Password=" & password ' 正在連接數據庫
??????? ShowMessage "Connecting to SQL Server using connect string: " & sDSNref, LogTypeInfo
??????? On Error Resume Next
??????? oConn.Open sDSNref
??????? If Err then
??????????? sMsg = Err.Description & " (" & Err.Number & ")" ShowMessage "error opening SQL connection: " & sMsg, LogTypeError iRetVal = Failure
??????????? ShowMessage "error opening SQL Connection: " & sMsg, LogTypeError
??????????? For each objErr in oConn.Errors
??????????????? ShowMessage "? ADO error: " & objErr.Description & " (Error #" & objErr.Number & "; Source: " & objErr.Source & "; SQL State: " & objErr.SQLState & "; NativeError: " & objErr.NativeError & ")", LogTypeError
??????????? Next
??????????? Err.Clear
??????????? Set Connect = Nothing
??????????? Exit Function
??????? End If
??????? On Error Goto 0 ShowMessage "Successfully opened connection to database.", LogTypeInfo ' Return the connection to the caller
??????? Set Connect = oConn
??? End Function
??? ' ***************************************************************************
??? ' 創建 ADODB.Recordset 對象，執行數據庫查詢，返回 Recordset 對象。
??? ' ***************************************************************************
??? Public Function Query(strSQL)
??????? Dim oRS
??????? ' Create ADO recordset object
??????? On Error Resume Next
??????? Set oRS = CreateObject("ADODB.Recordset")
??????? If Err then
??????????? Set Query = Nothing
??????????? ShowMessage "ERROR - Unable to create ADODB.Recordset object, impossible to query SQL Server: " & Err.Description & " (" & Err.Number & ")", LogTypeError
??????????? Exit Function
??????? End If
??????? On Error Goto 0 ' Issue the SQL statement
??????? ShowMessage "About to issue SQL statement: " & strSQL, LogTypeInfo
??????? On Error Resume Next
??????? oRS.Open strSQL, oConn, adOpenStatic, adLockReadOnly
??????? If Err then
??????????? Set Query = Nothing
??????????? ShowMessage "ERROR - Opening Record Set (Error Number = " & Err.Number & ") (Error Description: " & Err.Description & ").", LogTypeError
??????????? For each objErr in oConn.Errors
??????????????? ShowMessage "? ADO error: " & objErr.Description & " (Error #" & objErr.Number & "; Source: " & objErr.Source & "; SQL State: " & objErr.SQLState & "; NativeError: " & objErr.NativeError & ")", LogTypeError
??????????? Next
??????????? oRS.Close
??????????? Err.Clear
??????????? Exit Function
??????? End If
??????? On Error Goto 0 ShowMessage "Successfully queried the database.", LogTypeInfo
??????? Set Query = oRS
??? End Function
??? ' ***************************************************************************
??? ' 創建 ADODB.Command 對象，執行數據庫存儲過程，返回 Command 對象。
??? ' 注意：存儲過程要求，沒有返回值對象，返回值以Select方式返回，存儲過程需要使用
??? ' set nocount on，禁用影響行數消息。
??? ' ***************************************************************************
??? Public Function ExecuteProc(strSQL)
??????? Dim oComm,oRS
??????? ' Create ADO recordset object
??????? On Error Resume Next
??????? Set oComm = CreateObject("ADODB.Command")
??????? If Err then
??????????? Set ExecuteProc = Nothing
??????????? ShowMessage "ERROR - Unable to create ADODB.Command object, impossible to query SQL Server: " & Err.Description & " (" & Err.Number & ")", LogTypeError
??????????? Exit Function
??????? End If
??????? On Error Goto 0
??????? ' 分解參數
??????? Dim sql,cmd,parm,parms,p,index
??????? sql = Split(strSQL," ")
??????? If UBound(sql) = 1 Then
??????????? cmd = sql(0)
??????????? parm = Right(strSQL,Len(strSQL)-Len(cmd))
??????????? parms = Split(parm,",")
??????????? index = 0
??????????? For Each p In parms
??????????????? p = LTrim(Replace(p,"'",""))
??????????????? Dim para
??????????????? Set para = CreateObject("ADODB.Parameter")
??????????????? para.Name = index
??????????????? para.Type = adVarchar
??????????????? para.Size = 1000
??????????????? para.Direction = adParamInput
??????????????? para.Value = p
??????????????? oComm.Parameters.Append para
??????????????? index = index + 1
??????????? Next
??????? Else
??????????? cmd = strSQL
??????? End If ' Issue the SQL statement
??????? ShowMessage "About to issue SQL statement: " & strSQL, LogTypeInfo
??????? On Error Resume Next
??????? oComm.CommandType = adCmdStoredProc
??????? oComm.ActiveConnection = oConn
??????? oComm.CommandText = cmd
??????? Set oRS = oComm.Execute
??????? If Err then
??????????? Set ExecuteProc = Nothing
??????????? ShowMessage "ERROR - Opening Command (Error Number = " & Err.Number & ") (Error Description: " & Err.Description & ").", LogTypeError
??????????? For each objErr in oConn.Errors
??????????????? ShowMessage "? ADO error: " & objErr.Description & " (Error #" & objErr.Number & "; Source: " & objErr.Source & "; SQL State: " & objErr.SQLState & "; NativeError: " & objErr.NativeError & ")", LogTypeError
??????????? Next
??????????? oComm.Close
??????????? Err.Clear
??????????? Set ExecuteProc = Nothing
??????????? Exit Function
??????? End If
??????? On Error Goto 0 ShowMessage "Successfully queried the database.", LogTypeInfo
??????? Set ExecuteProc = oRS
??? End Function
End Class

轉載于:https://blog.51cto.com/qijinchao/263270

超強干貨來襲 云風專訪：近40年碼齡，通宵達旦的技術人生

總結

以上是生活随笔為你收集整理的VBScript 教程之数据库篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。