users.cls

Visual Basic + Oracle 9i数据库应用系统开发与实例(项目售后支持管理系统),界面简洁大方,功能完善,数据库完整,适合学生学习利用~

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Users"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 UserName  Char  用户名
'2 UserPwd Char  密码
'3 UserType  Tinyint 用户类型（1 - 系统管理员，2 - 经理用户，3 － 工程师用户）
'4 Ename Varchar 用户姓名
'5 Email Varchar 电子邮箱

Public UserName As String
Public UserPwd As String
Public UserType As Integer
Public Ename As String
Public Email As String

Public Sub Init()
  UserName = ""
  UserPwd = ""
  UserType = 0
  Ename = ""
  Email = ""
End Sub

'删除Users数据
Public Sub Delete(ByVal TmpUser As String)
  If TmpUser = "Admin" Then
    Exit Sub
  End If
  
  DB_Connect
  
  SqlStmt = "DELETE FROM Users WHERE UserName='" _
          + Trim(TmpUser) + "'"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Sub GetInfo(ByVal TmpUser As String)
  UserName = TmpUser
  If TmpUser = "" Then
    Init
    Exit Sub
  End If
  DB_Connect
  
  SqlStmt = "SELECT * FROM Users WHERE UserName='" _
          + Trim(TmpUser) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    UserPwd = TrimStr(ColVal)
    
    ColVal = String(4, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    UserType = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    Ename = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Len(ColVal), pcblen)
    Email = TrimStr(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetPwd(ByVal TmpUser As String) As String
  UserName = TmpUser
  
  DB_Connect
  
  SqlStmt = "SELECT UserPwd FROM Users WHERE UserName='" _
          + Trim(TmpUser) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetPwd = ""
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblen)
    Pwd = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetPwd = Pwd
  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpUser As String) As Boolean
  '连接数据库
  DB_Connect
  '设置SELECT语句,读取指定用户名对应的记录
  SqlStmt = "SELECT * FROM Users WHERE UserName='" _
          + Trim(TmpUser) + "'"
  '执行SQL语句
  OdbcExt (SqlStmt)
  '从结果集中读取数据，如果有数据，则IN_DB = True；否则In_DB = False
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    In_DB = False
  Else
    In_DB = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Function

Public Sub Insert()
  DB_Connect
  
  SqlStmt = "INSERT INTO Users Values('" _
     + Trim(UserName) + "','" + Trim(UserPwd) _
     + "'," + Trim(Str(UserType)) + ",'" + Trim(Ename) _
     + "','" + Trim(Email) + "')"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'根据用户类型读取员工姓名到Arr_Ename()数组中
Public Function LoadEname(ByVal TmpType As Integer) As String
  Dim i As Integer
  '初始化员工姓名数组
  Erase Arr_Ename
  ReDim Arr_Ename(0)
  '连接数据库
  DB_Connect
  '设置SQL语句
  SqlStmt = "SELECT Ename FROM Users WHERE UserType=" _
          + Trim(Str(TmpType))
  '执行SQL语句
  OdbcExt (SqlStmt)
  '把结果集中的员工姓名字段依次添加到Arr_Ename()数组中
  i = 0
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblen)
    ReDim Preserve Arr_Ename(i + 1)
    Arr_Ename(i) = TrimStr(ColVal)
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Function

Public Sub Update(ByVal TmpUser As String)
  DB_Connect
  
  SqlStmt = "Update Users Set UserName='" + Trim(UserName) _
          + "',UserPwd='" + Trim(UserPwd) + "',UserType=" _
          + Trim(Str(UserType)) + ",Ename='" _
          + Trim(Ename) + "',Email='" + Trim(Email) _
          + "' WHERE UserName='" + Trim(TmpUser) + "'"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub