frmstatus.vb

humar resourse management

        Me.Controls.Add(Me.cbxCompany)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.txtID)
        Me.Controls.Add(Me.Label9)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.Label7)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.ForeColor = System.Drawing.Color.Black
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "frmStatus"
        Me.ShowInTaskbar = False
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "     Status of Candidate"
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim Fdt As New DateTime
    Dim MDate, SDate, DDate As Date
    Dim IDate As String
    Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN") ' Declared in App.Config File for Connection String
    Dim Conn As New OleDbConnection(ConnectionString)
    Dim DA As OleDbDataAdapter
    Dim DS As DataSet
    Dim DT As DataTable
    Dim DR As DataRow
    Dim QueryExist As String
    Dim QueryNew As String
    Dim Flag As String
    Dim PKID As Integer
    Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
        Reset()
        Try
            QueryExist = "SELECT Candidate_Personal.ID, Candidate_Personal.Name, Candidate_Passport.PassportNumber, Agent.Name, Status.ClientID, Status.JobID,Status.ILocation, Status.IDate, Status.IStatus, Status.MStatus, Status.MDate, Status.VStatus, Status.VDate, Status.Departure, Status.DDate, Status.Remarks, Status.ID1 FROM Candidate_Personal, Candidate_Passport, Agent, Status WHERE (Status.ID=" & Val(txtID.Text) & ") AND (Candidate_Personal.ID = Status.ID) AND (Status.ID = Candidate_Passport.ID)"  'initial after braces are before quote AND (Candidate_Personal.AgentID = Agent.ID)"
            DS = New DataSet
            DA = New OleDbDataAdapter(QueryExist, Conn)
            DS.Clear()
            DA.Fill(DS, "Status")
            DT = DS.Tables("Status")
            If DT.Rows.Count = 0 Then 'NO Record Available in Status Table. Check in Candidate Tables
                QueryNew = "SELECT Candidate_Personal.ID, Candidate_Personal.Name, Candidate_Passport.PassportNumber, Agent.Name FROM Candidate_Personal, Candidate_Passport, Agent WHERE (Candidate_Personal.ID=" & Val(txtID.Text) & ") AND (Candidate_Personal.ID = Candidate_Passport.ID) AND (Candidate_Personal.AgentID = Agent.ID)"
                DA = New OleDbDataAdapter(QueryNew, Conn)
                Dim DS As New DataSet
                DS.Clear()
                DA.Fill(DS, "Status")
                Dim DT As DataTable
                DT = DS.Tables("Status")
                If DT.Rows.Count = 0 Then
                    MsgBox("NO Record Found For Given ID")
                    Return
                Else
                    Flag = "NEW"
                    Reset()
                    DR = DT.Rows(0)
                    txtID.Text = DR(0)
                    txtName.Text = DR(1)
                    txtPPNo.Text = DR(2)
                    txtAgent.Text = DR(3)
                End If
            Else ' Record Already Available in Status Table. Retrieve Data
                Flag = "EXIST"
                DR = DT.Rows(0)
                txtID.Text = DR(0)
                txtName.Text = DR(1)
                txtPPNo.Text = DR(2)
                txtAgent.Text = DR(3)
                cbxCompany.SelectedValue = Val(DR(4))
                cbxJob.SelectedValue = Val(DR(5))
                txtLocation.Text = DR(6)
                If Not DR(7) = "01-Jan-1800" Then
                    dtpIDate.Text = DR(7)
                    'BUG (Not sure) - Following line required to fix the bug. 
                    'If we donot include this line, GUI shows check box is checked
                    'but when we check the status in code (dtpIDate.Checked), it will return False
                    'I also noticed that if the retrieved date is today date, the check box does not check itself 
                    'If we donot add following line.
                    dtpIDate.Checked = True
                Else 'UnCheck the CheckBox and Set Today Date
                    dtpIDate.Text = Date.Today
                    dtpIDate.ShowCheckBox = False
                    dtpIDate.Checked = False
                    dtpIDate.ShowCheckBox = True
                End If
                If Not DR(10) = "01-Jan-1800" Then
                    dtpMDate.Text = DR(10)
                    dtpMDate.Checked = True
                Else 'UnCheck the CheckBox and Set Today Date
                    dtpMDate.Text = Date.Today
                    dtpMDate.ShowCheckBox = False
                    dtpMDate.Checked = False
                    dtpMDate.ShowCheckBox = True
                End If
                If Not DR(12) = "01-Jan-1800" Then
                    dtpSDate.Text = DR(12)
                    dtpSDate.Checked = True
                Else 'UnCheck the CheckBox and Set Today Date
                    dtpSDate.Text = Date.Today
                    dtpSDate.ShowCheckBox = False
                    dtpSDate.Checked = False
                    dtpSDate.ShowCheckBox = True
                End If
                If Not DR(14) = "01-Jan-1800" Then
                    dtpDDate.Text = DR(14)
                    dtpDDate.Checked = True
                Else 'UnCheck the CheckBox and Set Today Date
                    dtpDDate.Text = Date.Today
                    dtpDDate.ShowCheckBox = False
                    dtpDDate.Checked = False
                    dtpDDate.ShowCheckBox = True
                End If
                cbxIStatus.SelectedItem = DR(8)
                cbxMStatus.SelectedItem = DR(9)
                cbxVStatus.SelectedItem = DR(11)
                cbxDStatus.SelectedItem = DR(13)
                txtRemarks.Text = DR(15)
                PKID = Val(DR(16))
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub UpdateStatus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Store()
        Dim UpdateSQL As String
        Dim oUpdate As New Update
        Select Case Flag
            Case "NEW"
                Check()
                UpdateSQL = "INSERT INTO Status( "
                UpdateSQL &= "Id, ClientId, JobId, ILocation, IDate, IStatus, MStatus, MDate, VStatus, VDate, Departure, DDate, Remarks) Values("
                UpdateSQL &= "" & txtID.Text & ", "
                UpdateSQL &= "" & cbxCompany.SelectedValue & ", "
                UpdateSQL &= "" & cbxJob.SelectedValue & ", "
                UpdateSQL &= "'" & txtLocation.Text & "', "
                UpdateSQL &= "'" & IDate & "', "
                UpdateSQL &= "'" & cbxIStatus.SelectedItem & "', "
                UpdateSQL &= "'" & cbxMStatus.SelectedItem & "', "
                UpdateSQL &= "'" & MDate & "', "
                UpdateSQL &= "'" & cbxVStatus.SelectedItem & "', "
                UpdateSQL &= "'" & SDate & "', "
                UpdateSQL &= "'" & cbxDStatus.SelectedItem & "', "
                UpdateSQL &= "'" & DDate & "', "
                UpdateSQL &= "'" & txtRemarks.Text & "') "
                oUpdate.ORecord(UpdateSQL)
                MsgBox("Successfully Status Record Created")
            Case "EXIST"
                Check()
                UpdateSQL = "UPDATE Status SET "
                UpdateSQL &= "ClientID =" & cbxCompany.SelectedValue & ", "
                UpdateSQL &= "JobID =" & cbxJob.SelectedValue & ", "
                UpdateSQL &= "ILocation ='" & txtLocation.Text & "', "
                UpdateSQL &= "IDate ='" & IDate & "', "
                UpdateSQL &= "IStatus ='" & cbxIStatus.SelectedItem & "', "
                UpdateSQL &= "MStatus ='" & cbxMStatus.SelectedItem & "', "
                UpdateSQL &= "MDate ='" & MDate & "', "
                UpdateSQL &= "VStatus ='" & cbxVStatus.SelectedItem & "', "
                UpdateSQL &= "VDate ='" & SDate & "', "
                UpdateSQL &= "Departure ='" & cbxDStatus.SelectedItem & "', "
                UpdateSQL &= "DDate ='" & DDate & "', "
                UpdateSQL &= "Remarks ='" & txtRemarks.Text & "' "
                UpdateSQL &= "WHERE ID =" & txtID.Text & " "

                oUpdate.ORecord(UpdateSQL)
                MsgBox("Successfully Updated")
            Case Else
                MsgBox("BUG in UpdateStatus...!")
        End Select
        'MsgBox(UpdateSQL)
        Flag = ""
    End Sub
    Sub Store()
        If Not dtpIDate.Checked Then 'NOT the right way to do. Modified later
            Fdt = "01-Jan-1800"
            IDate = Format(Fdt, "dd-MMM-yyyy")
            dtpIDate.Text = Date.Today
            dtpIDate.ShowCheckBox = False
            dtpIDate.Checked = False
            dtpIDate.ShowCheckBox = True
        Else
            Fdt = dtpIDate.Text
            IDate = Format(Fdt, "dd-MMM-yyyy")
        End If
        If Not dtpMDate.Checked Then
            MDate = "01-Jan-1800"
            dtpMDate.Text = Date.Today
            dtpMDate.ShowCheckBox = False
            dtpMDate.Checked = False
            dtpMDate.ShowCheckBox = True
        Else
            MDate = dtpMDate.Text
        End If
        If Not dtpSDate.Checked Then
            SDate = "01-Jan-1800"
            dtpSDate.Text = Date.Today
            dtpSDate.ShowCheckBox = False
            dtpSDate.Checked = False
            dtpSDate.ShowCheckBox = True
        Else
            SDate = dtpSDate.Text
        End If
        If Not dtpDDate.Checked Then
            DDate = "01-Jan-1800"
            dtpDDate.Text = Date.Today
            dtpDDate.ShowCheckBox = False
            dtpDDate.Checked = False
            dtpDDate.ShowCheckBox = True
        Else
            DDate = dtpDDate.Text
        End If
    End Sub
    Sub Reset()
        dtpIDate.Text = Date.Today
        dtpIDate.ShowCheckBox = False
        dtpIDate.Checked = False
        dtpIDate.ShowCheckBox = True

        dtpMDate.Text = Date.Today
        dtpMDate.ShowCheckBox = False
        dtpMDate.Checked = False
        dtpMDate.ShowCheckBox = True

        dtpSDate.Text = Date.Today
        dtpSDate.ShowCheckBox = False
        dtpSDate.Checked = False
        dtpSDate.ShowCheckBox = True

        dtpDDate.Text = Date.Today
        dtpDDate.ShowCheckBox = False
        dtpDDate.Checked = False
        dtpDDate.ShowCheckBox = True

        cbxIStatus.SelectedItem = "Select"
        cbxMStatus.SelectedItem = "Select"
        cbxVStatus.SelectedItem = "Select"
        cbxDStatus.SelectedItem = "Select"
    End Sub
    Private Sub CloseFrm(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
    Dim sConn As OleDbConnection
    Private Sub frmStatus_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "..\HRMS.mdb;Persist Security Info=False")
        Dim JobQuery As String = "SELECT ID, Title FROM Job ORDER BY Title"
        CreateCombo.List(JobQuery, cbxJob, "Job", "Title", "ID")
        Dim ClientQuery As String = "SELECT ID, Name FROM Client ORDER BY Name"
        CreateCombo.List(ClientQuery, cbxCompany, "Client", "Name", "ID")
    End Sub
    Sub Check()
        If cbxIStatus.SelectedItem = "Select" Then
            cbxIStatus.SelectedItem = ""
        End If
        If cbxMStatus.SelectedItem = "Select" Then
            cbxMStatus.SelectedItem = ""
        End If
        If cbxVStatus.SelectedItem = "Select" Then
            cbxVStatus.SelectedItem = ""
        End If
        If cbxDStatus.SelectedItem = "Select" Then
            cbxDStatus.SelectedItem = ""
        End If
    End Sub

    Private Sub txtID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtID.TextChanged

    End Sub

    Private Sub txtLocation_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLocation.TextChanged

    End Sub
End Class