change.cls

《SQL Server 2000课程设计案例精编》-李昆-源代码-3436

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Change"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Change_Id  Int 变动编号
'2 ChangeType  Tinyint 职务/部门变动类型（1表示职务调动，2表示部门调动）
'3 Staff_Id  Int 员工编号
'4 ChangeDate  Char 10 变动时间
'5 Pre_String  Varchar 10 曾任职务/部门
'6 Now_String  Varchar 10 现任职务/部门
'7 Reason  Varchar 200 变动原因

Public Change_Id As Long
Public Change_Type As Integer
Public Staff_Id As Long
Public ChangeDate As String
Public Pre_String As String
Public Now_String As String
Public Reason As String

Public Sub Init()
  Change_Id = 0
  Staff_Id = 0
  Change_Type = 0
  ChangeDate = ""
  Pre_String = ""
  Now_String = ""
  Reason = ""
  
End Sub

'删除单个数据
Public Sub DeleteById(ByVal TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Change WHERE Change_Id=" + Trim(Str(TmpId))

  
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

'删除指定员工的所有数据
Public Sub DeleteByStaff(ByVal TmpStaffId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Change WHERE Staff_Id=" + Trim(Str(TmpStaffId))
  
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetInfo(ByVal TmpId As Long) As Boolean
  If TmpId <= 0 Then
    GetInfo = False
    Init
    Exit Function
  End If
  
  Change_Id = TmpId
  
  DB_Connect

  SqlStmt = "SELECT * FROM Change WHERE Change_Id=" + Trim(Str(TmpId))
  
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Init
    Exit Function
  Else
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    Change_Type = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblench)
    Staff_Id = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Now_String = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Pre_String = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    ChangeDate = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Reason = TrimStr(ColVal)
    
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Sub Insert()
  '连接数据库
  Change_Id = GetNewId
  DB_Connect
  'INSERT语句

    SqlStmt = "INSERT INTO Change (Change_Id ,Change_Type, Staff_Id, Now_String,Pre_String, ChangeDate ," _
            + " Reason) Values(" + Trim(Change_Id) + "," + Trim(Change_Type) + "," + Trim(Staff_Id _
            ) + ",'" + Trim(Now_String) + "','" + Trim(Pre_String) + "','" + Trim(ChangeDate _
            ) + "','" + Trim(Reason) + "')"
            
  '执行INSERT语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

Public Sub Update(ByVal TmpId As Long)
  '连接数据库
  DB_Connect
  'UPDATE语句

    SqlStmt = "Update Change Set Change_Type = " + Trim(Change_Type) + ", Staff_Id=" + Trim(Staff_Id) _
               + ", Now_String ='" + Trim(Now_String) + "', Pre_String ='" + Trim(Pre_String) + "', ChangeDate = '" _
               + Trim(ChangeDate) + "', Reason = '" + Trim(Reason) + "' WHERE Change_Id =" + Trim(TmpId)

  '执行UPDATE语句
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

Public Function GetNewId() As Long
  Dim TmpId As Long
  
  DB_Connect
    
  SqlStmt = "SELECT Change_Id FROM Change ORDER BY Change_Id"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
  
  GetNewId = i
End Function