maintenance.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 = "Maintenance"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id  数字 长整型 自动编号 维护编号
'2 DevId  文本 长度 50  设备名称
'3 MnName  文本 长度 50 维护人员
'4 MnSum 数字 长整型 维护费用
'5 MnDate 日期\时间 维护时间
'6 Comment 备注 备注

Public Id As Long
Public DevId As String
Public MnName As String
Public MnSum As Long
Public MnDate As String
Public Comment As String

Public Sub Init()
  DevId = ""
  MnName = ""
  MnSum = 0
  MnDate = ""
  Comment = ""
End Sub

'删除维护信息
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Maintenance 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 Maintenance 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 Maintenance 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
      DevId = ""
    Else
      DevId = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      MnName = ""
    Else
      MnName = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      MnDate = ""
    Else
      MnDate = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      MnSum = 0
    Else
      MnSum = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      Comment = ""
    Else
      Comment = rs.Fields(5)
    End If
    GetInfo = True
  End If
End Function
   
'插入分类
Public Sub Insert()
  SqlStmt = "INSERT INTO Maintenance(DevId,MnName,MnSum," & _
          "MnDate,Comment) VALUES('" + Trim(DevId) + "','" & _
          Trim(MnName) + "'," + Trim(MnSum) + ",'" + Trim(MnDate) + "','" & _
          Trim(Comment) + "')"
  SQLExt (SqlStmt)
End Sub
'更新数据
Public Sub Update(ByVal TmpId As Long)
   SqlStmt = "UPDATE Maintenance SET DevId='" + Trim(DevId) & _
          "',MnName='" + Trim(MnName) + "',MnSum=" + Trim(MnSum) + "," & _
          "MnDate='" + Trim(MnDate) + "',Comment='" & _
          Trim(Comment) + "' Where Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub