employeeinfo.cls

sql的实例源码

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "EmployeeInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 ENo 文本，关键字，字段大小：10，标题：员工编号 必填字段：是，允许空字符串：否，输入法模式：关闭
'2 Name 文本，字段大小：20，标题：姓名，必填字段（是），允许空字符串（否），输入法模式：开启
'3 Sex 文本，字段大小：2，标题：性别
'4 IDCard 文本，字段大小：18，标题：身份证，必填字段：是，允许空字符串：否，输入法模式：关闭
'5 NativePlace 文本，字段大小：50，标题：籍贯
'6 Residence 文本，字段大小：50，标题：户口所在地
'7 Marry 文本，字段大小：10，标题：婚姻状况
'8 Birthday 日期/时间，标题：出生年月
'9 Folk 文本，字段大小：10，标题：民族
'10 Party 文本，字段大小：20，标题：政治面貌
'11 Education 文本，字段大小：6，标题：文化程度
'12 DeptID 数字，整型，小数位数：0，标题：部门，默认值：0
'13 Tele 文本，字段大小：20，标题：固定电话，输入法模式：关闭
'14 Mobile 文本，字段大小：11，标题：手机，输入法模式：关闭
'15 Email 文本，字段大小：30，标题：电子邮件
'16 Address 文本，字段大小：100，标题：现在居住地
'17 MissionStatus 数字，整型，小数位数：0，标题：在职状况
'18 Duty 数字，整型，小数位数：0，标题：职务，默认值：0
'19 Post 数字，整型，小数位数：0，标题：职称，默认值：0
'20 ServiceLength 数字，整型，小数位数：0，标题：工龄，默认值：0
'21 Levels 数字，整型，小数位数：0，标题：工资级别，默认值：1
Public ENo As String
Public Name As String
Public Sex As String
Public IDCard As String
Public NativePlace As String
Public Residence As String
Public Marry As String
Public Birthday As Date
Public Folk As String
Public Party As String
Public Education As String
Public DeptID As Integer
Public Tele As String
Public Mobile As String
Public Email As String
Public Address As String
Public MissionStatus As Integer
Public Duty As Integer
Public Post As Integer
Public ServiceLength As Integer
Public Levels As Integer

'初始化
Public Sub Init()
  ENo = ""
  Name = ""
  Sex = ""
  IDCard = ""
  NativePlace = ""
  Residence = ""
  Marry = ""
  Birthday = Now
  Folk = ""
  Party = ""
  Education = ""
  DeptID = 0
  Tele = ""
  Mobile = ""
  Email = ""
  Address = ""
  MissionStatus = 1
  Duty = 0
  Post = 0
  ServiceLength = 0
  Levels = 1
End Sub

'判断员工编号是否存在
Public Function IsExistENo(ByVal paraENo As String) As Boolean
  Dim rs As New ADODB.Recordset
   '使用SELECT语句读取指定员工记录信息
  SQLStmt = "SELECT ENo FROM EmployeeInfo WHERE ENo = '" + Trim(StrReplace(paraENo)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '存在则返回True
    IsExistENo = True
  Else
    '不存在则返回False
    IsExistENo = False
  End If

  '断开连接
  DBDisconnect
End Function

'得到员工信息
Public Sub GetInfo(ByVal paraENo As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  ENo = paraENo                           '员工编号
  '使用SELECT语句读取指定部门数据
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE ENo = '" + Trim(StrReplace(paraENo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    Name = rs.Fields(1)                 '员工姓名
    If IsNull(rs.Fields(2)) Then
      Sex = ""
    Else
      Sex = rs.Fields(2)                  '性别
    End If
    IDCard = rs.Fields(3)               '身份证号
    If IsNull(rs.Fields(4)) Then
      NativePlace = ""
    Else
      NativePlace = Trim(rs.Fields(4))      '籍贯
    End If
    If IsNull(rs.Fields(5)) Then
      Residence = ""
    Else
      Residence = Trim(rs.Fields(5))      '户口所在地
    End If
    If IsNull(rs.Fields(6)) Then
      Marry = ""
    Else
      Marry = Trim(rs.Fields(6))      '婚姻状况
    End If
    If IsNull(rs.Fields(7)) Then
      Birthday = Now
    Else
      Birthday = Trim(rs.Fields(7))      '出生年月
    End If
    If IsNull(rs.Fields(8)) Then
      Folk = ""
    Else
      Folk = Trim(rs.Fields(8))      '民族
    End If
    If IsNull(rs.Fields(9)) Then
      Party = ""
    Else
      Party = Trim(rs.Fields(9))      '政治面貌
    End If
    If IsNull(rs.Fields(10)) Then
      Education = ""
    Else
      Education = Trim(rs.Fields(10))      '文化程度
    End If
    DeptID = Trim(rs.Fields(11))          '部门
    If IsNull(rs.Fields(12)) Then
      Tele = ""
    Else
      Tele = Trim(rs.Fields(12))      '固定电话
    End If
    If IsNull(rs.Fields(13)) Then
      Mobile = ""
    Else
      Mobile = Trim(rs.Fields(13))      '手机
    End If
    If IsNull(rs.Fields(14)) Then
      Email = ""
    Else
      Email = Trim(rs.Fields(14))      '电子邮件
    End If
    If IsNull(rs.Fields(15)) Then
      Address = ""
    Else
      Address = Trim(rs.Fields(15))      '现在居住地
    End If
    MissionStatus = Trim(rs.Fields(16))   '在职状况
    Duty = Trim(rs.Fields(17))   '职务
    Post = Trim(rs.Fields(18))   '职称
    ServiceLength = Trim(rs.Fields(19))   '工龄
    Levels = Trim(rs.Fields(20))   '工资级别
  Else
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'增加操作
Public Sub Insert()
  Dim SQLStmt As String
  SQLStmt = "INSERT INTO EmployeeInfo Values('" + Trim(StrReplace(ENo)) _
          + "', '" + Trim(StrReplace(Name)) + "', '" + Trim(Sex) + "', '" _
          + Trim(IDCard) + "', '" + Trim(StrReplace(NativePlace)) + "', '" _
          + Trim(Residence) + "', '" + Trim(Marry) _
          + "', " + Format(Birthday, "yyyy - mm - dd") + ", '" + Trim(Folk) _
          + "', '" + Trim(Party) + "', '" + Trim(Education) + "', " _
          + Trim(Str(DeptID)) + ", '" + Trim(Tele) + "', '" + Trim(Mobile) _
          + "', '" + Trim(StrReplace(Email)) + "', '" + Trim(StrReplace(Address)) + "', " _
          + Trim(Str(MissionStatus)) + ", " + Trim(Str(Duty)) + ", " + Trim(Str(Post)) _
          + ", " + Trim(Str(ServiceLength)) + ", " + Trim(Str(Levels)) + ")"
  SQLExt SQLStmt

  '===========修正出生年月时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE ENo = '" + Trim(StrReplace(ENo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(7) = Format(Birthday, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'修改操作
Public Sub Update(ByVal paraENo As String)
  Dim SQLStmt As String
  SQLStmt = "UPDATE EmployeeInfo SET" _
          + " Name = '" + Trim(StrReplace(Name)) + "', Sex = '" + Trim(Sex) + "', IDCard = '" _
          + Trim(IDCard) + "', NativePlace = '" + Trim(StrReplace(NativePlace)) + "', Residence = '" _
          + Trim(StrReplace(Residence)) + "', Marry = '" + Trim(Marry) _
          + "', Birthday = " + Format(Birthday, "yyyy - mm - dd") + ", Folk = '" + Trim(Folk) _
          + "', Party = '" + Trim(Party) + "', Education = '" + Trim(Education) + "', DeptID = " _
          + Trim(Str(DeptID)) + ", Tele = '" + Trim(Tele) + "', Mobile = '" + Trim(Mobile) _
          + "', Email = '" + Trim(StrReplace(Email)) + "', Address = '" + Trim(StrReplace(Address)) _
          + "', MissionStatus = " + Trim(Str(MissionStatus)) + ", Duty = " + Trim(Str(Duty)) + ", Post = " _
          + Trim(Str(Post)) + ", ServiceLength = " + Trim(Str(ServiceLength)) + ", Levels = " + Trim(Str(Levels)) _
          + " WHERE ENo = '" + Trim(StrReplace(paraENo)) + "'"
  SQLExt SQLStmt

  '===========修正出生年月时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE ENo = '" + Trim(StrReplace(ENo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(7) = Format(Birthday, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'删除操作
Public Sub Delete(ByVal paraENo As String)
  Dim SQLStmt As String
  SQLStmt = "DELETE FROM EmployeeInfo WHERE ENo = '" + Trim(StrReplace(paraENo)) + "'"
  SQLExt SQLStmt
End Sub