Issues with OleDbDataReader class

[kroysemaj]

I'm trying to read a boolean value from a table. For some reason, when the statement executes it kicks me out of the method and back out to .show() method. The data read takes place in a procedure that is called by the Form_Load procedure. Any idea why it's kicking out? I've tried several versions of the command but with zero change. Here's my code. The offending command is in line 51. Every time I run debug I get to that line and then am kicked back out to my form.show() method.

The end goal is to read a Boolean value Posted. If it is false then we read the data from this table to an accounts table to post the payments that are pending.

Thanks

Imports System.Data.OleDb

Public Class BankForm
    Dim con As New OleDbConnection

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

        postTransactions()

    End Sub

    Public Sub postTransactions()

        'On form load, update both BankAccounts from BankPostings table
        'declare variables, connections, and datasets
        Dim con As OleDbConnection = New OleDbConnection()
        Dim dbProvider As String
        Dim dbSource As String
        Dim query As String
        Dim maxID As Integer = 0
        Dim command As OleDbCommand
        Dim reader As OleDbDataReader
        Dim posted As Boolean
        Dim transactionDateTime As DateTime
        Dim studentID As Integer
        Dim transactionType As Char
        Dim transactionAmt As Decimal

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=final Project database.mdb;Persist Security Info=False"
        con.Open()

        'get max of BankPosting IDs 
        query = "SELECT MAX(BANKPOSTINGID) FROM BANKPOSTING"
        command = New OleDbCommand(query, con)
        reader = command.ExecuteReader()

        'get max off BankPosting IDs 
        While reader.Read
            maxID = reader(0).ToString
        End While
        MsgBox(maxID)


        'create new SQL query
        query = "SELECT POSTED FROM BANKPOSTING"
        command = New OleDbCommand(query, con)
        reader = command.ExecuteReader

        'Check Posted status in BankPosting
        For i As Integer = 0 To maxID
            posted = reader("POSTED")
            MsgBox(posted)

            If posted = False Then
                'get Date
                query = "SELECT Date FROM BANKPOSTING"
                command = New OleDbCommand(query, con)
                command.ExecuteReader()
                transactionDateTime = reader(1).read
                reader.Close()

                'get StudentID
                query = "SELECT StudentID FROM BANKPOSTING"
                command = New OleDbCommand(query, con)
                command.ExecuteReader()
                studentID = reader(2).read
                reader.Close()

                'get TransactionType
                query = "SELECT TransactionType FROM BANKPOSTING"
                command = New OleDbCommand(query, con)
                command.ExecuteReader()
                transactionType = reader(3).read
                reader.Close()

                'get TransactionType
                query = "SELECT TransactionAmt FROM BANKPOSTING"
                command = New OleDbCommand(query, con)
                command.ExecuteReader()
                transactionAmt = reader(4).read
                reader.Close()
                MsgBox(transactionDateTime.ToString + " " + studentID.ToString + " " + transactionType.ToString + " " + transactionAmt.ToString)
            End If

        Next i

        con.Close()

    End Sub


End Class

[thines01]

You're not setting the reader to anything after its first use.
You call command.ExecuteReader(), but you do not pass it TO the reader.

Also, since you do not have an array of readers, something like reader(1).Read should cause problems.

Also, since you are reading all of the data from the table BANKPOSTING, why not put it all in one command?

SELECT Date, StudentID, TransactionType, TransactionAmt FROM BANKPOSTING

[kroysemaj]

You're not setting the reader to anything after its first use.
You call command.ExecuteReader(), but you do not pass it TO the reader.

Also, since you do not have an array of readers, something like reader(1).Read should cause problems.

Also, since you are reading all of the data from the table BANKPOSTING, why not put it all in one command?

SELECT Date, StudentID, TransactionType, TransactionAmt FROM BANKPOSTING

I just noticed I don't have parentheses after .ExecuteReader, rookie mistake. As far as the reader(7).Read, that's how I was shown to make the reader read from a specific column (column 7 actually). I do the same in line 39 where I read the IDs to get the max. There may be a better, clearer way. We weren't really shown how to do this data manipulation in class so we're all kind of flailing a bit. I've seen reader.items(3).Read to achieve the same result but I'm not sure if that'll do what I want or not.

But, no, there's no array of readers.

Also yes, one command to read all that stuff. That sound you hear is me face-palming.

Thanks, I'll try this stuff out and report back.