อยากเคลียร์ข้อมูลจาก Datagridview แล้วเพิ่มข้อมูลใหม่โดยไม่มีข้อมูลเก่ามาแสดง [ระบบร้านค้า]

Imports System.Data.OleDb

Public Class Mainmenu
    Public constring As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
  & Application.StartupPath & "\JarernDB.accdb"
    Public con As New OleDbConnection
    Public sqlCommand As New OleDbCommand



    Public Sub formatGrid()
        With DataGridView1
            
            .Columns(0).HeaderText = "รหัสสินค้า"
            .Columns(1).HeaderText = "ชื่อสินค้า"
            .Columns(2).HeaderText = "หน่วยสินค้า"
            .Columns(3).HeaderText = "จำนวนสินค้าในคลัง"
            .Columns(4).HeaderText = "ราคาสินค้า"

            .Columns(0).Width = 50
            .Columns(1).Width = 150
            .Columns(2).Width = 110
            .Columns(3).Width = 80
            .Columns(4).Width = 80


        End With
    End Sub
    Public Sub formatGrid2()
        With DataGridView2

            .Columns(0).HeaderText() = "รหัสรายการ"
            .Columns(1).HeaderText() = "รหัสสินค้า"
            .Columns(2).HeaderText() = "ชื่อ"
            .Columns(3).HeaderText() = "หน่วย"
            .Columns(4).HeaderText() = "ราคา"
            .Columns(5).HeaderText() = "จำนวน"
            .Columns(6).HeaderText() = "รวม"




            .Columns(0).Width = 120
            .Columns(1).Width = 105
            .Columns(2).Width = 155
            .Columns(3).Width = 50
            .Columns(4).Width = 100
            .Columns(5).Width = 80
            .Columns(6).Width = 135

        End With
    End Sub

    Private Sub Mainmenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        con.ConnectionString = constring

        If con.State = ConnectionState.Closed Then
            con.Open()

        End If


        SetGrid()
        formatGrid()
        SelectPayType()
        formatGrid2()
        DataGridView2.DataSource.clear()
    End Sub

    Public Sub SetGrid()
        Dim sqlQuery As String
        Dim adapter As New OleDb.OleDbDataAdapter
        Dim ds As DataSet = New DataSet

        sqlQuery = "SELECT * FROM Tb_Product order by p_code "

        DataGridView1.Font = New Font("Arial", 13, FontStyle.Bold)




        adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
        adapter.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)

    End Sub
    Public Sub SelectPayType()
        Dim sqlQuery As String
        Dim adapter As New OleDb.OleDbDataAdapter
        Dim ds As DataSet = New DataSet
        Dim x As Integer
        Dim sumamount As Double

        sqlQuery = "SELECT * FROM Tb_pay "
            

        DataGridView2.Font = New Font("Arial", 13, FontStyle.Bold)



        adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
        adapter.Fill(ds)
        DataGridView2.DataSource = ds.Tables(0)

        For x = 0 To DataGridView2.Rows.Count - 1                   'คำนวณราคาทั้งหมด
            sumamount = DataGridView2.Rows(x).Cells(6).Value + sumamount

        Next
        TextBox1.Text = sumamount

    End Sub
    Private Sub Add_product()
        Dim sqlQuery As String


        Try
            sqlQuery = "Insert Into Tb_pay(pa_id,pa_code,pa_name,pa_unit,pa_price,pa_amount,pa_pay)Values('" & Tb_order.Text & "','" & Tb_id.Text & "','" & Tb_name.Text & "','" & Tb_unit.Text & "','" & Tb_price.Text & "','" & num_amount.Value & "','" & Tb_sum.Text & "')"
            With sqlCommand
                .CommandText = sqlQuery 'บันทึกข้อมูลใหม่
                .Connection = con
                .ExecuteNonQuery()

            End With

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        
    End Sub
    Private Sub Del_product()

        Dim dtAdapter As OleDb.OleDbDataAdapter
        Dim sqlQuery As String
        Dim ds As New DataTable
        sqlQuery = "DELETE * FROM Tb_pay WHERE pa_code='" & Tb_id.Text & "'"
        dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
        dtAdapter.Fill(ds)




    End Sub
    Private Sub Runnumber()
        
    End Sub

    Private Sub B_Exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Exit.Click
        If MsgBox("ต้องการออกจากระบบใช่หรือไม่", vbQuestion + vbYesNo) = vbYes Then
            Me.Close()
        End If
    End Sub

    Private Sub B_Storage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Storage.Click
        warehouse.Show()
    End Sub

    Private Sub B_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Search.Click
        Dim sqlQuery As String
        Dim adapter As New OleDb.OleDbDataAdapter
        Dim ds As DataSet = New DataSet

        sqlQuery = "SELECT * FROM Tb_Product Where p_name  LIKE '%" & TextBox2.Text & "%'order by p_code"

        adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
        adapter.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim i As Integer
        i = DataGridView1.CurrentRow.Index

        Dim amount As Integer = DataGridView1.Item(3, i).Value.ToString
        num_amount.Maximum = amount

        Tb_id.Text = DataGridView1.Item(0, i).Value.ToString
        Tb_name.Text = DataGridView1.Item(1, i).Value.ToString
        Tb_unit.Text = DataGridView1.Item(2, i).Value.ToString
        Tb_price.Text = DataGridView1.Item(4, i).Value.ToString

        
        Tb_sum.Text = Tb_price.Text * num_amount.Value



    End Sub


    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Buyproduct.Show()

    End Sub


    Private Sub B_submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_submit.Click
        Dim sqlQuery As String
        Dim ds As New DataTable
        Dim dtAdapter As OleDb.OleDbDataAdapter


        sqlQuery = "SELECT * FROM Tb_Product WHERE p_code='" & Tb_id.Text & "'"
        dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
        dtAdapter.Fill(ds)
        If Len(num_amount.Value) = 0 Or Len(Tb_id.Text) = 0 Then
            MsgBox("กรุณาใส่จำนวน หรือ เลือกสินค้า", vbInformation)
            Exit Sub
        End If
        Try
            sqlQuery = "Update  Tb_Product Set p_amount = p_amount- '" & num_amount.Value & "' Where p_code='" & Tb_id.Text & "'"

            With sqlCommand
                .CommandText = sqlQuery 'แก้ไขข้อมูล
                .Connection = con
                .ExecuteNonQuery()
            End With



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Add_product()
        SelectPayType()
        SetGrid()

    End Sub

    Private Sub B_cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_cancel.Click
        Dim sqlQuery As String
        Dim ds As New DataTable
        Dim dtAdapter As OleDb.OleDbDataAdapter

        sqlQuery = "SELECT * FROM Tb_pay WHERE pa_code='" & Tb_id.Text & "'"
        dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
        dtAdapter.Fill(ds)

        Try
            sqlQuery = "Update  Tb_Product Set p_amount = p_amount+ '" & num_amount.Value & "' Where p_code='" & Tb_id.Text & "'"

            With sqlCommand
                .CommandText = sqlQuery 'แก้ไขข้อมูล
                .Connection = con
                .ExecuteNonQuery()
            End With



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        num_amount.Value = 0
        Del_product()
        SelectPayType()
        SetGrid()
    End Sub

    Private Sub DataGridView2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick

        Dim i As Integer
        i = DataGridView2.CurrentRow.Index
        If i = DataGridView2.RowCount - 1 Then Exit Sub




        Tb_id.Text = DataGridView2.Item(1, i).Value.ToString
        Tb_name.Text = DataGridView2.Item(2, i).Value.ToString
        Tb_unit.Text = DataGridView2.Item(3, i).Value.ToString
        Tb_price.Text = DataGridView2.Item(4, i).Value.ToString
        num_amount.Value = DataGridView2.Item(5, i).Value.ToString

    End Sub

    
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click




        



    End Sub
End Class