customer.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 = "Customer"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id 文本 50 客户编号
'2 Name  文本 50 驾驶员姓名
'3 Sex 文本 10 性别
'4 Age 数字 整型 年龄
'5 IdCard  文本 20 身份证号
'6 Telephone  文本 50 电话
'7 WorkPlace 文本 50 工作单位
'8 Address 文本 50 地址
'9 ZipCode 文本 10  邮政编码
'10 Email 文本 30 电子邮件
'11 LicenseNo 文本 50  驾驶证号
'12 LicenseType 文本 50 驾照类型
'13 GetDate 文本 20 领驾驶证时间
'14 ExpiredDate 文本 20 到期时间
'15 DriverAge 文本 20 驾龄
'16 Flag 数字 字节 会员标记；0-不是；1-是会员
'17 TypeId 数字 长整型 会员类型
'18 Certificate 文本 50 抵押证件
'19 Warrantor 文本 50 担保人
'20 WIdCard 文本 50 担保人身份证号
'21 WWorkPlace 文本 50 担保人工作单位

Public Id As String
Public Name As String
Public Sex As String
Public Age As Integer
Public IdCard As String
Public Telephone As String
Public WorkPlace As String
Public Address As String
Public ZipCode As String
Public Email As String
Public LicenseNo As String
Public LicenseType As String
Public GetDate As String
Public ExpiredDate As String
Public DriverAge As Integer
Public Flag As Integer
Public TypeId As Long
Public Certificate As String
Public Warrantor As String
Public WIdCard As String
Public WWorkPlace As String

Public Sub Init()
  Id = ""
  Name = ""
  Sex = "男"
  Age = 18
  IdCard = ""
  Telephone = ""
  WorkPlace = ""
  Address = ""
  ZipCode = ""
  Email = ""
  LicenseNo = ""
  LicenseType = ""
  GetDate = ""
  ExpiredDate = ""
  DriverAge = 0
  Flag = 0
  TypeId = 0
  Certificate = ""
  Warrantor = ""
  WIdCard = ""
  WWorkPlace = ""
End Sub

'删除Customer数据
Public Sub Delete(ByVal TmpId As String)
  SqlStmt = "Delete FROM Customer WHERE Id='" + Trim(TmpId) + "'"
  SQLExt (SqlStmt)
End Sub

Public Function GetInfo(ByVal TmpId As String) As Boolean
  Dim rs As New ADODB.Recordset
  Id = TmpId
  SqlStmt = "SELECT * FROM Customer WHERE Id='" + Trim(TmpId) + "'"
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True 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
      Sex = "男"
    Else
      Sex = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      Age = 18
    Else
      Age = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      IdCard = ""
    Else
      IdCard = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      Telephone = ""
    Else
      Telephone = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      WorkPlace = ""
    Else
      WorkPlace = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      Address = ""
    Else
      Address = rs.Fields(7)
    End If
    If IsNull(rs.Fields(8)) Then
      ZipCode = ""
    Else
      ZipCode = rs.Fields(8)
    End If
    If IsNull(rs.Fields(9)) Then
      Email = ""
    Else
      Email = rs.Fields(9)
    End If
    If IsNull(rs.Fields(10)) Then
      LicenseNo = ""
    Else
      LicenseNo = rs.Fields(10)
    End If
    If IsNull(rs.Fields(11)) Then
      LicenseType = ""
    Else
      LicenseType = rs.Fields(11)
    End If
     
     If IsNull(rs.Fields(12)) Then
      GetDate = ""
    Else
      GetDate = rs.Fields(12)
    End If
    If IsNull(rs.Fields(13)) Then
      ExpiredDate = ""
    Else
      ExpiredDate = rs.Fields(13)
    End If
    If IsNull(rs.Fields(14)) Then
      DriverAge = 0
    Else
      DriverAge = rs.Fields(14)
    End If
    If IsNull(rs.Fields(15)) Then
      Flag = 0
    Else
      Flag = rs.Fields(15)
    End If
    If IsNull(rs.Fields(16)) Then
      TypeId = 0
    Else
      TypeId = rs.Fields(16)
    End If
    If IsNull(rs.Fields(17)) Then
      Certificate = ""
    Else
      Certificate = rs.Fields(17)
    End If
    If IsNull(rs.Fields(18)) Then
      Warrantor = ""
    Else
      Warrantor = rs.Fields(18)
    End If
    If IsNull(rs.Fields(19)) Then
      WIdCard = ""
    Else
      WIdCard = rs.Fields(19)
    End If
    If IsNull(rs.Fields(20)) Then
      WWorkPlace = ""
    Else
      WWorkPlace = rs.Fields(20)
    End If
    
  End If
  GetInfo = True
End Function
'判断是否已经存在此客户号
Public Function In_DB(ByVal TmpId As String) As Boolean
  Dim rs As New ADODB.Recordset
  SqlStmt = "SELECT * FROM Customer WHERE Id='" + Trim(TmpId) + "'"
  Set rs = QueryExt(SqlStmt)
  If Not rs.EOF Then
    In_DB = True
  Else
    In_DB = False
  End If
End Function

Public Sub Insert()
  '设置INSERT语句
  SqlStmt = "INSERT INTO Customer(Id,Name,Sex,Age,IdCard,Telephone,WorkPlace," & _
     "Address,ZipCode,Email,LicenseNo,LicenseType,GetDate,ExpiredDate," & _
     "DriverAge,Flag,TypeId,Certificate,Warrantor,WIdCard,WWorkPlace)" & _
     " Values('" + Trim(Id) + "','" + Trim(Name) + "','" + Trim(Sex) + "'," & _
     Trim(Age) + ",'" + Trim(IdCard) + "','" + Trim(Telephone) + "','" & _
     Trim(WorkPlace) + "','" + Trim(Address) + "','" + Trim(ZipCode) + "','" & _
     Trim(Email) + "','" + Trim(LicenseNo) + "','" + Trim(LicenseType) + "','" & _
     Trim(GetDate) + "','" + Trim(ExpiredDate) + "'," + Trim(DriverAge) + "," & _
     Trim(Flag) + "," + Trim(TypeId) + ",'" + Trim(Certificate) + "','" & _
     Trim(Warrantor) + "','" + Trim(WIdCard) + "','" + Trim(WWorkPlace) + "')"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub
'更新驾驶员、会员信息
Public Sub Update(ByVal TmpId As String)
  '设置UPDATE语句
  SqlStmt = "Update Customer Set Name='" + Trim(Name) + "',Sex='" + Trim(Sex) _
     + "',Age=" + Trim(Age) + ",IdCard='" + Trim(IdCard) _
     + "',Telephone='" + Trim(Telephone) + "',WorkPlace='" + Trim(WorkPlace) _
     + "',Address='" + Trim(Address) + "',ZipCode='" + Trim(ZipCode) + "'," _
     + "Email='" + Trim(Email) + "',LicenseNo='" + Trim(LicenseNo) + "'," _
     + "LicenseType='" + Trim(LicenseType) + "',GetDate='" + Trim(GetDate) + "'," _
     + "ExpiredDate='" + Trim(ExpiredDate) + "'," _
     + "DriverAge=" + Trim(DriverAge) + ",Flag=" + Trim(Flag) + "," _
     + "TypeId=" + Trim(TypeId) + ",Certificate='" + Trim(Certificate) + "'," _
     + "Warrantor='" + Trim(Warrantor) + "',WIdCard='" + Trim(WIdCard) + "'," _
     + "WWorkPlace='" + Trim(WWorkPlace) + "'" _
     + " WHERE Id='" + Trim(TmpId) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub
'更改驾驶员状态为会员
Public Sub UpdateToMember(ByVal TmpId As String)
  '设置UPDATE语句
  SqlStmt = "Update Customer Set Flag=1," _
          + "TypeId=" + Trim(TypeId) _
          + " WHERE Id='" + Trim(TmpId) + "'"
  '执行SQL语句
  SQLExt (SqlStmt)
End Sub