borrowlist.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 = "BorrowList"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id 数字 长整型 自动编号 编号
'2 BNo  文本 20 借阅编号
'3 BookNo  文本 20  图书编号
'4 BCount 数字 整型 借阅数量
'5 ReturnDate 日期\时间 应归还日期
'6 RealReturnDate 日期\时间 实际归还日期
'7 GDate 日期\时间 续借日期
'8 Forfeit 数字 长整型 罚款金额
'9 Status 文本 20 状态：借阅；归还；续借；丢失

Public Id As Long
Public BNo As String
Public BookNo As String
Public BCount As Integer
Public ReturnDate As String
Public RealReturnDate As String
Public GDate As String
Public Forfeit As Long
Public Status As String

Public Sub Init()
  Id = -1
  BNo = ""
  BookNo = ""
  BCount = 0
  ReturnDate = ""
  RealReturnDate = ""
  GDate = ""
  Forfeit = 0
  Status = ""
End Sub

'删除单个记录
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM BorrowList WHERE Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub
'删除一批记录
Public Sub DeleteByBNo(ByVal TmpBNo As String)
  SqlStmt = "DELETE FROM BorrowList WHERE BNo='" + Trim(TmpBNo) + "'"
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  If TmpId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  Id = TmpId
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM BorrowList WHERE Id=" + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetInfo = False
    Init
    Exit Function
  Else
    If IsNull(rs.Fields(1)) Then
      BNo = ""
    Else
      BNo = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      BookNo = ""
    Else
      BookNo = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      BCount = 0
    Else
      BCount = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      ReturnDate = ""
    Else
      ReturnDate = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      RealReturnDate = ""
    Else
      RealReturnDate = rs.Fields(5)
    End If
    
    If IsNull(rs.Fields(6)) Then
      GDate = ""
    Else
      GDate = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      Forfeit = 0
    Else
      Forfeit = rs.Fields(7)
    End If
    If IsNull(rs.Fields(8)) Then
      Status = "借阅"
    Else
      Status = rs.Fields(8)
    End If
  End If
  GetInfo = True
End Function

Public Sub Insert()
  SqlStmt = "INSERT INTO BorrowList(BNo,BookNo,BCount,ReturnDate,Forfeit,Status)" _
     + " Values('" + Trim(BNo) + "','" + Trim(BookNo) _
     + "'," + Trim(BCount) + ",'" + Trim(ReturnDate) + "'," + Trim(Forfeit) + ",'借阅')"
  
  SQLExt (SqlStmt)
End Sub

Public Sub Update(ByVal TmpId As Long)
  SqlStmt = "Update BorrowList Set BookNo='" + Trim(BookNo) + "', BCount=" _
          + Trim(BCount) + ",ReturnDate='" + Trim(ReturnDate) + "',RealReturnDate='" _
          + Trim(RealReturnDate) + ",GDate='" + Trim(GDate) _
          + "',Forfeit=" + Trim(Forfeit) + " WHERE Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub

'更改借阅状态：借阅,续借,归还,挂失
Public Sub UpdateStatus(ByVal TmpId As Integer)
  If Status = "续借" Then  '如果为续借则需要更新应归还日期
    SqlStmt = "Update BorrowList Set ReturnDate='" + Trim(ReturnDate) + "'," _
       + "RealReturnDate='" + Trim(RealReturnDate) + "'," _
       + "GDate='" + Trim(GDate) + "',Forfeit=" + Trim(Forfeit) + "," _
       + "Status='" + Trim(Status) + "' WHERE Id=" + Trim(TmpId)
  Else
    SqlStmt = "Update BorrowList Set RealReturnDate='" + Trim(RealReturnDate) + "'," _
       + "GDate='" + Trim(GDate) + "',Forfeit=" + Trim(Forfeit) + "," _
       + "Status='" + Trim(Status) + "' WHERE Id=" + Trim(TmpId)
  End If
  SQLExt (SqlStmt)
End Sub
'根据借阅证编号查找借阅数量
Public Function GetBorrowCount(ByVal TmpCardNo As String) As Integer
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT SUM(Bcount) FROM BorrowList l,Borrow b WHERE l.BNo=b.BorrowNo " _
     + " AND b.CardNo='" + Trim(TmpCardNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If IsNull(rs.Fields(0)) Then
    GetBorrowCount = 0
  Else
    GetBorrowCount = rs.Fields(0)
  End If
End Function
'计算某个图书的借阅数量
Public Function GetBookCount(ByVal TmpBookNo As String) As Integer
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT SUM(Bcount) FROM BorrowList WHERE BookNo='" + Trim(TmpBookNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If IsNull(rs.Fields(0)) Then
    GetBookCount = 0
  Else
    GetBookCount = rs.Fields(0)
  End If
End Function
'判断是否有重复借阅现象
Public Function IsBorrow(ByVal TmpBookNo As String, ByVal TmpBNo As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM BorrowList WHERE BookNo='" + Trim(TmpBookNo) + "'" _
           + "AND BNo='" + Trim(TmpBNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    IsBorrow = True
  Else
    IsBorrow = False
  End If
End Function

'借阅时更改图书库存数量
Public Sub UpdateTotal(ByVal TmpBNo As String)
  Dim rs As New ADODB.Recordset
  '根据指定的借阅编号读取借阅明细记录
  SqlStmt = "SELECT * FROM BorrowList WHERE BNo='" + Trim(TmpBNo) + "'"
  '结果集存放在rs对象中
  Set rs = QueryExt(SqlStmt)
  '处理所有的记录
  Do While rs.EOF
    '更新库存数量为原数量-1
    Call MyBookInfo.UpdateStore(rs.Fields(2), -1)
    '处理下一条记录
    rs.MoveNext
  Loop
End Sub