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

Public Post As Integer
Public Pname As String
Public PostWage As Double

'初始化
Public Sub Init()
  Post = 0
  Pname = ""
  PostWage = 0
End Sub

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

  '断开连接
  DBDisconnect
End Function

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

  '断开连接
  DBDisconnect
End Sub

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

  '断开连接
  DBDisconnect
End Function

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

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

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