discard.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 = "Discard"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id  数字 长整型 自动编号 报废编号
'2 DevId  文本 长度 50  设备编号
'3 DCount  数字 整型 报废数量
'4 DSum 数字 整型 折算金额
'5 Reason  文本 长度 200 报废原因
'6 CreateDate 日期\时间 申请时间
'7 UserName 文本 长度 30 申请人
'8 Checker  文本 长度 30 审批人
'9 CheckDate 日期\时间 审批时间
'10 Result 文本 长度 200 审批意见
'11 Status 文本 20 状态（申请、提交、同意、不同意、报废）

Public Id As Long
Public DevId As String
Public DCount As Integer
Public DSum As Long
Public Reason As String
Public CreateDate As String
Public UserName As String
Public Checker As String
Public CheckDate As String
Public Result As String
Public Status As String

Public Sub Init()
  DevId = ""
  DCount = 1
  DSum = 0
  Reason = ""
  CreateDate = Date
  UserName = ""
  Checker = ""
  CheckDate = ""
  Result = ""
  Status = "申请"
End Sub

'删除维护信息
Public Sub Delete(ByVal TmpId As Long)
  SqlStmt = "DELETE FROM Discard WHERE Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub

Public Function In_DB(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  SqlStmt = "SELECT * FROM Discard WHERE Id=" + Trim(TmpId)
  Set rs = QueryExt(SqlStmt)
  If rs.EOF = True Then
    In_DB = False
  Else
    In_DB = True
  End If
End Function
Public Function GetInfo(ByVal TmpId As Long) As Boolean
  Dim rs As New ADODB.Recordset
  
  '设置SELECT语句，读取编号为TmpId的记录
  SqlStmt = "SELECT * FROM Discard WHERE Id=" + Trim(TmpId)
  '将结果集读取到rs中
  Set rs = QueryExt(SqlStmt)

  If rs.EOF Then
    '如果结果集为空，则初始化
    Init
    GetInfo = False
  Else
    '将结果集中的数据赋值到成员变量中
    If IsNull(rs.Fields(0)) Then
      Id = 0
    Else
      Id = rs.Fields(0)
    End If
    If IsNull(rs.Fields(1)) Then
      DevId = ""
    Else
      DevId = rs.Fields(1)
    End If
    If IsNull(rs.Fields(2)) Then
      DCount = 1
    Else
      DCount = rs.Fields(2)
    End If
    If IsNull(rs.Fields(3)) Then
      DSum = 0
    Else
      DSum = rs.Fields(3)
    End If
    If IsNull(rs.Fields(4)) Then
      Reason = ""
    Else
      Reason = rs.Fields(4)
    End If
    If IsNull(rs.Fields(5)) Then
      CreateDate = ""
    Else
      CreateDate = rs.Fields(5)
    End If
    If IsNull(rs.Fields(6)) Then
      UserName = ""
    Else
      UserName = rs.Fields(6)
    End If
    If IsNull(rs.Fields(7)) Then
      Checker = ""
    Else
      Checker = rs.Fields(7)
    End If
    If IsNull(rs.Fields(8)) Then
      CheckDate = ""
    Else
      CheckDate = rs.Fields(8)
    End If
    If IsNull(rs.Fields(9)) Then
      Result = ""
    Else
      Result = rs.Fields(9)
    End If
    If IsNull(rs.Fields(10)) Then
      Status = ""
    Else
      Status = rs.Fields(10)
    End If
  
    GetInfo = True
  End If
End Function
   
'插入分类
Public Sub Insert()
  SqlStmt = "INSERT INTO Discard(DevId,DCount,DSum," & _
          "Reason,CreateDate,UserName,Status) " & _
          " VALUES('" + Trim(DevId) + "'," & _
          Trim(DCount) + "," + Trim(DSum) + ",'" + Trim(Reason) + "','" & _
          Trim(CreateDate) + "','" + Trim(UserName) + "','申请')"
  SQLExt (SqlStmt)
End Sub
'更新数据
Public Sub Update(ByVal TmpId As Long)
   SqlStmt = "UPDATE Discard SET DevId='" + Trim(DevId) & _
          "',DCount=" + Trim(DCount) + ",DSum=" + Trim(DSum) + "," & _
          "Reason='" + Trim(Reason) + "',CreateDate='" & _
          Trim(CreateDate) + "',UserName='" + Trim(UserName) + "'" & _
          " Where Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub
'更新状态
Public Sub UpdateStatus(ByVal TmpId As Long, ByVal iFlag As Integer)
  '根据iFlag值选择更新内容，iFlag=1提交时更新审批人和状态
  'iFlag=其它，只是更新状态
  If iFlag = 1 Then
    SqlStmt = "UPDATE Discard SET Checker='" + Trim(Checker) + "'," & _
          "Status='" + Trim(Status) + "' " & _
          "Where Id=" + Trim(TmpId)
  Else
    SqlStmt = "UPDATE Discard SET Checker='" + Trim(Checker) + "'," & _
          "Status='" + Trim(Status) + "' " & _
          "Where Id=" + Trim(TmpId)
  End If
  SQLExt (SqlStmt)
End Sub
'审批人更新意见
Public Sub UpdateApproval(ByVal TmpId As Long)
   SqlStmt = "UPDATE Discard SET CheckDate='" + Trim(CheckDate) + "'," & _
          "Result='" + Trim(Result) + "',Status='" & _
          Trim(Status) + "' " & _
          " Where Id=" + Trim(TmpId)
  SQLExt (SqlStmt)
End Sub