ado.bas

学生宿舍管理系统vb源码

Attribute VB_Name = "ado"

Public MsgString As String  '查询返回语句变量

Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
    '执行SQL查询并返回记录集
    Dim cnn As ADODB.Connection '定义数据库连接变量
    Dim rst As ADODB.Recordset  '定义数据库记录集变量
    Dim sTokens() As String

    On Error GoTo ExecuteSQL_Error  '错误处理机制

    sTokens = Split(SQL)    'Split函数返回一个下标从零开始的一维数组，它包含指定数目的子字符串
    Set cnn = New ADODB.Connection
    cnn.ConnectionTimeout = 25
    cnn.Provider = "SQLOLEDB"                               '数据库连接驱动程序
    cnn.Properties("Data Source").Value = "127.0.0.1"     '数据源
    cnn.Properties("Initial Catalog").Value = "ssgl"        '数据库名称
    cnn.Properties("User Id").Value = "grace"               '数据库登录用户名
    cnn.Properties("Password").Value = "grace"              '数据库登录密码
    cnn.Open        '打开数据库连接
   
    If InStr("INSERT,DELETE,UPDATE,CREATE", UCase$(sTokens(0))) Then
        cnn.Execute SQL
        MsgString = sTokens(0) & " query successful"
    Else
        Set rst = New ADODB.Recordset
        rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockOptimistic    '执行查询
      'rst.MoveLast     'get RecordCount
        Set ExecuteSQL = rst    '返回记录集
        MsgString = "查询到" & rst.RecordCount & " 条记录 "
    End If
ExecuteSQL_Exit:
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function

ExecuteSQL_Error:
    MsgString = "查询错误: " & Err.Description
    Resume ExecuteSQL_Exit
End Function