Imports System.Data
Imports System.Data.OleDb
Public Class Trian
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub Trian_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call GetDocNo()
        Call Cleartext()
        ListView1.Columns.Add("รหัสพนักงาน", 100, HorizontalAlignment.Center)
        ListView1.Columns.Add("ชื่อพนักงาน", 200, HorizontalAlignment.Center)
        ListView1.Columns.Add("แผนก", 100, HorizontalAlignment.Center)
    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'TAN'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            tno.Text = dr.Item("RunNo") + 1
        Else
        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & tno.Text & "'"
        gSQL += " Where DocNo = 'TAN'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Form2.ShowDialog()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If eno.Text = "" Or ename.Text = "" Then
            Exit Sub
        End If
        ListView1.Items.Add(eno.Text)
        ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(ename.Text)
        ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(epanak.Text)

        eno.Text = ""
        ename.Text = ""
        epanak.Text = ""
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        addem.ShowDialog()
    End Sub

    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        For i = 1 To ListView1.Items.Count - 1
            gSQL = "Insert Into Train Values ("
            gSQL += "'" & tno.Text & "',"
            gSQL += "'" & tname.Text & "',"
            gSQL += "'" & DateTimePicker1.Text & "',"
            gSQL += "'" & DateTimePicker2.Text & "',"
            gSQL += "'" & ListView1.Items(i).Text & "',"
            gSQL += "'" & ListView1.Items(i).SubItems(1).Text & "',"
            gSQL += "'" & ListView1.Items(i).SubItems(2).Text & "',"
            gSQL += "'" & hFiles.Text & "')"

            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                .ExecuteNonQuery()
            End With
        Next
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มขอ้มูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        UpdateDocNo()
        Cleartext()
        GetDocNo()
    End Sub
    Private Sub Cleartext()
        tname.Text = ""
        GetDocNo()
        eno.Text = ""
        ename.Text = ""
        epanak.Text = ""
        ListView1.Items.Clear()
        hFiles.Text = ""
    End Sub

    Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
        '/ ประกาศใช้งาน Open File Dialog ในแบบ Run Time
        Dim dlgOpenFile As OpenFileDialog = New OpenFileDialog()

        ' / ตั้งค่าการใช้งาน Open File Dialog
        With dlgOpenFile
            .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
            .Title = "เลือกไฟล์ MS Excel"
            .Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
            .FilterIndex = 1
            .RestoreDirectory = True
        End With
        '/ หากเลือกปุ่ม OK หลังจากการ Browse ...
        If dlgOpenFile.ShowDialog() = DialogResult.OK Then

            '  If dlgOpenFile.ShowDialog() = DialogResult.OK Then
            hFiles.Text = dlgOpenFile.FileName
            Dim strConn As String =
                " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
                dlgOpenFile.FileName & ";" &
                " Extended Properties=""Excel 12.0 Xml; HDR=YES"";"
            Dim Conn As New OleDbConnection(strConn)
            Conn.Open()
        End If
    End Sub

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