missiontype.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 = "MissionType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 MissionStatus 数字，主键，整型，标题：在职状况，默认值：1
'2 Mname 文本，字段大小：20，标题：在职状况名称，必填字段：是，允许空字符串：否
'3 MissionWage  数字，双精度型，标题：岗位工资，必填字段：否，允许空字符串：是，默认值：0

Public MissionStatus As Integer
Public Mname As String
Public MissionWage As Double

'初始化
Public Sub Init()
  MissionStatus = 0
  Mname = ""
  MissionWage = 0
End Sub

'判断在职状况名称是否存在
Public Function IsExistMission(ByVal paraMname As String) As Boolean
  Dim rs As New ADODB.Recordset
 
  SQLStmt = "SELECT MissionStatus FROM MissionType WHERE Mname = '" + Trim(StrReplace(paraMname)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    IsExistMission = True
  Else
    IsExistMission = False
  End If

  '断开连接
  DBDisconnect
End Function

'读取当前在职状况信息
Public Sub GetInfo(ByVal paraMname As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  Mname = paraMname                  '在职状况名称
  '使用SELECT语句读取在职状况类型数据
  SQLStmt = "SELECT * FROM MissionType WHERE Mname = '" + Trim(StrReplace(paraMname)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    MissionStatus = rs.Fields(0)      '在职状况
    MissionWage = rs.Fields(2)        '岗位工资
  Else
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'得到岗位工资
Public Function GetMissionWage(ByVal paraMission As Integer) As Double
  Dim rs As New ADODB.Recordset '定义结果集对象
  '使用SELECT语句读取在职状况数据
  SQLStmt = "SELECT * FROM MissionType WHERE MissionStatus = " + Trim(Str(paraMission))
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    GetMissionWage = rs.Fields(2)
  Else
    MsgBox "无此在职状况信息", vbInformation, "信息提示"
    GetMissionWage = 0
  End If

  '断开连接
  DBDisconnect
End Function

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

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

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