Imports System.Data.OleDb

Public Class ExpiredProds
    Private dbc As New OleDbConnection(conStr)
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs)

    End Sub

    Private Sub ExpiredProds_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dba As New OleDbDataAdapter("SELECT tbStocks.ProdID, tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.ExpirationDate, tbStocks.Quantity FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID WHERE ExpirationDate<=#" & Now() & "#;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView1.DataSource = dbset.Tables(0).DefaultView
    End Sub

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



        Dim ins As New OleDbCommand("INSERT INTO tbExpired (ProdID, Price, ExpirationDate, DateRemInv) VALUES(" & Me.DataGridView1.CurrentRow.Cells(0).Value & "," & Me.DataGridView1.CurrentRow.Cells(3).Value & ",#" & Me.DataGridView1.CurrentRow.Cells(4).Value & "#, #" & Now() & "#);", dbc)
        dbc.Open()
        ins.ExecuteNonQuery()
        dbc.Close()

        Dim del As New OleDbCommand("DELETE FROM tbStocks WHERE ProdID=" & Me.DataGridView1.CurrentRow.Cells(0).Value & ";", dbc)
        dbc.Open()
        del.ExecuteNonQuery()
        dbc.Close()




        Dim dba As New OleDbDataAdapter("SELECT tbStocks.ProdID, tbStocks.StockID,tbProduct.ProdName,tbStocks.Price,tbStocks.ExpirationDate, tbStocks.Quantity FROM tbStocks INNER JOIN tbProduct ON tbStocks.ProdID=tbProduct.ProdID WHERE ExpirationDate<=#" & Now() & "#;", dbc)
        Dim dbset As New DataSet
        dba.Fill(dbset)
        Me.DataGridView1.DataSource = dbset.Tables(0).DefaultView
        MessageBox.Show("Successfully deleted from Inventory!", "Delete Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub
End Class