cardtype.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 = "CardType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 CTypeID varchar 10 借阅证类型 Allow Null = False 主键
'2 TypeName varchar 20 借阅证类型名称 Allow Null = False
'3 MaxCount smallint 可借阅书籍的最大数目 Allow Null = False
'4 MaxDays smallint 可借阅书籍的最长时间 Allow Null = False
'5 RenewDays smallint 可续借书籍的最长时间 Allow Null = False

Public CTypeID As String
Public TypeName As String
Public MaxCount As Integer
Public MaxDays As Integer
Public RenewDays As Integer

'初始化
Public Sub Init()
  CTypeID = ""
  TypeName = ""
  MaxCount = 0
  MaxDays = 0
  RenewDays = 0
End Sub

'判断是否存在此证件类型编号
Public Function IsExistCTypeID(ByVal paraCTypeID As String) As Boolean
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardType WHERE CTypeID = '" + Trim(paraCTypeID) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    IsExistCTypeID = False
  Else
    IsExistCTypeID = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'判断是否存在此证件类型名称
Public Function IsExistTypeName(ByVal paraTypeName As String) As Boolean
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardType WHERE TypeName = '" + Trim(StrReplace(paraTypeName)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    IsExistTypeName = False
  Else
    IsExistTypeName = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'根据借阅证类型名称得到其他信息
Public Function GetInfo(ByVal paraTypeName As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardType WHERE TypeName = '" + Trim(StrReplace(paraTypeName)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则需要初始化
    Init
    '返回False
    GetInfo = False
  Else
    '===========写入RecordSet集中的数据===============
    '得到借阅证类型编号
    CTypeID = Trim(rs.Fields(0))
    '写入借阅证类型名称
    TypeName = Trim(paraTypeName)
    '得到可借阅书籍的最大数目
    MaxCount = Trim(rs.Fields(2))
    '得到可借阅书籍的最长时间
    MaxDays = Trim(rs.Fields(3))
    '得到可续借书籍的最长时间
    RenewDays = Trim(rs.Fields(4))
    '=========写入RecordSet集中的数据成功=============

    '返回True
    GetInfo = True
  End If

  '断开连接
  DBDisconnect
End Function

'根据分类编号得到借阅证类型名称
Public Function GetTypeName(ByVal paraID As String) As String
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardType WHERE CTypeID = '" + Trim(paraID) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则返回空
    GetTypeName = ""
  Else
    '若找到对应值，返回此值
    GetTypeName = Trim(rs.Fields(1))
  End If

  '断开连接
  DBDisconnect
End Function

'判断在此类型下是否存在借阅证件
Public Function HaveCardInfo(ByVal paraCTypeID As String) As Boolean
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardInfo WHERE CTypeID = '" + Trim(paraCTypeID) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    HaveCardInfo = False
  Else
    HaveCardInfo = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '设置INSERT语句
  SQLStmt = "INSERT INTO CardType VALUES('" + Trim(CTypeID) _
          + "', '" + Trim(StrReplace(TypeName)) + "', " _
          + Trim(Str(MaxCount)) + ", " + Trim(Str(MaxDays)) _
          + ", " + Trim(Str(RenewDays)) + ")"
  '执行INSERT语句
  SQLExt SQLStmt
End Sub

'更新数据
Public Sub Update(ByVal paraCTypeID As String)
  SQLStmt = "UPDATE CardType SET TypeName = '" + Trim(StrReplace(TypeName)) _
          + "', MaxCount = " + Trim(Str(MaxCount)) _
          + ", MaxDays = " + Trim(Str(MaxDays)) + ", RenewDays = " _
          + Trim(Str(RenewDays)) + " WHERE CTypeID = '" _
          + Trim(paraCTypeID) + "'"
  '执行UPDATE语句
  SQLExt SQLStmt
End Sub

'删除操作
Public Sub Delete(ByVal paraCTypeID As String)
  SQLStmt = "DELETE FROM CardType WHERE CTypeID = '" _
          + Trim(paraCTypeID) + "'"
  '执行DELETE语句
  SQLExt SQLStmt
End Sub