Version 1.0 Class
Begin
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
End
Attribute VB_Name = "ClsFamilyMember"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Id  TinyInt 編號
'2 Emp_Id  Int 員工編號
'3 Name  Varchar 50  姓名
'4 Sex Char 2  性別
'5 Age TinyInt 年齡
'6 Relationship  Varchar 20  與本人關系
'7 WorkingOrg  Varchar 40  工作單位
Public Id As String
Public Emp_Id As String
Public Name As String
Public Sex As String
Public Age As Integer
Public Relationship As String
Public WorkingOrg As String

Public Sub Init()
    Id = 0
    Emp_Id = 0
    Name = ""
    Sex = ""
    Age = 0
    Relationship = ""
    WorkingOrg = ""
End Sub

'刪除FamilyMember數據

Public Sub DeleteById(ByVal TmpEmpId As Long, _
                      ByVal TmpId As Integer)
    '連接數據庫
    MyDB.openDB
    
    SqlStmt = "DELETE FROM FamilyMember WHERE Id=" _
               + Trim(Str(TmpId)) + " And Emp_Id=" _
               + Trim(Str(TmpEmpId))
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    '斷開與數據庫的連接
    MyDB.closeDB
End Sub

'刪除FamilyMember數據

Public Sub DeleteByEmpId(ByVal TmpEmpId As Long)
    '連接數據庫
    MyDB.openDB
    
    SqlStmt = "DELETE FROM FamilyMember WHERE EmpId=" + Trim(Str(TmpEmpId))
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    '斷開與數據庫的連接
    MyDB.closeDB
End Sub

Public Function GetInfo(ByVal TmpEmpId As Long, _
                        ByVal TmpId As Long) As Boolean
    If TmpId <= 0 Or TmpEmpId <= 0 Then
        GetInfo = False
        Exit Function
    End If
    
    Id = TmpId
    Emp_Id = TmpEmpId
    
    '連接數據庫
    MyDB.openDB
    
    SqlStmt = "SELECT * FROM FamilyMember WHERE Id=" _
               + Trim(Str(TmpId)) + " And Emp_Id=" _
               + Trim(Str(Emp_Id))
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    If rsFamilyMember.RecordCount = 0 Then
        GetInfo = False
        Exit Function
    Else
        
        Name = rsFamilyMember.Fields("Name")
        
        
        Sex = rsFamilyMember.Fields("Sex")
        
        
        Age = rsFamilyMember.Fields("Age")
        
        
        Relationship = rsFamilyMember.Fields("Relationship")
        
        
        WorkingOrg = rsFamilyMember.Fields("WorkingOrg")
    End If
    
    
    
    GetInfo = True
    
    '斷開與數據庫的連接
    MyDB.closeDB
    
End Function

Public Function GetNewId() As Long
    Dim TmpId As Long
    
    '連接數據庫
    MyDB.openDB
    
    SqlStmt = "SELECT Id FROM FamilyMember ORDER BY Id"
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    i = 1
    Do While rsFamilyMember.EOF = False
        
        TmpId = rsFamilyMember.Fields("Id")
        If TmpId = i Then
            i = i + 1
        Else
            GetNewId = i
            '斷開與數據庫的連接
            MyDB.closeDB
            Exit Function
        End If
        rsFamilyMember.MoveNext
    Loop
    '斷開與數據庫的連接
    MyDB.closeDB
    
    GetNewId = i
End Function

Public Sub Insert(ByVal TmpEmpId As Long)
    Id = GetNewId()
    
    '連接數據庫
    MyDB.openDB
    
    SqlStmt = "INSERT INTO FamilyMember Values(" _
               + Trim(Str(Id)) + "," + Trim(Str(TmpEmpId)) _
               + ",'" + Trim(Name) + "','" + Trim(Sex) + "'," _
               + Trim(Str(Age)) + ",'" + Trim(Relationship) + "','" _
               + Trim(WorkingOrg) + "')"
    
    
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    '斷開與數據庫的連接
    MyDB.closeDB
    
End Sub

Public Sub Update(ByVal OriEmpId As Long, _
                  ByVal OriId As Long)
    '連接數據庫
    MyDB.openDB
    
    
    SqlStmt = "Update FamilyMember Set Name='" + Trim(Name) _
               + "',Sex='" + Trim(Sex) + "',Age=" + Trim(Str(Age)) _
               + ",Relationship='" + Trim(Relationship) _
               + "',WorkingOrg='" + Trim(WorkingOrg) _
               + "' WHERE Emp_Id=" + Trim(Str(OriEmpId)) _
               + " And Id=" + Trim(Str(OriId))
    
    
    '執行SQL語句
    Dim Isexe As Boolean
    Dim rsFamilyMember As ADODB.Recordset
    Isexe = MyDB.exeSql(SqlStmt, rsFamilyMember, True)
    
    '斷開與數據庫的連接
    MyDB.closeDB
    
End Sub