storein.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 = "StoreIn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 StoreId 数字 长整型 入库编号
'2 BookNo  文本 50  书号
'3 StoreCount 数字 长整型 入库数量
'4 Price 数字 双精度 图书价格
'5 UserName 文本 40  经办人
'6 CreateDate 时间/日期  入库日期
'7 Flag 文本 20 是否审核  未审核（可以删除），审核完毕（不能删除）

Public StoreId As Long
Public BookNo As String
Public StoreCount As Long
Public Price As Double
Public UserName As String
Public CreateDate As String
Public Flag As String

Public Sub Init()
  StoreId = 0
  BookNo = ""
  StoreCount = 0
  Price = 0
  UserName = ""
  CreateDate = ""
  Flag = "未审核"
End Sub

Public Function GetInfo(ByVal TmpStoreId As Long) As Boolean
  '如果入库编号小于或等于0，则对数据进行初始化，并返回
  If TmpStoreId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  Dim rs As New ADODB.Recordset
  StoreId = TmpStoreId
  
  '设置SQL语句
  SqlStmt = "SELECT * FROM StoreIn WHERE StoreId=" + Trim(Str(StoreId))
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetInfo = False
    Init
    Exit Function
  Else
    '从结果集中按列读取数据
    '书号
    If IsNull(rs.Fields(1)) Then
      BookNo = ""
    Else
      BookNo = rs.Fields(1)
    End If
    '入库图书数量
    If IsNull(rs.Fields(2)) Then
      StoreCount = 0
    Else
      StoreCount = rs.Fields(2)
    End If

    '图书单价
    If IsNull(rs.Fields(3)) Then
      Price = 0
    Else
      Price = rs.Fields(3)
    End If
    '经办人
    If IsNull(rs.Fields(4)) Then
      UserName = ""
    Else
      UserName = rs.Fields(4)
    End If
    '操作日期
    If IsNull(rs.Fields(5)) Then
      CreateDate = ""
    Else
      CreateDate = rs.Fields(5)
    End If
    '审核标志
    If IsNull(rs.Fields(6)) Then
      Flag = ""
    Else
      Flag = rs.Fields(6)
    End If
  End If
  GetInfo = True
End Function
'插入记录
Public Sub Insert()
  SqlStmt = "INSERT INTO StoreIn(BookNo,StoreCount,Price,UserName,CreateDate,Flag) Values('" _
     + Trim(BookNo) + "'," + Trim(StoreCount) + "," _
     + Trim(Price) + ",'" + Trim(UserName) + "','" + Trim(CreateDate) + "','未审核')"
  SQLExt (SqlStmt)
End Sub
'未审核前可以删除记录
Public Sub Delete(ByVal TmpStoreId As Long)
  SqlStmt = "DELETE FROM StoreIn WHERE StoreId=" + Trim(TmpStoreId)
  SQLExt (SqlStmt)
End Sub
'未审核前可以更改记录
Public Sub Update(ByVal TmpStoreId As Long)
  SqlStmt = "UPDATE StoreIn SET BookNo='" + Trim(BookNo) + "', StoreCount=" + Trim(StoreCount) _
     + ", Price=" + Trim(Price) + ", UserName='" + Trim(UserName) + "', CreateDate='" + Trim(CreateDate) _
     + "' WHERE StoreId=" + Trim(TmpStoreId)
  SQLExt (SqlStmt)
End Sub

'更改审核标志
Public Sub UpdateFlag(ByVal TmpStoreId As Long)
  SqlStmt = "UPDATE StoreIn SET Flag='审核完毕' WHERE StoreId=" + Trim(TmpStoreId)
  SQLExt (SqlStmt)
End Sub