thisisdog

Imports System.Data
Imports System.Data.SqlClient

Public Class frmStaff
    Dim inc As Integer
    Dim MAXROWS As Integer

    Dim con As New SqlClient.SqlConnection
    Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Dim sql As String

    Dim dt As New DataTable
    Dim rowIndex As Integer = 0
    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf;Trusted_Connection=Yes;Integrated Security=False;"
        con.Open()

        sql = "SELECT * FROM Staff"
        da = New SqlClient.SqlDataAdapter(sql, con)
        da.Fill(ds, "Staff")

        con.Close()

        MAXROWS = ds.Tables("Staff").Rows.Count
        inc = -1

        Dim connStr As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf;Trusted_Connection=Yes;Integrated Security=False;"
        Dim sqlStr As String = "SELECT * FROM Staff"
        Dim dataAdapater As New SqlClient.SqlDataAdapter(sqlStr, connStr)
        dataAdapater.Fill(dt)
        dataAdapater.Dispose()
        UpdateTextBoxes()

    End Sub
    Private Sub Recordsatdatabase()

        txtStaffID.Text = ds.Tables("Staff").Rows(inc).Item("StaffID")
        txtStaffName.Text = ds.Tables("Staff").Rows(inc).Item("StaffName")
        cbPosition.Text = ds.Tables("Staff").Rows(inc).Item("StaffPosition")
        rbMale.Checked = ds.Tables("Staff").Rows(inc).Item("StaffGenderMale")
        rbFemale.Checked = ds.Tables("Staff").Rows(inc).Item("StaffGenderFemale")
        txtAddress1.Text = ds.Tables("Staff").Rows(inc).Item("StaffAddress1")
        txtAddress2.Text = ds.Tables("Staff").Rows(inc).Item("StaffAddress2")
        txtDOB.Text = ds.Tables("Staff").Rows(inc).Item("StaffDOB")
        txtContactNo.Text = ds.Tables("Staff").Rows(inc).Item("StaffContactNo")
        txtRegistrationDate.Text = ds.Tables("Staff").Rows(inc).Item("StaffRegistrationDate")
        txtCountry.Text = ds.Tables("Staff").Rows(inc).Item("StaffCountry")
        cbICColor.Text = ds.Tables("Staff").Rows(inc).Item("StaffICColor")
        txtICNo.Text = ds.Tables("Staff").Rows(inc).Item("StaffICNo")
        txtLabel.Text = CStr(dt.Rows(inc)("StaffPhoto"))
        txtAnnualLeave.Text = ds.Tables("Staff").Rows(inc).Item("StaffAnnualLeave")

        PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
    End Sub
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> MAXROWS - 1 Then
            inc = inc + 1
            Recordsatdatabase()
        Else
            MsgBox("You are at the last record!")
        End If

    End Sub
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If inc > 0 Then
            inc = inc - 1
            Recordsatdatabase()
        Else
            MsgBox("You are at the first record!")
        End If
    End Sub
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            Recordsatdatabase()
        Else
            MsgBox("You are at the first record already!")
        End If
    End Sub
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MAXROWS - 1 Then
            inc = MAXROWS - 1
            Recordsatdatabase()
        Else
            MsgBox("You are at the end of the record!")
        End If
    End Sub
    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        btnConfirm.Enabled = True
        btnNew.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False

        txtStaffID.Clear()
        txtStaffName.Clear()
        cbPosition.Text = "Select"
        rbMale.Checked = False
        rbFemale.Checked = False
        txtAddress1.Clear()
        txtAddress2.Clear()
        txtContactNo.Clear()
        txtCountry.Clear()
        cbICColor.Text = "Select"
        txtICNo.Text = "Clear"
        PictureBox1.Image = Nothing
        txtLabel.Text = "00.jpg"

    End Sub
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New SqlClient.SqlCommandBuilder(da)

        ds.Tables("Staff").Rows(inc).Item("StaffID") = txtStaffID.Text
        ds.Tables("Staff").Rows(inc).Item("StaffName") = txtStaffName.Text
        ds.Tables("Staff").Rows(inc).Item("StaffPosition") = cbPosition.Text
        ds.Tables("Staff").Rows(inc).Item("StaffGenderMale") = rbMale.Checked
        ds.Tables("Staff").Rows(inc).Item("StaffGenderFemale") = rbFemale.Checked
        ds.Tables("Staff").Rows(inc).Item("StaffAddress1") = txtAddress1.Text
        ds.Tables("Staff").Rows(inc).Item("StaffAddress2") = txtAddress2.Text
        ds.Tables("Staff").Rows(inc).Item("StaffDOB") = txtDOB.Text
        ds.Tables("Staff").Rows(inc).Item("StaffContactNo") = txtContactNo.Text
        ds.Tables("Staff").Rows(inc).Item("StaffRegistrationDate") = txtRegistrationDate.Text
        ds.Tables("Staff").Rows(inc).Item("StaffPhoto") = txtLabel.Text
        ds.Tables("Staff").Rows(inc).Item("StaffCountry") = txtCountry.Text
        ds.Tables("Staff").Rows(inc).Item("StaffICColor") = cbICColor.Text
        ds.Tables("Staff").Rows(inc).Item("StaffICNo") = txtICNo.Text
        ds.Tables("Staff").Rows(inc).Item("StaffAnnualLeave") = txtAnnualLeave.Text

        da.Update(ds, "Staff")

        MsgBox("Data has been updated")
    End Sub
    Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        btnConfirm.Enabled = False
        btnNew.Enabled = True
        btnupdate.Enabled = True
        btndelete.Enabled = True

        inc = 0
        Recordsatdatabase()
    End Sub
#Region "Function for checking blank values in textbox"
    Sub Check_Textbox()
        Dim r As DialogResult
        If txtStaffID.Text = "" _
        Or txtStaffName.Text = "" _
        Or cbPosition.Text = "Select" _
        Or txtAddress1.Text = "" _
        Or txtContactNo.Text = "" _
        Or txtCountry.Text = "" _
        Or cbICColor.Text = "Select" _
        Or txtAnnualLeave.Text = "" _
Then

            r = MessageBox.Show("The system has detected one or more forms are blank. Please try again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Else

            Call ProductSave()
        End If

    End Sub
#End Region
    Private Sub ProductSave()
        Dim cb As New SqlClient.SqlCommandBuilder(da)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("Staff").NewRow

        dsNewRow.Item("StaffID") = txtStaffID.Text
        dsNewRow.Item("StaffName") = txtStaffName.Text
        dsNewRow.Item("StaffGenderMale") = rbMale.Checked
        dsNewRow.Item("StaffGenderFemale") = rbFemale.Checked
        dsNewRow.Item("StaffPosition") = cbPosition.Text
        dsNewRow.Item("StaffAddress1") = txtAddress1.Text
        dsNewRow.Item("StaffAddress2") = txtAddress2.Text
        dsNewRow.Item("StaffDOB") = txtDOB.Text
        dsNewRow.Item("StaffContactNo") = txtContactNo.Text
        dsNewRow.Item("StaffRegistrationDate") = txtRegistrationDate.Text
        dsNewRow.Item("StaffCountry") = txtCountry.Text
        dsNewRow.Item("StaffICColor") = cbICColor.Text
        dsNewRow.Item("StaffICNo") = txtICNo.Text
        dsNewRow.Item("StaffPhoto") = txtLabel.Text
        dsNewRow.Item("StaffAnnualLeave") = txtAnnualLeave.Text
        PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)

        ds.Tables("Staff").Rows.Add(dsNewRow)
        da.Update(ds, "Staff")

        MsgBox("The new existing record has been saved in the database.")

        btnConfirm.Enabled = False
        btnNew.Enabled = True
        btnUpdate.Enabled = True
        btnDelete.Enabled = True
    End Sub


    Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click

        Check_Textbox()

        'If inc <> -1 Then

        'End If
        Call ModulefrmStaffRefresh.ResetStaff()
    End Sub
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Would you like to remove this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        Else
            Dim cb As New SqlClient.SqlCommandBuilder(da)
            ds.Tables("Staff").Rows(inc).Delete()

            MAXROWS = MAXROWS - 1
            inc = 0
            Recordsatdatabase()
            da.Update(ds, "Staff")
        End If
    End Sub
    Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
        Me.Close()
    End Sub
    Sub UpdateTextBoxes()

        txtStaffID.Text = CStr(dt.Rows(rowIndex)("StaffID"))
        txtStaffName.Text = CStr(dt.Rows(rowIndex)("StaffName"))
        cbPosition.Text = CStr(dt.Rows(rowIndex)("StaffPosition"))
        rbMale.Checked = CStr(dt.Rows(rowIndex)("StaffGenderMale"))
        rbFemale.Checked = CStr(dt.Rows(rowIndex)("StaffGenderFemale"))
        txtAddress1.Text = CStr(dt.Rows(rowIndex)("StaffAddress1"))
        txtAddress2.Text = CStr(dt.Rows(rowIndex)("StaffAddress2"))
        txtDOB.Text = CStr(dt.Rows(rowIndex)("StaffDOB"))
        txtContactNo.Text = CStr(dt.Rows(rowIndex)("StaffContactNo"))
        txtRegistrationDate.Text = CStr(dt.Rows(rowIndex)("StaffRegistrationDate"))
        txtLabel.Text = CStr(dt.Rows(rowIndex)("StaffPhoto"))
        txtCountry.Text = CStr(dt.Rows(rowIndex)("StaffCountry"))
        cbICColor.Text = CStr(dt.Rows(rowIndex)("StaffICColor"))
        txtICNo.Text = CStr(dt.Rows(rowIndex)("StaffICNo"))
        PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
        txtAnnualLeave.Text = CStr(dt.Rows(rowIndex)("StaffAnnualLeave"))

    End Sub

    Private Sub btnImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImage.Click
        Try
            Dim fopen As New OpenFileDialog
            fopen.FileName = ""
            fopen.Filter = "Image Files (*.jpg)|*.jpg|(*.jpeg)|*.JPEG|(*.gif)|*.gif|(*.png)|*.png|All Files (*.*)|*.*"
            fopen.ShowDialog()

            PictureBox1.Image = System.Drawing.Bitmap.FromFile(fopen.FileName)
            txtLabel.Text = fopen.FileName

        Catch ex As Exception

        End Try
    End Sub

    Private Sub rbMale_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbMale.CheckedChanged
        If rbMale.Checked = True Then
            rbFemale.Checked = False

        Else
            rbFemale.Checked = False
            rbFemale.Checked = False
        End If
    End Sub

    Private Sub rbFemale_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFemale.CheckedChanged
        If rbFemale.Checked = True Then
            rbMale.Checked = False
        Else

        End If
    End Sub


    Sub Grid(ByVal sqlStr As String)

        Dim dt As New DataTable()
        Dim connstr As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf;Trusted_Connection=Yes;Integrated Security=False;"
        Dim dataAdapter As New SqlClient.SqlDataAdapter(sqlStr, connstr)

        dataAdapter.Fill(dt)
        dataAdapter.Dispose()

        If dt.Rows.Count <> 0 Then
            If txtStaffID.Text = CStr(dt.Rows(0)("StaffID")) Then

                txtStaffID.Text = CStr(dt.Rows(0)("StaffID"))
                txtStaffName.Text = CStr(dt.Rows(0)("StaffName"))
                cbPosition.Text = CStr(dt.Rows(0)("StaffPosition"))
                rbMale.Checked = CStr(dt.Rows(0)("StaffGenderMale"))
                rbFemale.Checked = CStr(dt.Rows(0)("StaffGenderFemale"))
                txtAddress1.Text = CStr(dt.Rows(0)("StaffAddress1"))
                txtAddress2.Text = CStr(dt.Rows(0)("StaffAddress2"))
                txtDOB.Text = CStr(dt.Rows(0)("StaffDOB"))
                txtContactNo.Text = CStr(dt.Rows(0)("StaffContactNo"))
                txtRegistrationDate.Text = CStr(dt.Rows(0)("StaffRegistrationDate"))
                txtLabel.Text = CStr(dt.Rows(0)("StaffPhoto"))
                txtCountry.Text = CStr(dt.Rows(0)("StaffCountry"))
                cbICColor.Text = CStr(dt.Rows(0)("StaffICColor"))
                txtICNo.Text = CStr(dt.Rows(0)("StaffICNo"))

                txtLabel.Text = CStr(dt.Rows(0)("StaffPhoto"))
                PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
                txtAnnualLeave.Text = CStr(dt.Rows(0)("StaffAnnualLeave"))

            Else

                txtStaffName.Clear()
                txtAddress1.Clear()
                txtAddress2.Clear()
                txtContactNo.Clear()
                txtCountry.Clear()
                txtICNo.Clear()
                txtAnnualLeave.Text = "0"

            End If
        End If
    End Sub


    Private Sub txtStaffID_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtStaffID.TextChanged
        Grid("SELECT * FROM Staff WHERE StaffID LIKE '" & txtStaffID.Text & "%'")
    End Sub
End Class