moddbsql.bas

这是图书馆管理系统

Attribute VB_Name = "modDBSQL"
Public g_DBCon As New ADODB.Connection
Public rs As New ADODB.Recordset


Public Function ConnectToServer() As Boolean
'建立、连接数据库函数
    On Error GoTo CONNECTERR
    
        '连接到SQL Server数据库master
        g_DBCon.ConnectionString = "provider=sqloledb; data source=127.0.0.1;" & _
                                "initial catalog=master;integrated security=sspi"
        g_DBCon.ConnectionTimeout = 30      '30秒等待时间
        g_DBCon.Open                        '打开连接
        
        '判断数据库是否存在
        rs.Open "select * from sysdatabases where name='x'", g_DBCon, adOpenStatic
        If rs.BOF = True And rs.EOF = True Then
            '建立数据库X
            g_DBCon.Execute ("create database x")
            
            g_DBCon.Close
            If DisConnect() = True Then     '判断，如果关闭连接成功
                '连接到SQL Server数据库X
                g_DBCon.ConnectionString = "provider=sqloledb; data source=127.0.0.1;" & _
                                        "initial catalog=x; integrated security=sspi"
                g_DBCon.ConnectionTimeout = 30      '30秒等待时间
                g_DBCon.Open                '打开连接
            End If
            
            '建立表、导入原始数据
            If IniDB = False Then End               '如果建立表失败，退出程序
            If IniTable = False Then End            '如果导入原始数据失败，退出程序
        Else
        
            g_DBCon.Close
            If DisConnect() = True Then     '判断，如果关闭连接成功
                '连接到SQL Server数据库X
                g_DBCon.ConnectionString = "provider=sqloledb; data source=127.0.0.1;" & _
                                        "initial catalog=x; integrated security=sspi"
                g_DBCon.ConnectionTimeout = 30      '30秒等待时间
                g_DBCon.Open                '打开连接
        
            End If
        
        End If
        
        ConnectToServer = True
        Exit Function
'错误处理
CONNECTERR:
    ConnectToServer = False
    MsgBox "错误代码：" & Err.Number & vbCrLf & _
            "错误描述：" & Err.Description, vbCritical + vbOKOnly, "连接错误"
End Function

Public Function ExecuteSQL(ByVal strSQL As String) As Boolean
'执行SQL的函数
    On Error Resume Next
        g_DBCon.Execute (strSQL)
        If Err.Number > 0 Then
            MsgBox "错误代码：" & Err.Number & vbCrLf & _
                "错误描述：" & Err.Description, vbCritical + vbOKOnly, "连接错误"
            Err.Clear
            ExecuteSQL = False
        Else
            ExecuteSQL = True
        End If
End Function


Public Function DisConnect() As Boolean
'关闭连接的函数
    On Error Resume Next
        '如果数据库以连接，关闭连接
        If g_DBCon.State = adStateOpen Then
            g_DBCon.Close
        End If
        DisConnect = True
End Function



Public Function IniDB() As Boolean
'建立表函数
    On Error GoTo ON_DBERR
        '建立图书信息表
        g_DBCon.Execute ("create table BookMessage (BookIndex int identity(1,1),BookName varchar(50),Author varchar(50),Publish varchar(50),Numbers int,Price smallmoney,JoinTime datetime,BorrowDegree int default 0,Types varchar(10),States bit)")
        '建立读者信息表
        g_DBCon.Execute ("create table ReaderMessage (ReaderIndex int identity(1,1),ReaderName varchar(50),Age int,Sex bit,Duty bit,BorrowBookDegree int default(0))")
        '建立借阅信息表
        g_DBCon.Execute ("create table BorrowMessage(BorrowIndex int identity(1,1),BookIndex int,ReaderIndex int,BorrowTime datetime,ReturnTime datetime,ReturnTimes datetime,States bit default(0))")
        '建立用户登陆表
        g_DBCon.Execute ("create table UserValidate(UserName varchar(20),UserPWD varchar(20),UserPopedom bit)")
        IniDB = True        '建立表成功，返回TRUE
        Exit Function
'错误处理
ON_DBERR:
        MsgBox "错误代码：" & Err.Number & vbCrLf & _
            "错误描述：" & Err.Description, vbCritical + vbOKOnly, "错误"
        IniDB = False       '建立表失败，返回FALSE
End Function


Public Function IniTable() As Boolean
'导入原始数据函数
    On Error GoTo ON_DBERR
        '输入原始数据
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('关系型数据库及SQL语言','APTECH','北大青鸟',10,30,'2003-1-1',0,'图书',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('网页编程','APTECH','北大青鸟',12,40,'2003-2-1',0,'图书',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('VB可视化编程','APTECH','北大青鸟',15,45,'2003-2-10',0,'图书',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('生活报2004年11月30日刊','报业集团','报业集团',50,0.5,'2004-11-30',0,'报纸',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('新晚报2004年12月3日刊','报业集团','报业集团',80,0.5,'2004-12-3',0,'报纸',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('微型计算机2004年12月（上）','微型计算机编辑部','微型计算机编辑部',20,6,'2004-12-5',0,'杂志',1)")
        g_DBCon.Execute ("insert BookMessage(BookName,Author,Publish,Numbers,Price,JoinTime,BorrowDegree,Types,States)" & _
                                    "values('程序员2004年11月刊','程序员编辑部','程序员编辑部',20,8,'2004-11-5',0,'杂志',1)")
        
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('李树鹏',23,1,0)")
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('毕肆海',25,1,1)")
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('王慧',21,0,0)")
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('宋丹龙',22,1,0)")
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('李家励',24,1,0)")
        g_DBCon.Execute ("insert ReaderMessage(ReaderName,Age,Sex,Duty)values('彭甘翔',16,1,0)")
        
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('Administrator','123',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('李树鹏','lsp',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('毕肆海','bsh',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('王慧','wl',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('宋丹龙','sdl',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('李家励','ljl',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('彭甘翔','pgx',1)")
        g_DBCon.Execute ("insert UserValidate(UserName,UserPWD,UserPopedom)" & _
                                    "values('user','user',0)")
                                    
        IniTable = True        '导入原始数据成功，返回TRUE
        Exit Function
'错误处理
ON_DBERR:
        MsgBox "错误代码：" & Err.Number & vbCrLf & _
            "错误描述：" & Err.Description, vbCritical + vbOKOnly, "错误"
        IniTable = False       '导入原始数据失败，返回FALSE
End Function