dutytype.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 = "DutyType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Duty 数字，主键，整型，标题：职务，默认值：0
'2 Dname 文本，字段大小：20，标题：职务名称，必填字段：是，允许空字符串：否
'3 Allowance  数字，双精度型，标题：补贴，必填字段：否，允许空字符串：是，默认值：0

Public Duty As Integer
Public Dname As String
Public Allowance As Double

'初始化
Public Sub Init()
  Duty = 0
  Dname = ""
  Allowance = 0
End Sub

'判断职务名称是否存在
Public Function IsExistDuty(ByVal paraDname As String) As Boolean
  Dim rs As New ADODB.Recordset
 
  SQLStmt = "SELECT Duty FROM DutyType WHERE Dname = '" + Trim(StrReplace(paraDname)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    IsExistDuty = True
  Else
    IsExistDuty = False
  End If

  '断开连接
  DBDisconnect
End Function

'读取当前职务信息
Public Sub GetInfo(ByVal paraDname As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  Dname = paraDname                  '职务名称
  '使用SELECT语句读取职务类型数据
  SQLStmt = "SELECT * FROM DutyType WHERE Dname = '" + Trim(StrReplace(paraDname)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    Duty = rs.Fields(0)               '职务
    Allowance = rs.Fields(2)          '补贴
  Else
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'得到补贴值
Public Function GetAllowance(ByVal paraDuty As Integer) As Double
  Dim rs As New ADODB.Recordset '定义结果集对象
  '使用SELECT语句读取职务类型数据
  SQLStmt = "SELECT * FROM DutyType WHERE Duty = " + Trim(Str(paraDuty))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    GetAllowance = rs.Fields(2)
  Else
    MsgBox "无此职务", vbInformation, "信息提示"
    GetAllowance = 0
  End If

  '断开连接
  DBDisconnect
End Function

'判断指定的职务中是否存在员工记录
Public Function HaveEmployee(ByVal paraDuty As Integer) As Boolean
  Dim rs As New ADODB.Recordset
  '使用SELECT语句在EmployeeInfo表中读取Duty字段等于paraDuty的记录
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE Duty = " + Trim(Str(paraDuty))
  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(Duty) FROM DutyType"
  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()
  '生成编号
  Duty = GenNewID
  '插入操作
  SQLStmt = "INSERT INTO DutyType VALUES(" + Trim(Str(Duty)) + ",'" _
            + Trim(StrReplace(Dname)) + "', " + Trim(Str(Allowance)) + ")"
  SQLExt SQLStmt
End Sub

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

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