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

    Private Sub Institution_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call connectdatabase()
        Call GetDocNo()

    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'INS'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            isnNo.Text = dr.Item("RunNo") + 1
        Else

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & isnNo.Text & "'"
        gSQL += " Where DocNo = 'INS'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
    End Sub
    Private Sub showdata()
        gSQL = "select * from Institution_Train"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            isnNo.Text = dr.Item("Ins_ID").ToString
            isnname.Text = dr.Item("Ins_name").ToString
            isnCT.Text = dr.Item("Ins_country").ToString
            isntel.Text = dr.Item("Ins_tel").ToString
            isnemail.Text = dr.Item("Ins_email").ToString
            isnad.Text = dr.Item("Ins_address").ToString
            isncont.Text = dr.Item("Ins_contact").ToString
            isncontt.Text = dr.Item("Ins_CTstatus").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูลสินค้า")
            Call Cleartext()
            txtSearch.Text = ""
        End If
        dr.Close()
    End Sub
    Private Sub Cleartext()
        txtSearch.Text = ""
        isnNo.Text = ""
        isnname.Text = ""
        isnCT.Text = ""
        isntel.Text = ""
        isnemail.Text = ""
        isnad.Text = ""
        isncont.Text = ""
        isncontt.Text = ""

    End Sub

    Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
        gSQL = "select * from Institution_Train where Ins_ID  = '" & txtSearch.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then

            isnNo.Text = dr.Item("Ins_ID").ToString
            isnname.Text = dr.Item("Ins_name").ToString
            isnCT.Text = dr.Item("Ins_country").ToString
            isntel.Text = dr.Item("Ins_tel").ToString
            isnemail.Text = dr.Item("Ins_email").ToString
            isnad.Text = dr.Item("Ins_address").ToString
            isncont.Text = dr.Item("Ins_contact").ToString
            isncontt.Text = dr.Item("Ins_CTstatus").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูลสินค้า")
            Call Cleartext()
            txtSearch.Text = ""
        End If
        dr.Close()
    End Sub

    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        Try
            gSQL = "Insert into Institution_Train Values ("
            gSQL += "'" & isnNo.Text & "',"
            gSQL += "'" & isnname.Text & "',"
            gSQL += "'" & isnCT.Text & "',"
            gSQL += "'" & isntel.Text & "',"
            gSQL += "'" & isnemail.Text & "',"
            gSQL += "'" & isnad.Text & "',"
            gSQL += "'" & isncont.Text & "',"
            gSQL += "'" & isncontt.Text & "')"
            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("มีรหัสนี้แล้วไม่สามารถเพิ่มได้")
        End Try
    End Sub

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

            Call Cleartext()
            txtSearch.Text = " "
        End If
    End Sub

    Private Sub PictureBox7_Click(sender As Object, e As EventArgs) Handles PictureBox7.Click
        gSQL = "Update Institution_Train Set Ins_name ='" & isnname.Text & "',"
        gSQL += "Ins_country='" & isnCT.Text & "',"
        gSQL += "Ins_tel='" & isntel.Text & "',"
        gSQL += "Ins_email='" & isnemail.Text & "',"
        gSQL += "Ins_address='" & isnad.Text & "',"
        gSQL += "Ins_contact='" & isncont.Text & "',"
        gSQL += "Ins_CTstatus='" & isncontt.Text & "'"
        gSQL += " Where Ins_ID ='" & isnNo.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 PictureBox9_Click(sender As Object, e As EventArgs) Handles PictureBox9.Click
        DTbase.Show()
        Hide()
    End Sub


End Class