mdl_sqlconnection.bas

图书馆信息管理系统

Attribute VB_Name = "Mdl_SQLConnection"
Public cnMain As New ADODB.Connection    '主连接
Option Explicit

'连接SQL Server服务器
Public Function SQLConnect(ByVal cnThis As ADODB.Connection, ByVal strServer As String, _
        ByVal strUID As String, ByVal strPassword As String, Optional ByVal strDataBase As String = vbNullString)
    Dim strSQL As String
    '生成连接字符串
    strSQL = "Driver={SQL Server};Server=" & strServer & _
        ";UID=" & strUID & ";PWD=" & strPassword
    If strDataBase = vbNullString Then
        '数据库为空
    Else
        strSQL = strSQL & ";Database=" & strDataBase
    End If
    
    cnThis.ConnectionTimeout = 1
    cnThis.Open strSQL
End Function

'登录模块
Public Function Login(UID As String, PWD As String) As Boolean
On Error Resume Next
    Dim rs As New ADODB.Recordset
    rs.Open "Select * From 账户", cnMain, 1, 1
    
    Do Until rs.EOF
        '用户名比较为文本比较(不区分大小写)
        '密码比较为二进制比较(区分大小写)
        If StrComp(rs("用户名"), Trim(UID), 1) = 0 And StrComp(rs("密码"), PWD, 0) = 0 Then
            Login = True    '登录信息正确
            
            strUID = Trim(UID)
            
            '修改账户表的最后登录信息
            Set rs = New ADODB.Recordset
            rs.Open "SELECT 最后登录时间 FROM 账户 WHERE 用户名 = " & Str2SQL(UID), cnMain, adOpenDynamic, adLockOptimistic
            strLastLogTime = Format(rs("最后登录时间"), "yyyy-mm-dd hh:nn:ss")
            rs("最后登录时间") = Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh:nn:ss")
            rs.Update
            
            '添加登录信息到账户登录表
            cnMain.Execute ("INSERT 账户登录 VALUES (" & Str2SQL(UID) & "," & Str2SQL(Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh:nn:ss")) & ")")
            Call ShowTip(Trim(UID) & "，欢迎使用图书馆信息管理系统！")
            
            Exit Function
        End If
        rs.MoveNext
    Loop
    Login = False
End Function

'用户权限判断
Public Function HavePower(strPower As String) As Boolean
On Error Resume Next
    Dim strSQL As String
    Dim rs As New ADODB.Recordset

    strSQL = "SELECT * FROM 账户,账户类型 WHERE 用户名 = " & Str2SQL(strUID) & " AND 账户.账户类型 = 账户类型.账户类型"
    rs.Open strSQL, cnMain, 1, 1
    
    HavePower = rs(strPower)
End Function

'从数据库中加载数据到表格
Public Function LoadData(ByRef Grid As MSHFlexGrid, ByVal strSQL As String, ByRef Err_Msg As String, Optional ByVal IsShowTip As Boolean) As Boolean
On Error GoTo ERRORZONE
    Dim rs As New ADODB.Recordset
    rs.Open strSQL, cnMain, 1, 1
    With Grid
        .Clear
        If rs.RecordCount = 0 Then
            .Rows = 2    '如果记录集为空则设置为2行(第1行为标题)
            .Cols = rs.Fields.Count
            Dim i As Integer
            For i = 0 To rs.Fields.Count - 1
                .TextMatrix(0, i) = rs(i).Name
            Next i
        Else:
            Set .DataSource = rs    '记录集不为空则绑定到表格控件
        End If
        .Row = 1: .Col = 0     '默认选中第1行
        .ColSel = .Cols - 1    '整行选择
    End With
    
    '是否显示记录集总数到状态栏
    If IsShowTip = True Then Call ShowTip("共有 " & rs.RecordCount & " 条记录")
    
    LoadData = True
    Exit Function
ERRORZONE:
    Grid.Rows = 2
    Err_Msg = Err.Description
    LoadData = False
End Function

'字符串两端加上单引号(变成SQL语句中的字符串)
Public Function Str2SQL(ByVal Source As String, Optional StrType As String) As String
    If StrType = "L" Then
        Source = LCase(Source)
    ElseIf StrType = "U" Then
        Source = UCase(Source)
    End If
    Str2SQL = "'" & Trim(Source) & "'"
End Function