cards.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 = "Cards"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Cardno  文本 30  借阅证编号
'2 Name 文本 50 姓名
'3 WorkPlace 文本 50 工作单位
'4 Idcard  文本 50  有效证件号码
'5 TypeId 数字 长整型 证件类型
'6 CreateDate  时间\日期 发证日期
'7 Status  文本 20 表示当前状态：有效、挂失、注销

Public Cardno As String
Public Name As String
Public WorkPlace As String
Public Idcard As String
Public TypeId As Long
Public CreateDate As String
Public Status As String

Public Sub Init()
  Cardno = ""
  Name = ""
  WorkPlace = ""
  Idcard = ""
  TypeId = 0
  CreateDate = Date
  Status = "有效"
End Sub

'删除数据
Public Sub Delete(ByVal TmpCardNo As String)
  SqlStmt = "DELETE FROM Cards WHERE Cardno='" + Trim(TmpCardNo) + "'"
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpCardNo As String) As Boolean
  If Len(Trim(TmpCardNo)) = 0 Then
    Init
    GetInfo = False
    Exit Function
  End If
  Dim rs As New ADODB.Recordset
  
  Cardno = TmpCardNo
  SqlStmt = "SELECT * FROM Cards WHERE Cardno='" + Trim(TmpCardNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetInfo = False
    Exit Function
  Else
    If IsNull(rs.Fields(1)) Then
      Name = ""
    Else
      Name = rs.Fields(1)
    End If
  
    If IsNull(rs.Fields(2)) Then
      WorkPlace = ""
    Else
      WorkPlace = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      Idcard = ""
    Else
      Idcard = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      CreateDate = ""
    Else
      CreateDate = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      TypeId = 0
    Else
      TypeId = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      Status = ""
    Else
      Status = rs.Fields(6)
    End If
  End If
  GetInfo = True
End Function
'判断借阅证号是否存在
Public Function In_DB(ByVal TmpCardNo As String) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Cards WHERE CardNo='" + Trim(TmpCardNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    In_DB = True
  Else
    In_DB = False
  End If
End Function

Public Function GetStatus(ByVal TmpCardNo As String) As String
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT Status FROM Cards WHERE Cardno='" + Trim(TmpCardNo) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF Then
    GetStatus = ""
    Exit Function
  Else
    If IsNull(rs.Fields(0)) Then
      GetStatus = ""
    Else
      GetStatus = rs.Fields(0)
    End If
  End If
End Function


Public Sub Insert()
  SqlStmt = "INSERT INTO Cards(CardNo,Name,WorkPlace,IdCard,TypeId,CreateDate,Status)" _
          + " Values('" + Trim(Cardno) + "','" + Trim(Name) + "','" _
          + Trim(WorkPlace) + "','" + Trim(Idcard) + "'," _
          + Trim(TypeId) + ",'" + Trim(CreateDate) + "','" + Trim(Status) + "')"
  SQLExt (SqlStmt)
End Sub

Public Sub Update(ByVal TmpCardNo As String)
  SqlStmt = "Update Cards Set Name='" + Trim(Name) + "',WorkPlace='" _
          + Trim(WorkPlace) + "', Idcard='" + Trim(Idcard) + "', CreateDate='" _
          + Trim(CreateDate) + "', Status='" + Trim(Status) + "' WHERE Cardno='" _
          + Trim(TmpCardNo) + "'"
  SQLExt (SqlStmt)
End Sub
'更改状态标志为挂失Status=StatusNum
Public Sub UpdateStatus(ByVal TmpCardNo As String, ByVal TmpStatus As String)
  SqlStmt = "Update Cards Set Status='" + Trim(TmpStatus) + "' WHERE Cardno='" + Trim(TmpCardNo) + "'"
  SQLExt (SqlStmt)
End Sub