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

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


    End Sub

    Private Sub fillcboTF()
        gSQL = "Select * From Type_foods"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboTF
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("TypeF_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeF_ID").ColumnName
        End With
    End Sub
    Private Sub fillcboTR()
        gSQL = "Select * From Type_res"
        Dim da As New OleDbDataAdapter(gSQL, cnn)
        Dim dt As New DataTable
        da.Fill(dt)
        With cboTR
            .DataSource = dt
            .DisplayMember = dt.Columns.Item("Type_name").ColumnName
            .ValueMember = dt.Columns.Item("TypeR_ID").ColumnName
        End With
    End Sub
    Private Sub GetDocNo()
        gSQL = "Select * From RunDoc Where DocNo = 'FOO'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader()
        End With
        dr.Read()
        If dr.HasRows Then
            foodNo.Text = dr.Item("RunNo") + 1
        Else

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

    Private Sub showdata()
        gSQL = "select * from Foods_Train"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gSQL
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            foodNo.Text = dr.Item("Food_ID").ToString
            foodname.Text = dr.Item("Food_name").ToString
            cboTR.SelectedValue = dr.Item("TypeR_ID").ToString
            cboTF.SelectedValue = dr.Item("TypeF_ID").ToString
            foodprice.Text = dr.Item("Food_price").ToString
            foodCT.Text = dr.Item("Food_country").ToString
            foodamper.Text = dr.Item("Food_amper").ToString
            foodad.Text = dr.Item("Food_address").ToString
            foodtel.Text = dr.Item("Food_tel").ToString
            foodemail.Text = dr.Item("Food_email").ToString
            foodcont.Text = dr.Item("Food_contact").ToString
            foodcontt.Text = dr.Item("Food_CTstatus").ToString

        End If
        dr.Close()
    End Sub


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

            foodNo.Text = dr.Item("Food_ID").ToString
            foodname.Text = dr.Item("Food_name").ToString
            cboTR.SelectedValue = dr.Item("TypeR_ID").ToString
            cboTF.SelectedValue = dr.Item("TypeF_ID").ToString
            foodprice.Text = dr.Item("Food_price").ToString
            foodCT.Text = dr.Item("Food_country").ToString
            foodamper.Text = dr.Item("Food_amper").ToString
            foodad.Text = dr.Item("Food_address").ToString
            foodtel.Text = dr.Item("Food_tel").ToString
            foodemail.Text = dr.Item("Food_email").ToString
            foodcont.Text = dr.Item("Food_contact").ToString
            foodcontt.Text = dr.Item("Food_CTstatus").ToString
        Else
            MessageBox.Show("ไม่พบข้อมูลสินค้า")
            Call Cleartext()
        End If
        dr.Close()
    End Sub


    Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
        gSQL = "Insert Into Foods_Train Values ("
        gSQL += "'" & foodNo.Text & "',"
        gSQL += "'" & foodname.Text & "',"
        gSQL += "'" & cboTR.SelectedValue & "',"
        gSQL += "'" & cboTF.SelectedValue & "',"
        gSQL += "'" & foodprice.Text & "',"
        gSQL += "'" & foodCT.Text & "',"
        gSQL += "'" & foodamper.Text & "',"
        gSQL += "'" & foodad.Text & "',"
        gSQL += "'" & foodtel.Text & "',"
        gSQL += "'" & foodemail.Text & "',"
        gSQL += "'" & foodcont.Text & "',"
        gSQL += "'" & foodcontt.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 = ""
        foodNo.Text = ""
        foodname.Text = ""
        cboTR.SelectedValue = ""
        cboTF.SelectedValue = ""
        foodprice.Text = ""
        foodCT.Text = ""
        foodamper.Text = ""
        foodad.Text = ""
        foodtel.Text = ""
        foodemail.Text = ""
        foodcont.Text = ""
        foodcontt.Text = ""

    End Sub
    Private Sub NewDoc()
        foodNo.Text = ""
        foodname.Text = ""
        cboTR.SelectedValue = ""
        cboTF.SelectedValue = ""
        foodprice.Text = ""
        foodCT.Text = ""
        foodamper.Text = ""
        foodad.Text = ""
        foodtel.Text = ""
        foodemail.Text = ""
        foodcont.Text = ""
        foodcontt.Text = ""
    End Sub
    Private Sub PictureBox6_Click(sender As Object, e As EventArgs) Handles PictureBox6.Click
        DTbase.Show()
        Hide()

    End Sub

    Private Sub PictureBox5_Click(sender As Object, e As EventArgs) Handles PictureBox5.Click
        gSQL = "Update Foods_Train Set Food_name ='" & foodname.Text & "',"
        gSQL += "TypeR_ID='" & cboTR.SelectedValue & "',"
        gSQL += "TypeF_ID='" & cboTF.SelectedValue & "',"
        gSQL += "Food_price='" & foodprice.Text & "',"
        gSQL += "Food_country='" & foodCT.Text & "',"
        gSQL += "Food_amper='" & foodamper.Text & "',"
        gSQL += "Food_address='" & foodad.Text & "',"
        gSQL += "Food_tel='" & foodtel.Text & "',"
        gSQL += "Food_email='" & foodemail.Text & "',"
        gSQL += "Food_contact='" & foodcont.Text & "',"
        gSQL += "Food_CTstatus='" & foodcontt.Text & "'"
        gSQL += " Where Food_ID ='" & foodNo.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 PictureBox7_Click(sender As Object, e As EventArgs) Handles PictureBox7.Click
        Dim ans As MsgBoxResult
        ans = MessageBox.Show("ต้องการลบรายการหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If ans = MsgBoxResult.Yes Then
            gSQL = "Delete From Foods_Train where Food_ID  = '" & txtSearch.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gSQL
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("ลบรายการแล้ว", "ลบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
            showdata()
            Call Cleartext()
        End If

    End Sub

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