Imports System.Data
Imports System.Data.OleDb
Public Class Employee
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String

    Private Sub Employee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call fillcbotypeem()
        Call fillcboedu()
        Call fillcbotypesalary()
        Call fillcbotypeSd()
        Call Cleartext()

        Call GetDocNo()

    End Sub
    Private Sub fillcbotypeem()
        gSQL = "Select * From Type_Employee"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cbotypeem
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeEm_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeEm_ID").ColumnName
        End With
    End Sub
    Private Sub fillcboedu()
        gSQL = "Select * From Type_education"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboedu
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeEd_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeEd_ID").ColumnName
        End With
    End Sub
    Private Sub fillcbotypesalary()
        gSQL = "Select * From Type_Salary"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cbotypesalary
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeSalary_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeSalary_ID").ColumnName
        End With
    End Sub
    Private Sub fillcbotypeSd()
        gSQL = "Select * From Type_soider"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cbotypeSd
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeSd_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeSd_ID").ColumnName
        End With
    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'EMP'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            emNo.Text = dr.Item("RunNo") + 1
        Else

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & emNo.Text & "'"
        gSQL += " Where DocNo = 'EMP'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
    End Sub


    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim ans As MsgBoxResult
        ans = MessageBox.Show("ต้องการลบรายการหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If ans = MsgBoxResult.Yes Then
            gSQL = "Delete From Employeee where Employee_ID  = '" & txtSearch.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("ลบรายการแล้ว", "ลบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)

            Call Cleartext()
        End If

    End Sub

    Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
        gSQL = "select * from Employeee where Employee_ID  = '" & txtSearch.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            emNo.Text = dr.Item("Employee_ID").ToString
            emname.Text = dr.Item("Employee_name").ToString
            emid.Text = dr.Item("Employee_cardNo").ToString
            TextBox1.Text = dr.Item("Employee_BD").ToString
            emage.Text = dr.Item("Employee_age").ToString
            emsadsana.Text = dr.Item("Employee_race").ToString
            emnati.Text = dr.Item("Employee_nationality").ToString
            emtel.Text = dr.Item("Employee_tel").ToString
            emadd.Text = dr.Item("Employee_address").ToString
            emfacebook.Text = dr.Item("Employee_facebook").ToString
            emline.Text = dr.Item("Employee_line").ToString
            ememail.Text = dr.Item("Employee_email").ToString
            emweight.Text = dr.Item("Employee_weight").ToString
            emheight.Text = dr.Item("Employee_height").ToString
            empic.Text = dr.Item("Employee_pic").ToString
            cbotypeem.SelectedValue = dr.Item("TypeEm_ID").ToString
            cbotypesalary.SelectedValue = dr.Item("TypeSalary_ID").ToString
            emsalary.Text = dr.Item("Employee_salary").ToString
            TextBox2.Text = dr.Item("Employee_Datework").ToString
            emsoi.Text = dr.Item("Employee_Soi").ToString
            emstatus.Text = dr.Item("Employee_statas").ToString
            emfamily.Text = dr.Item("Employee_family").ToString
            emnameF.Text = dr.Item("Employee_FatherN").ToString
            emnameM.Text = dr.Item("Employee_MotherN").ToString
            emgrade.Text = dr.Item("Employee_EdGrade").ToString
            cboedu.SelectedValue = dr.Item("TypeEd_ID").ToString
            emgenius.Text = dr.Item("Employee_genius").ToString
            emnameWork.Text = dr.Item("Employee_workN").ToString
            emdisease.Text = dr.Item("Employee_disease").ToString
            cbotypeSd.SelectedValue = dr.Item("TypeSd_ID").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูลสินค้า")
            Call Cleartext()
        End If
        dr.Close()
    End Sub
    Private Sub Cleartext()
        txtSearch.Text = ""
        emNo.Text = ""
        emname.Text = ""
        TextBox1.Text = ""
        TextBox2.Text = ""
        emid.Text = ""
        emage.Text = ""
        emsadsana.Text = ""
        emnati.Text = ""
        emtel.Text = ""
        emadd.Text = ""
        emfacebook.Text = ""
        emline.Text = ""
        ememail.Text = ""
        emweight.Text = ""
        emheight.Text = ""
        empic.Text = ""
        cbotypeem.SelectedValue = ""
        cbotypesalary.SelectedValue = ""
        emsalary.Text = ""
        emsoi.Text = ""
        emstatus.Text = ""
        emfamily.Text = ""
        emnameF.Text = ""
        emnameM.Text = ""
        emgrade.Text = ""
        cboedu.SelectedValue = ""
        emgenius.Text = ""
        emnameWork.Text = ""
        emdisease.Text = ""
        cbotypeSd.SelectedValue = ""
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        gSQL = "Update Employeee Set Employee_name ='" & emname.Text & "',"
        gSQL += "Employee_cardNo='" & emid.Text & "',"
        gSQL += "Employee_BD='" & TextBox1.Text & "',"
        gSQL += "Employee_age='" & emage.Text & "',"
        gSQL += "Employee_race='" & emsadsana.Text & "',"
        gSQL += "Employee_nationality='" & emnati.Text & "',"
        gSQL += "Employee_tel='" & emtel.Text & "',"
        gSQL += "Employee_address='" & emadd.Text & "',"
        gSQL += "Employee_facebook='" & emfacebook.Text & "',"
        gSQL += "Employee_line='" & emline.Text & "',"
        gSQL += "Employee_email='" & ememail.Text & "',"
        gSQL += "Employee_weight='" & emweight.Text & "',"
        gSQL += "Employee_height='" & emheight.Text & "',"
        gSQL += "Employee_pic='" & empic.Text & "',"
        gSQL += "TypeEm_ID='" & cbotypeem.SelectedValue & "',"
        gSQL += "TypeSalary_ID='" & cbotypesalary.SelectedValue & "',"
        gSQL += "Employee_salary='" & emsalary.Text & "',"
        gSQL += "Employee_Datework='" & TextBox2.Text & "',"
        gSQL += "Employee_Soi='" & emsoi.Text & "',"
        gSQL += "Employee_statas='" & emstatus.Text & "',"
        gSQL += "Employee_family='" & emfamily.Text & "',"
        gSQL += "Employee_FatherN='" & emnameF.Text & "',"
        gSQL += "Employee_MotherN='" & emnameM.Text & "',"
        gSQL += "Employee_EdGrade ='" & emgrade.Text & "',"
        gSQL += "TypeEd_ID ='" & cboedu.SelectedValue & "',"
        gSQL += "Employee_genius ='" & emgenius.Text & "',"
        gSQL += "Employee_workN ='" & emnameWork.Text & "',"
        gSQL += "Employee_disease ='" & emdisease.Text & "',"
        gSQL += "TypeSd_ID ='" & cbotypeSd.SelectedValue & "'"
        gSQL += " Where Employee_ID ='" & emNo.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("แก้ไขรายการใหม่", "แก้ไขข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Call Cleartext()
    End Sub
    Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
        DTbase.Show()
        Hide()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            gSQL = "Insert into Employeee Values ("
            gSQL += "'" & emNo.Text & "',"
            gSQL += "'" & emname.Text & "',"
            gSQL += "'" & emid.Text & "',"
            gSQL += "'" & TextBox1.Text & "',"
            gSQL += "'" & emage.Text & "',"
            gSQL += "'" & emsadsana.Text & "',"
            gSQL += "'" & emnati.Text & "',"
            gSQL += "'" & emtel.Text & "',"
            gSQL += "'" & emadd.Text & "',"
            gSQL += "'" & emfacebook.Text & "',"
            gSQL += "'" & emline.Text & "',"
            gSQL += "'" & ememail.Text & "',"
            gSQL += "'" & emweight.Text & "',"
            gSQL += "'" & emheight.Text & "',"
            gSQL += "'" & empic.Text & "',"
            gSQL += "'" & cbotypeem.SelectedValue & "',"
            gSQL += "'" & cbotypesalary.SelectedValue & "',"
            gSQL += "'" & emsalary.Text & "',"
            gSQL += "'" & TextBox2.Text & "',"
            gSQL += "'" & emsoi.Text & "',"
            gSQL += "'" & emstatus.Text & "',"
            gSQL += "'" & emfamily.Text & "',"
            gSQL += "'" & emnameF.Text & "',"
            gSQL += "'" & emnameM.Text & "',"
            gSQL += "'" & emgrade.Text & "',"
            gSQL += "'" & cboedu.SelectedValue & "',"
            gSQL += "'" & emgenius.Text & "',"
            gSQL += "'" & emnameWork.Text & "',"
            gSQL += "'" & emdisease.Text & "',"
            gSQL += "'" & cbotypeSd.SelectedValue & "')"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Call UpdateDocNo()
            Call Cleartext()
        Catch ex As Exception
            MessageBox.Show("มีรหัสนี้แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
End Class