Imports System.Data
Imports System.Data.OleDb
Public Class ShowProducts
    Dim dr As OleDbDataReader
    Dim cmd As New OleDbCommand
    Dim gsql As String
    Private Sub ShowProducts_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ConnectDatabase()
        showlist()
        SetGrid()
        SetHeaderGrid()
    End Sub
    Private Sub showlist() 'dataGridView
        Dim dt As New DataTable
        gsql = "select * from PRODUCTS"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
        End With
        If dr.HasRows Then
            dt.Load(dr)
            dgvproducts.DataSource = dt 'dtโหลด dr ให้ dgv=dt
        End If
        dr.Close()
    End Sub
    Private Sub SetHeaderGrid()
        'datagrid
        If dgvProducts.RowCount > 0 Then
            dgvProducts.Columns(0).HeaderText = "รหัสสินค้า"
            dgvProducts.Columns(0).Width = 100
            dgvProducts.Columns(1).HeaderText = "รายการสินค้า"
            dgvProducts.Columns(1).Width = 300
            dgvproducts.Columns(2).HeaderText = "ประเภทสินค้า"
            dgvproducts.Columns(2).Width = 300
            dgvproducts.Columns(3).HeaderText = "หน่วยนับ"
            dgvproducts.Columns(3).Width = 150
            dgvproducts.Columns(4).HeaderText = "ราคาต้นทุน"
            dgvproducts.Columns(4).Width = 150
            dgvproducts.Columns(5).HeaderText = "ราคาขาย"
            dgvproducts.Columns(5).Width = 100
            dgvproducts.Columns(6).HeaderText = "จำนวนสินค้า"
            dgvproducts.Columns(6).Width = 200
        End If
    End Sub
    Private Sub SetGrid() 'สร้างข้อความหัวคอลัมน์ แก้ไขหัวคอลัมน์ตามต้องการ

        If dgvproducts.Columns(0).HeaderText = "รหัสสินค้า" Then
            dgvproducts.Columns(0).Width = 100
            dgvproducts.Columns(1).HeaderText = "รายการสินค้า"
            dgvproducts.Columns(1).Width = 300
            dgvproducts.Columns(2).HeaderText = "ประเภทสินค้า"
            dgvproducts.Columns(2).Width = 300
            dgvproducts.Columns(3).HeaderText = "หน่วยนับ"
            dgvproducts.Columns(3).Width = 150
            dgvproducts.Columns(4).HeaderText = "ราคาต้นทุน"
            dgvproducts.Columns(4).Width = 150
            dgvproducts.Columns(5).HeaderText = "ราคาขาย"
            dgvproducts.Columns(5).Width = 100
            dgvproducts.Columns(6).HeaderText = "จำนวนสินค้า"
            dgvproducts.Columns(6).Width = 200
        End If
    End Sub
    Private Sub dgvcompany_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvproducts.CellContentClick
        Dim i As Integer 'ดับเบิ้ลคลิก
        With dgvproducts
            If e.RowIndex >= 0 Then
                i = .CurrentRow.Index
                FmOrder.txtP_ID.Text = .Rows(i).Cells(0).Value.ToString
                FmOrder.txtP_Name.Text = .Rows(i).Cells(1).Value.ToString
                FmOrder.txtP_Total.Text = .Rows(i).Cells(4).Value.ToString
            End If
        End With
        Me.Close()

    End Sub

    Private Sub txtP_TextChanged(sender As Object, e As EventArgs) Handles txtP.TextChanged
        Dim dt As New DataTable
        gsql = "Select * from PRODUCTS"

        gsql += " where P_Name like '%" & txtP.Text & "%'"
        With cmd
            .CommandType = CommandType.Text
            .CommandText = gsql
            .Connection = cnn
            dr = .ExecuteReader
            If dr.HasRows Then
                dt.Load(dr)
                dgvproducts.DataSource = dt
            End If
            dr.Close()
        End With
    End Sub

End Class