booktype.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 = "BookType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 TypeId  数字 长整型 分类编号
'2 TypeName  文本 50  分类名称
'4 UpperId 数字 长整型 上级分类的编号

Public TypeId As Long
Public TypeName As String
Public UpperId As Long

Public Sub Init()
  TypeId = -1
  TypeName = ""
  UpperId = -1
End Sub

'删除BookType
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM BookType WHERE TypeId=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub

Public Sub GetInfo(TmpId As Long)
  TypeId = TmpId
  Dim rs As New ADODB.Recordset '定义结果集对象
  '使用SELECT语句读取指定类型数据
  SqlStmt = "SELECT * FROM BookType WHERE TypeId=" + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    TypeName = TrimStr(rs.Fields(1))  '类型名称
    UpperId = Val(rs.Fields(2))       '上级编号
  Else
    Init
  End If
End Sub

Public Function GetName(ByVal TmpId As Long) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT TypeName FROM BookType WHERE TypeId=" _
          + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetName = TrimStr(rs.Fields(0))
  Else
    GetName = ""
  End If
End Function

Public Function GetUpperName(ByVal TmpId As Long) As String
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT u.TypeName FROM BookType a,BookType u WHERE a.UpperId=u.TypeId AND a.TypeId=" _
          + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetUpperName = TrimStr(rs.Fields(0))
  Else
    GetUpperName = ""
  End If
End Function

Public Function GetUpperId(ByVal TmpId As Long) As Integer
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT UpperId FROM BookType WHERE TypeId=" _
          + Trim(Str(TmpId))
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    GetUpperId = Val(rs.Fields(0))
  Else
    GetUpperId = -1
  End If
End Function

Public Function HaveBook(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句
  SqlStmt = "SELECT TypeId FROM BookInfo WHERE TypeId=" + Trim(TmpId)
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  '如果没有满足条件查询结果
  If rs.EOF Then
    HaveBook = False
  Else
    HaveBook = True
  End If
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句
  SqlStmt = "SELECT TypeId FROM BookType WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  '如果没有满足条件查询结果
  If rs.EOF Then
    HaveSon = False
  Else
    HaveSon = True
  End If
End Function

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

Public Function Insert() As Long
  Dim rs As New ADODB.Recordset  '定义结果集对象
  '因为TypeId的类型为自动编号,所以在插入数据时没有考虑TypeId字段
  SqlStmt = "INSERT INTO BookType(TypeName,UpperId) VALUES('" + Trim(TypeName) _
          + "'," + Trim(Str(UpperId)) + ")"
  '执行INSERT语句
  SQLExt (SqlStmt)
  '设置SELECT语句,读取表BookType中TypeId字段的最大值
  SqlStmt = "SELECT MAX(TypeId) FROM BookType"
  '读取记录集到rs对象
  Set rs = QueryExt(SqlStmt)
  '返回最大编号值
  If Not rs.EOF Then
    Insert = rs.Fields(0)
  Else
    Insert = 1
  End If
End Function

Public Sub Load_by_Upper(ByVal UpperId As Integer)
  Dim rs As New ADODB.Recordset
  Dim i As Long
  '对数组进行初始化
  Erase Arr_BookType
  ReDim Arr_BookType(0)
  Erase Arr_TypeId
  ReDim Arr_TypeId(0)
  '设置SQL语句
  SqlStmt = "SELECT TypeId, TypeName FROM BookType WHERE UpperId=" _
          + Trim(Str(UpperId)) + " ORDER BY TypeName"
  '执行SQL语句
  Set rs = QueryExt(SqlStmt)
  i = 0
  '依次把读取的数据赋值到数组中
  Do Until rs.EOF
    ReDim Preserve Arr_TypeId(i + 1)
    Arr_TypeId(i) = Val(rs.Fields(0))

    ReDim Preserve Arr_BookType(i + 1)
    Arr_BookType(i) = TrimStr(rs.Fields(1))
    rs.MoveNext

    i = i + 1
  Loop
End Sub

'更新数据
Public Sub Update(ByVal TmpId As Long)
  SqlStmt = "UPDATE BookType SET TypeName='" + Trim(TypeName) _
          + "' WHERE TypeId=" + Trim(Str(TmpId))
  SQLExt (SqlStmt)
End Sub