Error message when you use DataReader in Visual Basic .NET: "Invalid attempt to read from column ordinal" (308069)

MORE INFORMATION

Setting the CommandBehavior.SequentialAccess flag causes the DataReader to read both rows and columns sequentially. However, the rows and columns are not buffered. After you read past a column, the column is dropped from memory. Thus, any attempt to re-read the column or read previously read columns results in an exception.

Using the CommandBehavior.SequentialAccess flag provides a performance benefit, especially when you use Binary Large Object (BLOB) fields. If you do not use SequentialAccess, all of the BLOB data is copied to the client. This may consume a lot of resources. CommandBehavior.SequentialAccess also improves performance when you access non-BLOB fields. When CommandBehavior.SequentialAccess is not set, you can access a column out of order; however, you incur the following overhead:

• The column is checked to see if it is later than a previous accessed column.
• The data for all the previously accessed columns is retrieved and then cached for possible later retrieval.

Columns must be checked and cached because when you use the DataReader, the underlying stream is forward-only for rows as well as column access.

Steps to Reproduce the Behavior

NOTE: This sample uses the Northwind database that comes with Microsoft SQL Server.

1. In Visual Studio .NET, create a new Visual Basic Windows Application. Form1 is added to the project by default.
2. Add the following code to the General Declarations of Form1:

   Imports System.Data.SqlClient
   					

3. Add a Button to Form1. Button1 is added by default.
4. Open the Code Window for Button1. Paste the following code into the Button1_Click event procedure:

   'Modify this Connection string to use your SQL Server and logins.
   Dim myConnstring As New String("Server=server;uid=login;pwd=password;database=northwind")
   
   Dim mySelectQuery As String = "SELECT FirstName, LastName FROM Employees"
   
   Dim myConnection As New SqlConnection(myConnstring)
   Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
   
   myConnection.Open()
   
   'SequentialAccess gives forward-only reading of columns.
   Dim myReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess)
   
   'Uncomment the following line, and comment the proceeding line, to work around.
   'Dim myReader As SqlDataReader = myCommand.ExecuteReader()
   
   Try
      'Read only the first row.
      myReader.Read()
   
      'Display the LastName then the FirstName of the row.
      MessageBox.Show(myReader!LastName.ToString & ", " & myReader!FirstName.ToString)
   
      'Uncomment the following line, and comment the proceeding line, to work around.
      'MessageBox.Show(myReader!FirstName.ToString & " " & myReader!LastName.ToString)
   
   Catch ex As Exception
      MsgBox(ex.ToString)
   
   Finally
      ' Always call Close when done reading.
      myReader.Close()
      ' Always call Close when done reading.
      myConnection.Close()
   End Try
   					

5. Modify the Connection string to use your SQL Server's server name and log on.
6. With the CommandBehavior.SequentialAccess flag set, try to access the columns out of sequence as follows:
   1. Press the F5 key to compile and run the client application. Notice the error that is displayed.
   2. Click OK to dismiss the error. Stop the running project to return to the Design Environment.
7. With the CommandBehavior.SequentialAccess flag set, try to access the columns in the sequence that they are defined in the SELECT statement as follows:
   1. Uncomment the following line:

         'MessageBox.Show(myReader!FirstName.ToString & " " & myReader!LastName.ToString)

   2. Comment the following line:

         MessageBox.Show(myReader!LastName.ToString & ", " & myReader!FirstName.ToString)
      						

   3. Press F5 to compile and run the client application. Notice that the column data is displayed without error.
   4. Click OK to dismiss the message box. Stop the running project to return to the Design Environment.
8. With the CommandBehavior.SequentialAccess flag not set, try to access the columns out of sequence as follows:
   1. Comment the following lines:

      Dim myReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess)
      						

      and

      MessageBox.Show(myReader!FirstName.ToString & " " & myReader!LastName.ToString)
      						

   2. Uncomment the following lines:

      'Dim myReader As SqlDataReader = myCommand.ExecuteReader()
      						

      and

      MessageBox.Show(myReader!LastName.ToString & ", " & myReader!FirstName.ToString)
      						

   3. Press F5 to compile and run the client application. Notice that the column data is displayed without error.
   4. Click OK to dismiss the message box. Stop the running project to return to the Design Environment.