update.vb

humar resourse management

' Human Resources Management System (HRMS) 
'
' Programmed by: Nura Tijjani Abubakar ([e-mail])
' Submitted to: Mr. Ravindran Kanapathy
'
' Oxford Brookes University (OBU)
' Final Year Project (November, 2007)

Imports System.Data
Imports System.Data.OleDb
Public Class Update
    Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN") ' Declared in App.Config File for Connection String
    Dim MyConnection As New OleDbConnection(ConnectionString)
    Dim UpdateSQL As String
    Public Function ORecord(ByVal Query As String) 'Other Tables
        Try
            OpenConnection()
            Update(Query)
        Catch ex As Exception
            MessageBox.Show("Error at Insert.Record: " & ex.Message)
        Finally
            If MyConnection.State = ConnectionState.Open Then
                MyConnection.Close()
            End If
        End Try
    End Function
    Public Function Record(ByVal oDetails As Details) As Boolean
        Try
            OpenConnection()
            'Update Personal Details
            UpdateSQL = "UPDATE Candidate_Personal SET "
            UpdateSQL &= "AgentID =" & oDetails.Agent & ", "
            UpdateSQL &= "PJobID =" & oDetails.PPost & ", "
            UpdateSQL &= "SJobID =" & oDetails.SPost & ", "
            UpdateSQL &= "Name ='" & oDetails.Name & "', "
            UpdateSQL &= "FatherName ='" & oDetails.FName & "', "
            UpdateSQL &= "DateOfBirth ='" & oDetails.DoB & "', "
            UpdateSQL &= "PlaceOfBirth ='" & oDetails.PoB & "', "
            UpdateSQL &= "Address ='" & oDetails.Address & "', "
            UpdateSQL &= "City ='" & oDetails.City & "', "
            UpdateSQL &= "Pincode ='" & oDetails.Pincode & "', "
            UpdateSQL &= "Phone ='" & oDetails.Phone & "', "
            UpdateSQL &= "Mobile ='" & oDetails.Mobile & "', "
            UpdateSQL &= "Email ='" & oDetails.Email & "', "
            UpdateSQL &= "Profile ='" & oDetails.Profile & "' "
            UpdateSQL &= "WHERE ID =" & oDetails.ID & " "
            Update(UpdateSQL)

            'Update Passport Details 
            Update("Delete from Candidate_Passport WHERE ID =" & oDetails.ID & " ")
            If oDetails.PPNo <> "" Then
                Update("INSERT INTO Candidate_Passport (ID, PassportNumber, PlaceOfIssue, DateOfIssue, DateOfExpiry) Values (" & oDetails.ID & ", '" & oDetails.PPNo & "', '" & oDetails.PoI & "', '" & oDetails.DoI & "','" & oDetails.DoE & "')")
            End If

            'Update Educational Details
            Update("Delete from Candidate_Educational WHERE ID =" & oDetails.ID & " ")
            If oDetails.Institute <> "" Then
                Update("insert into Candidate_Educational (ID, Institute, Course, Major, YearOfPassed) Values (" & oDetails.ID & ", '" & oDetails.Institute & "', '" & oDetails.Course & "', '" & oDetails.Major & "', '" & oDetails.YoP & "')")
            End If
            If oDetails.InstituteNew1 <> "" Then
                Update("insert into Candidate_Educational (ID, Institute, Course, Major, YearOfPassed) Values (" & oDetails.ID & ", '" & oDetails.InstituteNew1 & "', '" & oDetails.CourseNew1 & "', '" & oDetails.MajorNew1 & "', '" & oDetails.YoPNew1 & "')")
            End If
            If oDetails.InstituteNew2 <> "" Then
                Update("insert into Candidate_Educational (ID, Institute, Course, Major, YearOfPassed) Values (" & oDetails.ID & ", '" & oDetails.InstituteNew2 & "', '" & oDetails.CourseNew2 & "', '" & oDetails.MajorNew2 & "', '" & oDetails.YoPNew2 & "')")
            End If
            'Update Experience Details
            Update("Delete from Candidate_Experience WHERE ID =" & oDetails.ID & " ")
            If oDetails.Company1 <> "" Then
                If oDetails.Company1 <> "" Then
                    Update("insert into Candidate_Experience (ID, CompanyName, Country, Years,Months,JobTitle) Values (" & oDetails.ID & ", '" & oDetails.Company1 & "', '" & oDetails.Country1 & "', '" & oDetails.Years1 & "', '" & oDetails.Months1 & "', '" & oDetails.Job1 & "')")
                End If
                If oDetails.Company2 <> "" Then
                    Update("insert into Candidate_Experience (ID, CompanyName, Country, Years,Months,JobTitle) Values (" & oDetails.ID & ", '" & oDetails.Company2 & "', '" & oDetails.Country2 & "', '" & oDetails.Years2 & "', '" & oDetails.Months2 & "', '" & oDetails.Job2 & "')")
                End If
                If oDetails.Company3 <> "" Then
                    Update("insert into Candidate_Experience (ID, CompanyName, Country, Years,Months,JobTitle) Values (" & oDetails.ID & ", '" & oDetails.Company3 & "', '" & oDetails.Country3 & "', '" & oDetails.Years3 & "', '" & oDetails.Months3 & "', '" & oDetails.Job3 & "')")
                End If
            End If
            Return True
        Catch ex As Exception
            MessageBox.Show("Error at Update.Record" & ex.Message)
            Return False
        Finally
            If MyConnection.State = ConnectionState.Open Then
                CloseConnection()
            End If
        End Try
    End Function
    Public Sub OpenConnection() 
        Try
            MyConnection.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub CloseConnection()
        If MyConnection.State = ConnectionState.Open Then
            MyConnection.Close()
        End If
    End Sub
    Public Function Update(ByVal Query As String)
        Try
            Dim myCommand As New OleDbCommand(Query, MyConnection)
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error at Update.Update: " & ex.Message & ex.Source)
        End Try
    End Function
End Class