Imports System.Data
Imports System.Data.OleDb

Public Class Place
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gSQL As String
    Private Sub PictureBox9_Click(sender As Object, e As EventArgs) Handles PictureBox9.Click
        DTbase.Show()
        Hide()
    End Sub

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

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

        End If
        dr.Close()
    End Sub
    Private Sub UpdateDocNo()
        gSQL = "Update RunDoc Set RunNo = '" & pno.Text & "'"
        gSQL += " Where DocNo = 'PLA'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
    End Sub
    Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
        gSQL = "select * from Place_Train where Place_ID  = '" & txtSearch.Text & "'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            pno.Text = dr.Item("Place_ID").ToString
            pname.Text = dr.Item("Place_name").ToString
            pprice.Text = dr.Item("Place_price").ToString
            qtyp.Text = dr.Item("Place__qtyP").ToString
            qty.Text = dr.Item("Place__qtyN").ToString
            pCT.Text = dr.Item("Place_country").ToString
            pemail.Text = dr.Item("Place_email").ToString
            ptel.Text = dr.Item("Place_tel").ToString
            padd.Text = dr.Item("Place_address").ToString
            pcont.Text = dr.Item("Place_contact").ToString
            pcontt.Text = dr.Item("Place_CTstatus").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูล")
            Call Cleartext()
        End If
        dr.Close()
    End Sub

    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        gSQL = "Insert Into Place_Train Values ("
        gSQL += "'" & pno.Text & "',"
        gSQL += "'" & pname.Text & "',"
        gSQL += "'" & pprice.Text & "',"
        gSQL += "'" & qtyp.Text & "',"
        gSQL += "'" & qty.Text & "',"
        gSQL += "'" & pCT.Text & "',"
        gSQL += "'" & pemail.Text & "',"
        gSQL += "'" & ptel.Text & "',"
        gSQL += "'" & padd.Text & "',"
        gSQL += "'" & pcont.Text & "',"
        gSQL += "'" & pcontt.Text & "')"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Call UpdateDocNo()
        Call Cleartext()
    End Sub
    Private Sub Cleartext()
        txtSearch.Text = ""
        pno.Text = ""
        pname.Text = ""
        pprice.Text = ""
        qtyp.Text = ""
        qty.Text = ""
        pCT.Text = ""
        pemail.Text = ""
        ptel.Text = ""
        padd.Text = ""
        pcont.Text = ""
        pcontt.Text = ""

    End Sub

    Private Sub PictureBox7_Click(sender As Object, e As EventArgs) Handles PictureBox7.Click
        gSQL = "Update Place_Train Set Place_name ='" & pname.Text & "',"
        gSQL += "Place_price='" & pprice.Text & "',"
        gSQL += "Place__qtyP='" & qtyp.Text & "',"
        gSQL += "Place__qtyN='" & qty.Text & "',"
        gSQL += "Place_country='" & pCT.Text & "',"
        gSQL += "Place_email='" & pemail.Text & "',"
        gSQL += "Place_tel='" & ptel.Text & "',"
        gSQL += "Place_address='" & padd.Text & "',"
        gSQL += "Place_contact='" & pcont.Text & "',"
        gSQL += "Place_CTstatus='" & pcontt.Text & "'"
        gSQL += " Where Place_ID ='" & pno.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 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 Place_Train where Place_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
End Class