leveltype.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 = "LevelType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Levels 数字，主键，整型，标题：工资级别，默认值：1
'2 Lname 文本，字段大小：20，标题：级别名称，必填字段：是，允许空字符串：否
'3 BasicWage  数字，双精度型，标题：基本工资，必填字段：否，允许空字符串：是，默认值：0

Public Levels As Integer
Public Lname As String
Public BasicWage As Double

'初始化
Public Sub Init()
  Levels = 0
  Lname = ""
  BasicWage = 0
End Sub

'判断级别名称是否存在
Public Function IsExistLevel(ByVal paraLname As String) As Boolean
  Dim rs As New ADODB.Recordset
 
  SQLStmt = "SELECT Levels FROM LevelType WHERE Lname = '" + Trim(StrReplace(paraLname)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    IsExistLevel = True
  Else
    IsExistLevel = False
  End If

  '断开连接
  DBDisconnect
End Function

'读取当前工资级别信息
Public Sub GetInfo(ByVal paraLname As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  Lname = paraLname                  '级别名称
  '使用SELECT语句读取工资级别类型数据
  SQLStmt = "SELECT * FROM LevelType WHERE Lname = '" + Trim(StrReplace(paraLname)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    Levels = rs.Fields(0)             '工资级别
    BasicWage = rs.Fields(2)          '基本工资
  Else
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'得到基本工资
Public Function GetBasicWage(ByVal paraLevel As Integer) As Double
  Dim rs As New ADODB.Recordset '定义结果集对象
  '使用SELECT语句读取工资级别数据
  SQLStmt = "SELECT * FROM LevelType WHERE Levels = " + Trim(Str(paraLevel))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    GetBasicWage = rs.Fields(2)
  Else
    MsgBox "无此工资级别", vbInformation, "信息提示"
    GetBasicWage = 0
  End If

  '断开连接
  DBDisconnect
End Function

'判断指定的工资级别中是否存在员工记录
Public Function HaveEmployee(ByVal paraLevel As Integer) As Boolean
  Dim rs As New ADODB.Recordset
  '使用SELECT语句在EmployeeInfo表中读取Levels字段等于paraLevel的记录
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE Levels = " + Trim(Str(paraLevel))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '当有记录，则表明有员工记录，返回True
    HaveEmployee = True
  Else
    '当无记录，则表明无员工记录，返回False
    HaveEmployee = False
  End If

  '断开连接
  DBDisconnect
End Function

'生成新记录的工资级别编号
Public Function GenNewID() As Integer
  Dim rs As New ADODB.Recordset
  '使用SELECT语句，读取最大的编号
  SQLStmt = "SELECT Max(Levels) FROM LevelType"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '没有工资级别记录的时候，赋值1
    GenNewID = 1
  Else
    '有工资级别记录的时候，赋值为表中最大编号 + 1
    GenNewID = rs.Fields(0) + 1
  End If

  '断开连接
  DBDisconnect
End Function

'插入一条记录
Public Sub Insert()
  '生成编号
  Levels = GenNewID
  '插入操作
  SQLStmt = "INSERT INTO LevelType VALUES(" + Trim(Str(Levels)) + ",'" _
            + Trim(StrReplace(Lname)) + "', " + Trim(Str(BasicWage)) + ")"
  SQLExt SQLStmt
End Sub

'修改一条记录
Public Sub Update(ByVal paraID As Integer)
  '修改操作
  SQLStmt = "UPDATE LevelType SET Lname = '" + Trim(StrReplace(Lname)) _
          + "', BasicWage = " + Trim(Str(BasicWage)) _
          + " WHERE Levels = " + Trim(Str(paraID))
  SQLExt SQLStmt
End Sub

'删除一条记录
Public Sub Delete(ByVal paraID As Integer)
  '删除操作
  SQLStmt = "DELETE FROM LevelType WHERE Levels = " + Trim(Str(paraID))
  SQLExt SQLStmt
End Sub