Imports System.Data
Imports System.Data.OleDb
Public Class Law
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub Law_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call Cleartext()
        Call SetGrid()
        Call GetDocNo()
    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'LAW'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            TextBox1.Text = dr.Item("RunNo") + 1
        Else

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & TextBox1.Text & "'"
        gSQL += " Where DocNo = 'LAW'"
        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
        lawSR.Show()
    End Sub
    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        gSQL = "Insert Into Law Values ("
        gSQL += "'" & TextBox1.Text & "',"
        gSQL += "'" & DateTimePicker1.Text & "',"
        gSQL += "'" & eno.Text & "',"
        gSQL += "'" & ename.Text & "',"
        gSQL += "'" & lwhy.Text & "')"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Call UpdateDocNo()
        Call Cleartext()
        Call GetDocNo()
    End Sub
    Private Sub Cleartext()
        txtSearch.Text = ""
        eno.Text = ""
        ename.Text = ""
        lwhy.Text = ""
        GetDocNo()
    End Sub
    Private Sub SetGrid()
        If DataGridView1.RowCount > 0 Then
            DataGridView1.Columns(0).HeaderText = "ลำดับที่"
            DataGridView1.Columns(1).HeaderText = "วันที่"
            DataGridView1.Columns(2).HeaderText = "รหัสพนักงาน"
            DataGridView1.Columns(3).HeaderText = "ชื่อพนักงาน"
            DataGridView1.Columns(4).HeaderText = "เหตุผล"
        End If
    End Sub
    Private Sub PictureBox4_Click(sender As Object, e As EventArgs) Handles PictureBox4.Click
        Dim dt As New DataTable
        Dim cmd As New OleDbCommand

        gSQL = "Select Law_ID,Law_date,Employee_ID,Employee_name,Law_why From Law Where Employee_name ='" & txtSearch.Text & "'"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader

            If dr.HasRows Then
                dt.Load(dr)
                DataGridView1.DataSource = dt
                SetGrid()
            End If
            dr.Close()
        End With
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        frmPreview.Show()
    End Sub

    Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
        wel.Show()
        Hide()
    End Sub
End Class