distribute.cls

vb与access数据库的操作实例

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Distribute"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id  数字 长整型 自动编号 报废编号
'2 DeptId 数字 长整型 部门编号
'3 EmpName  文本 长度 50 负责人
'4 DevId  文本 长度 50  设备编号
'5 CreateDate 日期\时间 申请时间
'6 Flag 数字 字节 标记：1-分配；2-分配确认

Public Id As Long
Public DeptId As Long
Public EmpName As String
Public DevId As String
Public CreateDate As String
Public Flag As Integer

Public Sub Init()
  DeptId = -1
  EmpName = ""
  DevId = ""
  CreateDate = Date
  Flag = 1
End Sub

'删除分配信息
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Distribute WHERE Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub

Public Function In_DB(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Distribute WHERE Id=" + Trim(TmpId)
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    In_DB = False
  Else
    In_DB = True
  End If
End Function
Public Function GetInfo(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  '设置SELECT语句，读取编号为TmpId的记录
  SqlStmt = "SELECT * FROM Distribute WHERE Id=" + Trim(TmpId)
  '将结果集读取到rs中
  Set rs = QueryExt(SqlStmt)

  If rs.EOF Then
    '如果结果集为空，则初始化
    Init
    GetInfo = False
  Else
    '将结果集中的数据赋值到成员变量中
    If IsNull(rs.Fields(0)) Then
      Id = 0
    Else
      Id = rs.Fields(0)
    End If
    If IsNull(rs.Fields(1)) Then
      DeptId = 1
    Else
      DeptId = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      EmpName = ""
    Else
      EmpName = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      DevId = ""
    Else
      DevId = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      CreateDate = ""
    Else
      CreateDate = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      Flag = ""
    Else
      Flag = rs.Fields(5)
    End If
  
    GetInfo = True
  End If
End Function
   
'插入
Public Sub Insert()
  SqlStmt = "INSERT INTO Distribute(DeptId,EmpName,DevId,CreateDate,Flag) " & _
          " VALUES(" + Trim(DeptId) + ",'" + Trim(EmpName) + "','" & _
          Trim(DevId) + "','" + Trim(CreateDate) + "',1)"
  SQLExt (SqlStmt)
End Sub
'更新数据
Public Sub Update(ByVal TmpId As Long)
   SqlStmt = "UPDATE Distribute SET DeptId=" + Trim(DeptId) + "," & _
          "EmpName='" + Trim(EmpName) + "'," & _
          "DevId='" + Trim(DevId) + "',CreateDate='" + Trim(CreateDate) + "'" & _
          "Where Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub
'更新状态标记为分配审核
Public Sub UpdateFlag(ByVal TmpId As Long)
  SqlStmt = "UPDATE Distribute SET Flag=2 " & _
          "Where Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub
'返回当前最大编号
Public Function MaxId() As Long
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT MAX(Id) FROM Distribute"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    MaxId = 0
  Else
    MaxId = rs.Fields(0)
  End If
End Function