IndexOutOfRange error when accessing OleDbDataReader items

Question

I have some sql that works fine in access 2010 and returns the correct values. However, in VB.NET, when I run it causes the above error in the while loop. dr.HasRows is returning 'TRUE' but the var 'itm' shows nothing in auto window. Can someone please explain why this is happening and how I can correct it. Thanks

Dim cmd As OleDbCommand = New OleDbCommand("SELECT Max(Requests.[Request no]) AS [MaxOfRequest no], Requests.Customer, " &
                    "Max([Request Boxes].[Request no]) AS [MaxOfRequest no1], [Request Boxes].Customer " &
                    "FROM Requests, [Request Boxes] " &
                    "GROUP BY Requests.Customer, [Request Boxes].Customer " &
                    "HAVING (((Requests.Customer)='" & cmbCustomer.Text & "') AND (([Request Boxes].Customer)='" & cmbCustomer.Text & "')) " &
                    "ORDER BY Requests.Customer, [Request Boxes].Customer", oledbCnn)

            dr = cmd.ExecuteReader()

            If dr.HasRows Then <--- this returns true

                While dr.Read

                    itm = CStr(dr.Item("[MaxOfRequest no]")) <--- ERROR HERE
                    itm2 = CStr(dr.Item("[MaxOfRequest no1]"))

                End While

            End If

That dr.HasRows is true means there are rows. It doesn't mean that a particular field (like [MaxOfRequest no]) is present - which the error is trying to say. — Hans Keﬆing
– Hans Keﬆing, Commented Jan 24, 2014 at 12:42

Gord Thompson · Accepted Answer · 2014-01-24 12:32:53Z

2

You don't need the square brackets here:

itm = CStr(dr.Item("[MaxOfRequest no]"))

The square brackets are just delimiters in the SQL statement; they are not part of the column name itself. Try this instead:

itm = CStr(dr.Item("MaxOfRequest no"))

edited Jan 24, 2014 at 12:32

answered Jan 24, 2014 at 12:22

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1532468 Over a year ago

Thanks very much Gord

Collectives™ on Stack Overflow

IndexOutOfRange error when accessing OleDbDataReader items

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related