insert.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 Insert
    Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN") ' Declared in App.Config File for Connection String
    Dim MyConnection As New OleDbConnection(ConnectionString)
    Dim NewID As Integer
    Public Function Record(ByVal Query As String)
        Try
            OpenConnection()
            iOthers(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 NewRecord(ByVal oDetails As Details)
        Try
            OpenConnection()

            iPersonal("insert into Candidate_Personal (AppliedOn,AgentID,PJobID,SJObID,Name,FatherName,DateOfBirth,PlaceOfBirth,Address,City,Pincode,Phone,Mobile,Email,Profile) values ('" & oDetails.AppliedOn & "', " & oDetails.Agent & ", " & oDetails.PPost & ", " & oDetails.SPost & ", '" & oDetails.Name & "', '" & oDetails.FName & "', '" & oDetails.DoB & "', '" & oDetails.PoB & "', '" & oDetails.Address & "', '" & oDetails.City & "', '" & oDetails.Pincode & "', '" & oDetails.Phone & "', '" & oDetails.Mobile & "', '" & oDetails.Email & "','" & oDetails.Profile & "') ")
            oDetails.ID = NewID
            If oDetails.PPNo <> "" Then
                iOthers("insert into Candidate_Passport (ID,PassportNumber,PlaceOfIssue,DateOfIssue,DateOfExpiry) Values (" & oDetails.ID & ", '" & oDetails.PPNo & "','" & oDetails.PoI & "','" & oDetails.DoI & "','" & oDetails.DoE & "')")
            End If
            If oDetails.Institute <> "" Then
                iOthers("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
                iOthers("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
                iOthers("insert into Candidate_Educational (ID, Institute, Course, Major, YearOfPassed) Values (" & oDetails.ID & ", '" & oDetails.InstituteNew2 & "', '" & oDetails.CourseNew2 & "', '" & oDetails.MajorNew2 & "', '" & oDetails.YoPNew2 & "')")
            End If

            If oDetails.Company1 <> "" Then
                iOthers("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
                iOthers("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
                iOthers("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
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If MyConnection.State = ConnectionState.Open Then
                CloseConnection()
            End If
        End Try
    End Function

    Public Sub OpenConnection() ' Establish the connection once and run all insert query. Then Close the connection
        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 iPersonal(ByVal Query As String)
        Try
            Dim myCommand As New OleDbCommand(Query, MyConnection)
            Dim myCommand1 As New OleDbCommand("SELECT @@IDENTITY", MyConnection) 'This logic will be improved shortly
            myCommand.ExecuteNonQuery()
            NewID = myCommand1.ExecuteScalar
        Catch ex As Exception
            MsgBox("Error at Insert.iPersonal: " & ex.Message)
        End Try
    End Function
    Public Function iOthers(ByVal Query As String)
        Try
            Dim myCommand As New OleDbCommand(Query, MyConnection)
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error at Insert.iOthers: " & ex.Message)
        End Try
    End Function

End Class