Some code in my sub won't execute

Question

I have a sub in my program that removes items from my access database if their expiration date has passed. I am using an oledbdatareader to find the rows that need deleting. My problem is that most of this sub will not execute. I have used a messagebox to tell me that the code does not run after I have defined my datareader.

I have used this same code in many other functions and forms, and it works fine, but for some reason in this sub it doesn't. Can anyone see my problem?

Private Sub AutoDate()
    Using connection As New OleDbConnection(connectionstring)
        connection.Open()

        Dim Command As New OleDbCommand("SELECT ExpirationDate FROM Iventory", connection)
        Dim Command2 As New OleDbCommand("DELETE FROM Inventory WHERE ExpirationDate = @p1", connection)

        MessageBox.Show("Got here") 'This messagebox shows
        Dim Reader As OleDbDataReader = Command.ExecuteReader()
        MessageBox.Show("Got here") 'This messagebox does not show

        While Reader.Read()
                Dim ExpDate As DateTime = Reader.Item("ExpirationDate")
                Command2.Parameters.AddWithValue("@p1", ExpDate)

                If ExpDate.ToString < System.DateTime.Today.ToString Then
                    Dim cmd = Command2.ExecuteNonQuery()
                    If cmd > 0 Then
                        MessageBox.Show("Out of date items have been removed from database")
                    Else
                        Exit Sub
                    End If
                End If
        End While
        connection.Close()
    End Using
End Sub

Remove the command object. You are calling execute reader on this so it won't delete anything — Matt Wilko, Mar 09 '15 at 17:39
that first SQL SELECT statement is incomplete; not sure what its purpose is if you are removing expired stuff — Ňɏssa Pøngjǣrdenlarp, Mar 09 '15 at 17:52
The first statement reads all the expiration dates from the inventory table so that I can check if it is before today's date — ZenLogic, Mar 09 '15 at 17:55
You don't need to *read all the expiration dates*. Your `DELETE` already checks to see if it's before the indicated date with the `WHERE` clause. Just pass today's date as the parameter to `Command2`. (And to make it much cleaner, you can change the `=` to `<=` and clean up any old ones you missed along the way.) — Ken White, Mar 09 '15 at 18:00
Oh I see, of course! How silly of me... Thank you very much; this has been puzzling me for a while! — ZenLogic, Mar 09 '15 at 18:22

Some code in my sub won't execute

0 Answers0