rewardinfo.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 = "RewardInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 ENo 文本，关键字，字段大小：10，标题：员工编号 必填字段：是，允许空字符串：否，输入法模式：关闭
'2 Classes 数字，整型，标题：奖罚类别，默认值：1
'3 Recorder 文本，字段大小：20 ，标题：记录人员，输入法模式：开启
'4 RecordTime 日期/时间，标题：记录时间
'5 Memo  文本，字段大小：255，标题：备注，必填字段：否，允许空字符串：是，输入法模式：开启

Public ENo As String
Public Classes As Integer
Public Recorder As String
Public RecordTime As Date
Public Memo As String

'初始化
Public Sub Init()
  ENo = ""
  Classes = 1
  Recorder = ""
  RecordTime = 0
  Memo = ""
End Sub

'读取当前用户一条奖罚情况信息
Public Function GetSingleInfo(ByVal paraENo As String, ByVal pos As Integer) As Boolean
  Dim rs As New ADODB.Recordset '定义结果集对象
  
  ENo = paraENo                       '员工编号
  '使用SELECT语句读取奖罚类别数据
  SQLStmt = "SELECT * FROM RewardInfo WHERE ENo = '" + Trim(StrReplace(paraENo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    'MsgBox "无记录", vbInformation, "信息提示"
    GetSingleInfo = False
    Exit Function
  End If
  rs.Move pos
  If rs.EOF Then
    'MsgBox "没有这么多条记录，请确认无误", vbInformation, "信息提示"
    GetSingleInfo = False
    Exit Function
  End If
  Classes = rs.Fields(1)            '奖罚类别
  If IsNull(rs.Fields(2)) Then
    Recorder = ""
  Else
    Recorder = rs.Fields(2)           '记录人员
  End If
  If IsNull(rs.Fields(3)) Then
    RecordTime = Now
  Else
    RecordTime = rs.Fields(3)         '记录时间
  End If
  If IsNull(rs.Fields(4)) Then
    Memo = ""                       '备注
  Else
    Memo = rs.Fields(4)
  End If
  GetSingleInfo = True

  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '插入操作
  SQLStmt = "INSERT INTO RewardInfo VALUES('" + Trim(StrReplace(ENo)) + "', " _
            + Trim(Str(Classes)) + ", '" + Trim(StrReplace(Recorder)) + "', " _
            + Format(RecordTime, "yyyy - mm - dd") + ", '" _
            + Trim(StrReplace(Memo)) + "')"
  SQLExt SQLStmt

  '===========修正记录时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM RewardInfo WHERE ENo = '" + Trim(StrReplace(ENo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(3) = Format(RecordTime, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'删除一条记录
Public Sub Delete(ByVal paraID As String, ByVal paraClass As Integer)
  '删除操作
  SQLStmt = "DELETE FROM RewardInfo WHERE ENo = '" + Trim(StrReplace(paraID)) _
          + "' AND Classes = " + Trim(Str(paraClass))
  SQLExt SQLStmt
End Sub

'删除所有指定员工记录
Public Sub DeleteEmp(ByVal paraENo As String)
  '删除操作
  SQLStmt = "DELETE FROM RewardInfo WHERE ENo = '" + Trim(StrReplace(paraID)) _
          + "'"
  SQLExt SQLStmt
End Sub