accessorylist.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 = "AccessoryList"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'设备分配中的配件信息
'1 Id  数字 长整型 自动编号 编号
'2 DisId 数字 长整型 分配编号
'3 DevId  文本 长度 50  设备编号
'4 ACount 数字 整型 数量

Public Id As Long
Public DisId As Long
Public DevId As String
Public ACount As Integer

Public Sub Init()
  DisId = -1
  DevId = ""
  ACount = 1
End Sub

Public Function In_DB(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM AccessoryList 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 AccessoryList 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
      DisId = 1
    Else
      DisId = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      DevId = ""
    Else
      DevId = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      ACount = ""
    Else
      ACount = rs.Fields(3)
    End If
  
    GetInfo = True
  End If
End Function
   
'插入
Public Sub Insert()
  SqlStmt = "INSERT INTO AccessoryList(DisId,DevId,ACount)" & _
          "VALUES(" + Trim(DisId) + ",'" & _
          Trim(DevId) + "'," + Trim(ACount) + ")"
  SQLExt (SqlStmt)
End Sub
'根据分配编号删除配件信息
Public Sub DeleteByDisId(ByVal TmpDId As Long)
  SqlStmt = "DELETE FROM AccessoryList WHERE DisId=" + Trim(TmpDId)
  SQLExt (SqlStmt)
End Sub

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

'根据分配编号更改此编号下的所有库存配件的数量
Public Sub UpdateCount(ByVal TmpId As Long)
  Dim rs As New ADODB.Recordset
  Dim sDId As String
  
  SqlStmt = "SELECT * FROM AccessoryList WHERE DisId=" + Trim(TmpId)
  Set rs = QueryExt(SqlStmt)
  Do While Not rs.EOF
    '设备编号
    sDId = Trim(rs.Fields(1))
    '更改库存数量
    MyDevice.UpdateCount sDId, rs.Fields(3)
    rs.MoveNext
  Loop
End Sub