deptinfo.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 = "DeptInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 DeptID 数字，主键，整型，标题：部门编号，有效性规则：DeptID > 0，有效性文本：部门编号必须大于0
'2 DeptName 文本，字段大小：50，标题：部门名称，必填字段：是，允许空字符串：否
'3 Describes  文本，字段大小：255，标题：描述，必填字段：否，允许空字符串：是，输入法模式：开启

Public DeptID As Integer
Public DeptName As String
Public Describes As String

'初始化
Public Sub Init()
  DeptID = 0
  DeptName = ""
  Describes = ""
End Sub

'判断部门名称是否存在
Public Function IsExistDept(ByVal paraDeptName As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  '使用SELECT语句读取指定部门数据
  SQLStmt = "SELECT DeptID FROM DeptInfo WHERE DeptName = '" + Trim(StrReplace(paraDeptName)) _
             + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '存在则返回True
    IsExistDept = True
  Else
    '不存在则返回False
    IsExistDept = False
  End If

  '断开连接
  DBDisconnect
End Function

'读取当前部门记录
Public Sub GetInfo(ByVal paraDeptName As String)
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  DeptName = paraDeptName                 '部门名称
  '使用SELECT语句读取指定部门数据
  SQLStmt = "SELECT * FROM DeptInfo WHERE DeptName = '" + Trim(StrReplace(paraDeptName)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If Not rs.EOF Then
    '读取部门其他信息
    DeptID = rs.Fields(0)                 '部门编号
    If IsNull(rs.Fields(2)) Then
      Describes = ""
    Else
      Describes = Trim(rs.Fields(2))      '部门描述
    End If
  Else
    '查询结果为空，则初始化类
    Init
  End If

  '断开连接
  DBDisconnect
End Sub

'判断指定的部门中是否存在员工记录
Public Function HaveEmployee(ByVal paraDeptID As Integer) As Boolean
  Dim rs As New ADODB.Recordset
  '使用SELECT语句在EmployeeInfo表中读取DeptID字段等于paraDeptID的记录
  SQLStmt = "SELECT * FROM EmployeeInfo WHERE DeptID = " + Trim(Str(paraDeptID))
  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(DeptID) FROM DeptInfo"
  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()
  '得到部门编号
  DeptID = GenNewID

  '插入操作
  SQLStmt = "INSERT INTO DeptInfo VALUES(" + Trim(Str(DeptID)) + ",'" _
            + Trim(StrReplace(DeptName)) + "','" + Trim(StrReplace(Describes)) + "')"
  SQLExt SQLStmt
End Sub

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

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