clsado.cls

ＶＢ实现成绩管理系统．包括成绩录入与试卷评审等．

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsADO"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit


'数据
Private strConn As String '保存连接字符串
Private objConn As ADODB.Connection '对象Connection的引用
Private objComm As ADODB.Command '对象Command的引用
Private objRs As ADODB.Recordset '对象Recordset的引用

'方法
Public Sub SetConnectionString(ByVal ConnectionString As String)
'该过程设置一个连接字符串，当调用OpenDB函数使用缺省参数时，使用该字符串连接数据库
    
    strConn = ConnectionString
    
End Sub

Public Sub SetSQLDBName(ByVal Host As String, ByVal DBName As String, ByVal UserID As String, ByVal Pwd As String)
    strConn = "Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=" & UserID & ";Initial Catalog=cake;Data Source=" & Host
End Sub

Public Sub SetAccessDBName(ByVal DBName As String, Optional ByVal Password As String)
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";Persist Security Info=False;Jet OLEDB:Database Password=" & Password
End Sub

Public Function OpenDB(Optional ByVal ConnectionString As String) As Boolean
'该函数创建一个Conncetion对象，并建立数据连接。如果建立连接返回True，否则返回False
    
    On Error GoTo Err '无法建立连接时的错误处理
    If ConnectionString <> "" Then strConn = ConnectionString '若指定参数，修改连接字符串
    
    If Not objConn Is Nothing Then '判断数据库是否已打开
        objConn.Close
    Else
        Set objConn = New ADODB.Connection '创建Connction对象
    End If
    objConn.ConnectionString = strConn '设置连接字符串
    objConn.Open '建立数据库连接，若失败则跳转至Err
    OpenDB = True
Err:
End Function


Public Sub CloseDB()
'该函数关闭数据库连接，并销毁Connection对象

    If Not objConn Is Nothing Then
        objConn.Close '关闭数据库连接
        Set objConn = Nothing '销毁Connection对象
    End If
End Sub

Public Function ExecuteCommand(ByVal SQLString As String) As Boolean
'该过程执行SQLString包含的SQL语句，不返回数据集
    If objConn Is Nothing Then Exit Function
    Set objComm = New ADODB.Command '创建Command对象
    objComm.ActiveConnection = objConn
    objComm.CommandText = SQLString
    On Error GoTo Err_Label
    objComm.Execute , , adCmdText Or adExecuteNoRecords '执行SQL语句
    ExecuteCommand = True
Exit_Label:
    Set objComm = Nothing '销毁Command对象
    Exit Function
Err_Label:

    Resume Exit_Label
End Function


Public Function CreateRecordset(ByVal SQLString As String) As ADODB.Recordset
'该函数执行SQLString包含的SQL语句，返回Recordset对象(结果记录集)
    If objConn Is Nothing Then Exit Function
    On Error GoTo Err_Label
    Set objRs = New ADODB.Recordset '创建Recordset对象
    objRs.Open SQLString, objConn, adOpenStatic, adLockOptimistic, adCmdText '执行SQL语句
    Set CreateRecordset = objRs '返回Recordset对象
    
Exit_Label:
    Set objRs = Nothing
    Exit Function
Err_Label:
    Set CreateRecordset = Nothing
    Resume Exit_Label
End Function

Public Function SQLFilter(ByRef s As String) As String
    Dim ret As String
    ret = Replace(s, "'", "''")
    SQLFilter = ret
End Function