bookinfo.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 = "BookInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 BookNo  文本 50  书号
'2 BookName  文本 50  图书名称
'3 Author  文本 50  作者
'4 Publisher 文本 50  出版社
'5 Location  文本 50  版次
'6 Price  数字 单精度型 价格
'7 TypeId 数字 整型 分类编号
'8 Total 数字 长整型 总数量
'9 Description 文本  图书状态


Public BookNo As String
Public BookName As String
Public Author As String
Public Publisher As String
Public Location As String
Public Price As Single
Public Total As Integer
Public TypeId As Integer
Public Description As String

Public Sub Init()
  BookNo = ""
  BookName = ""
  Author = ""
  Publisher = ""
  Location = ""
  Price = 0
  Total = 0
  TypeId = 0
  Description = ""
End Sub

'删除BookInfo数据
Public Sub Delete(ByVal TmpBookNo As String)
  SqlStmt = "Delete FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  SQLExt (SqlStmt)
End Sub
'取得图书名称
Public Function GetName(ByVal TmpBookNo As String) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT BookName FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetName = ""
    Exit Function
  Else
    GetName = TrimStr(rs.Fields(1))
  End If
End Function

'取得图书分类编号
Public Function GetTypeId(ByVal TmpBookNo As String) As Integer
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT TypeId FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetTypeId = 0
    Exit Function
  Else
    GetTypeId = Val(rs.Fields(0))
  End If
End Function

'判断图书编号是否重复
Public Function HaveNo(ByVal TmpNo As String) As Boolean
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT BookNo FROM BookInfo WHERE BookName='" + Trim(TmpName) + "'"
  Set rs = QueryExt(SqlStmt)
  
  If Not rs.EOF Then
    HaveNo = True  '有重复编号
  Else
    HaveNo = False
  End If
End Function
'得到当前库存数量
Public Function GetTotalNum(ByVal TmpBookNo As String) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT Total FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetTotalNum = Val(rs.Fields(7))
  Else
    GetTotalNum = 0
  End If
End Function


Public Function GetInfo(ByVal TmpBookNo As String) As Boolean
  If TmpBookNo = "" Then
    Init
    GetInfo = False
    Exit Function
  End If
  
  BookNo = TmpBookNo
  
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT * FROM BookInfo WHERE BookNo='" + Trim(TmpBookNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetInfo = False
    Exit Function
  Else
    BookName = TrimStr(rs.Fields(1))
    Author = TrimStr(rs.Fields(2))
    Publisher = TrimStr(rs.Fields(3))
    Location = TrimStr(rs.Fields(4))
    Price = Val(rs.Fields(5))
    TypeId = Val(rs.Fields(6))
    Total = Val(rs.Fields(7))
    Description = TrimStr(rs.Fields(8))
  End If
  GetInfo = True
End Function

Public Function In_DB(ByVal TmpBookName As String) As Boolean
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT BookNo FROM BookInfo WHERE BookName='" + Trim(TmpBookName) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    In_DB = True
  Else
    In_DB = False
  End If
End Function

Public Sub Insert()
  '设置INSERT语句
  SqlStmt = "INSERT INTO BookInfo (BookNo, BookName, Publisher," _
     + "Author, Location, Price,TypeId,Total,Description) Values('" + Trim(BookNo) + "','" _
     + Trim(BookName) + "','" + Trim(Publisher) + "','" + Trim(Author) _
     + "','" + Trim(Location) + "'," + Trim(Price) + "," + Trim(TypeId) _
     + ",0,'" + Trim(Description) + "')"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub

Public Sub Update(ByVal BookNo As String)
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set BookName='" + Trim(BookName) + "',Publisher='" _
          + Trim(Publisher) + "',Author='" + Trim(Author) _
          + "',Location='" + Trim(Location) + "',Price=" + Trim(Price) _
          + ",Description='" + Trim(Description) + "' WHERE BookNo='" + Trim(BookNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub

'图书盘点时更改当前图书库存数量
Public Sub UpdateTotal(ByVal TmpBookNo As String, ByVal CountNum As Long)
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set Total=" + Trim(CountNum) _
          + " WHERE BookNo='" + Trim(TmpBookNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub

'入库审核和借阅确认后更新图书资料表中的库存数量，借阅确认的数量需要转为负数
Public Sub UpdateStore(ByVal TmpBookNo As String, ByVal TmpStoreCount As Long)
  '设置UPDATE语句
  SqlStmt = "Update BookInfo Set Total=Total+" + Trim(TmpStoreCount) _
          + " WHERE BookNo='" + Trim(TmpBookNo) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub