Imports System.Data
Imports System.Data.OleDb
Public Class FmMember
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gsql As String
    Private Sub FmMember_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        Label16.Text = Today

        showdata()
        ShowList()
        setGrid()
        Cbotypr.Items.Add("สมาชิก")

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Main2.Show()
        Me.Hide()
    End Sub
    Private Sub showdata()
        Dim dt As New DataTable
        gsql = "Select * from MEMBER"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
            If dr.HasRows Then
                dt.Load(dr)
                dgvmember.DataSource = dt
            End If
            dr.Close()

        End With

    End Sub
    Private Sub ShowList()
        Dim dt As New DataTable
        gsql = "Select * from MEMBER"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
            If dr.HasRows Then
                dt.Load(dr)
                dgvmember.DataSource = dt
            End If
            dr.Close()

        End With
        dt = Nothing


    End Sub
    Private Sub setGrid()
        If dgvmember.RowCount > 0 Then
            dgvmember.Columns(0).HeaderText = "รหัสสมาชิก"
            dgvmember.Columns(1).HeaderText = "วันที่สมัครสมาชิก "
            dgvmember.Columns(2).HeaderText = "ชื่อสกุล"
            dgvmember.Columns(3).HeaderText = "เลขบัตรประชาชน"
            dgvmember.Columns(4).HeaderText = "ที่อยู่"
            dgvmember.Columns(5).HeaderText = "เบอร์โทรศัทพ์"
        End If
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
         Try

            gsql = "Insert Into MEMBER Values ("
            gsql += "'" & txtid.Text & "',"
            gsql += "'" & txtShipdate.Text & "',"
            gsql += "'" & txtName.Text & "',"
            gsql += "'" & txtidcard.Text & "',"
            gsql += "'" & txttel.Text & "',"
            gsql += "'" & txtAddress.Text & "',"
            gsql += "'" & Cbotypr.Text & "')"


            'cmd ให้ cmd รันคำสั่ง
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gsql
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("เพิ่มรายการใหม่แล้ว", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception 'เมื่อERROE ให้เด้งมาข้างล่างนี้
            MessageBox.Show("มีรหัสสินค้านี้แล้วไม่สามารถเพิ่มข้อมูลใหม่ได้", "เพิ่มข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

        showdata()
        ClaerText()

    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim ans As MsgBoxResult
        ans = MessageBox.Show("ต้องการลบรายการหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If ans = MsgBoxResult.Yes Then
            gsql = "Delete From MEMBER Where M_ID = '" & txtid.Text & "'"
            With cmd
                .CommandType = CommandType.Text
                .CommandText = gsql
                .Connection = cnn
                .ExecuteNonQuery()
            End With
            MessageBox.Show("ลบรายการแล้ว", "ลบข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
        showdata()
        ClaerText()
    End Sub

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        gsql = "Update MEMBER Set M_Shipdate = '" & txtShipdate.Text & "',"
        gsql += "M_Name = '" & txtName.Text & "',"
        gsql += "M_idcard = '" & txtidcard.Text & "',"
        gsql += "M_Address = '" & txtAddress.Text & "',"
        gsql += "M_Tel = '" & txttel.Text & "'"
        gsql += " Where M_ID = '" & txtid.Text & "'"
        With cmd

            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            .ExecuteNonQuery()
        End With
        MessageBox.Show("แก้ไขรายการใหม่", "แก้ไขข้อมูล", MessageBoxButtons.OK, MessageBoxIcon.Information)
        txtid.Text = ""
        txtShipdate.Text = ""
        txtName.Text = ""
        txtidcard.Text = ""
        txtAddress.Text = ""
        txttel.Text = ""

        Me.ShowList()
    End Sub
    Private Sub ClaerText()
        txtid.Text = ""
        txtShipdate.Text = ""
        txtName.Text = ""
        txtidcard.Text = ""
        txtAddress.Text = ""
        txttel.Text = ""

    End Sub
    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub


    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        gsql = "Select * From MEMBER where M_ID ='" & txtSearch.Text & "'"

        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With
        dr.Read()
        If dr.HasRows Then
            txtid.Text = dr.Item("M_ID").ToString
            txtShipdate.Text = dr.Item("M_Shipdate").ToString
            txtName.Text = dr.Item("M_Name").ToString
            txtidcard.Text = dr.Item("M_idcard").ToString
            txtAddress.Text = dr.Item("M_Address").ToString


        Else
            MessageBox.Show("ไม่พบข้อมูล")

        End If
        dr.Close()


        btnSearch.Text = ""
    End Sub

    Private Sub Label9_Click(sender As Object, e As EventArgs) Handles Label9.Click

    End Sub
End Class