cardinfo.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 = "CardInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 CardNo varchar 16 借阅证号 Allow Null = False 关键字
'2 Reader varchar 20 读者姓名 Allow Null = False
'3 WorkPlace varchar 50 工作单位 Allow Null = True
'4 IDCard varchar 18 身份证号 Allow Null = False
'5 CTypeID varchar 10 借阅证类型 Allow Null = False
'6 CreateDate datetime 办证时间 Allow Null = False
'7 CardState tinyint 证件状态 Allow Null = False （0－有效，1－过期，2－挂失）

Public CardNo As String
Public Reader As String
Public WorkPlace As String
Public IDCard As String
Public CTypeID As String
Public CreateDate As Date
Public CardState As Byte

'初始化
Public Sub Init()
  CardNo = ""
  Reader = ""
  WorkPlace = ""
  IDCard = ""
  CTypeID = ""
  CreateDate = Now
  CardState = 0
End Sub

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

'根据借阅证号得到其他信息
Public Function GetInfo(ByVal paraCNo As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardInfo WHERE CardNo = '" + Trim(StrReplace(paraCNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则需要初始化
    Init
    '返回False
    GetInfo = False
  Else
    '===========写入RecordSet集中的数据===============
    '写入借阅证号
    CardNo = Trim(paraCNo)
    '得到读者姓名
    Reader = Trim(rs.Fields(1))
    '得到工作单位
    If IsNull(rs.Fields(2)) Then
      WorkPlace = ""
    Else
      WorkPlace = Trim(rs.Fields(2))
    End If
    '得到身份证号
    IDCard = Trim(rs.Fields(3))
    '得到证件类型编号
    CTypeID = Trim(rs.Fields(4))
    '得到办证时间
    CreateDate = rs.Fields(5)
    '得到办证状态
    CardState = rs.Fields(6)
    '=========写入RecordSet集中的数据成功=============

    '返回True
    GetInfo = True
  End If

  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '设置INSERT语句
  SQLStmt = "INSERT INTO CardInfo VALUES('" + Trim(StrReplace(CardNo)) _
          + "', '" + Trim(StrReplace(Reader)) + "', '" _
          + Trim(StrReplace(WorkPlace)) + "', '" + Trim(IDCard) _
          + "', '" + Trim(CTypeID) + "', " + Format(CreateDate, "yyyy-mm-dd") + ", " _
          + Trim(Str(CardState)) + ")"
  '执行INSERT语句
  SQLExt SQLStmt

  '===========修正办证时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardInfo WHERE CardNo = '" + Trim(StrReplace(CardNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(5) = Format(CreateDate, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'更新数据
Public Sub Update(ByVal paraCNo As String)
  SQLStmt = "UPDATE CardInfo SET Reader = '" + Trim(StrReplace(Reader)) _
          + "', WorkPlace = '" + Trim(StrReplace(WorkPlace)) + "', IDCard = '" _
          + Trim(IDCard) + "', CTypeID = '" + Trim(CTypeID) + "', CreateDate = " _
          + Format(CreateDate, "yyyy-mm-dd") + ", CardState = " _
          + Trim(Str(CardState)) + " WHERE CardNo = '" _
          + Trim(StrReplace(paraCNo)) + "'"
  '执行UPDATE语句
  SQLExt SQLStmt

  '===========修正办证时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardInfo WHERE CardNo = '" + Trim(StrReplace(CardNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(5) = Format(CreateDate, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'修改办证日期
Public Sub UpdateDate(ByVal paraCNo As String)
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM CardInfo WHERE CardNo = '" + Trim(StrReplace(paraCNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(5) = Format(Now, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'修改证件状态
Public Sub UpdateState(ByVal paraCNo As String, ByVal paraState As Byte)
  SQLStmt = "UPDATE CardInfo SET CardState = " + Trim(Str(paraState)) _
          + " WHERE CardNo = '" + Trim(StrReplace(paraCNo)) + "'"
  SQLExt SQLStmt
End Sub

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