bookinfo.cls

sql的实例源码

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 varchar 20 图书编号 Allow Null = False 关键字
'2 BookName varchar 40 图书名称 Allow Null = False
'3 Author varchar 20 作者 Allow Null = False
'4 Publisher varchar 30 出版社 Allow Null = False
'5 Price real 图书价格 Allow Null = False
'6 TypeID int 图书类型编号 Allow Null = False
'7 CoverPhoto image 封面图片 Allow Null = True
'8 Memo varchar 255 备注 Allow Null = True

Public BookNo As String
Public BookName As String
Public Author As String
Public Publisher As String
Public Price As Single
Public TypeID As Long
Public Memo As String

'初始化
Public Sub Init()
  BookNo = ""
  BookName = ""
  Author = ""
  Publisher = ""
  Price = 0
  TypeID = 0
  Memo = ""
End Sub

'判断是否存在此书籍编号
Public Function IsExistBNo(ByVal paraBNo As String) As Boolean
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM BookInfo WHERE BookNo = '" + Trim(paraBNo) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    IsExistBNo = False
  Else
    IsExistBNo = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'根据图书编号得到其他信息
Public Function GetInfo(ByVal paraBNo As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM BookInfo WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则需要初始化
    Init
    '返回False
    GetInfo = False
  Else
    '===========写入RecordSet集中的数据===============
    '写入图书编号
    BookNo = Trim(paraBNo)
    '得到图书名称
    BookName = Trim(rs.Fields(1))
    '得到作者名称
    Author = Trim(rs.Fields(2))
    '得到出版社名称
    Publisher = Trim(rs.Fields(3))
    '得到图书价格
    Price = rs.Fields(4)
    '得到图书分类编号
    TypeID = rs.Fields(5)
    '得到备注
    If IsNull(rs.Fields(7)) Then
      Memo = ""
    Else
      Memo = Trim(rs.Fields(7))
    End If
    '=========写入RecordSet集中的数据成功=============

    '返回True
    GetInfo = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '设置INSERT语句
  SQLStmt = "INSERT INTO BookInfo(BookNo, BookName, Author, Publisher, Price," _
          + " TypeID) VALUES('" + Trim(StrReplace(BookNo)) _
          + "', '" + Trim(StrReplace(BookName)) + "', '" _
          + Trim(StrReplace(Author)) + "', '" + Trim(StrReplace(Publisher)) _
          + "', " + Trim(Val(Price)) + ", " + Trim(Str(TypeID)) + ")"
  '执行INSERT语句
  SQLExt SQLStmt
End Sub

'更新数据
Public Sub Update(ByVal paraBNo As String)
  SQLStmt = "UPDATE BookInfo SET BookName = '" + Trim(StrReplace(BookName)) _
          + "', Author = '" + Trim(StrReplace(Author)) + "', Publisher = '" _
          + Trim(StrReplace(Publisher)) + "', Price = " + Trim(Val(Price)) _
          + ", TypeID = " + Trim(Str(TypeID)) _
          + " WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  '执行UPDATE语句
  SQLExt SQLStmt
End Sub

'删除操作
Public Sub Delete(ByVal paraBNo As String)
  SQLStmt = "DELETE FROM BookInfo WHERE BookNo = '" _
          + Trim(StrReplace(paraBNo)) + "'"
  '执行DELETE语句,删除BookNo等于paraBNo的记录
  SQLExt SQLStmt
End Sub