Microsoft KB Archive/308069

= Error message when you use DataReader in Visual Basic .NET: &quot;Invalid attempt to read from column ordinal&quot; =

Article ID: 308069

Article Last Modified on 3/9/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q308069



For a Microsoft Visual C# .NET version of this article, see 308614.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.SqlClient



SYMPTOMS
When you use DataReader to read a row, if you try to access columns in that row, you receive an error message similar to the following:

System.InvalidOperationException: Invalid attempt to read from column ordinal '0'. With CommandBehavior.SequentialAccess, you may only read from column ordinal '2' or greater.



CAUSE
This problem occurs because you executed OleDbCommand or SqlCommand with the System.Data.CommandBehavior.SequentialAccess flag set but did not access the columns sequentially.



RESOLUTION
Use one of the following methods to work around this problem:
 * Read each column only once and in the sequence in which it is defined by the SELECT query.

NOTE: For performance reasons, which are listed in the &quot;More Information&quot; section, this is the preferred resolution.
 * Do not use CommandBehavior.SequentialAccess. If you do not use CommandBehavior.SequentialAccess, you can access a column in a row twice and read columns out of sequence.



STATUS
This behavior is by design.



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.  In Visual Studio .NET, create a new Visual Basic Windows Application. Form1 is added to the project by default.  Add the following code to the General Declarations of Form1: Imports System.Data.SqlClient  Add a Button to Form1. Button1 is added by default.  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(&quot;Server=server;uid=login;pwd=password;database=northwind&quot;)

Dim mySelectQuery As String = &quot;SELECT FirstName, LastName FROM Employees&quot;

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 & &quot;, &quot; & myReader!FirstName.ToString)

'Uncomment the following line, and comment the proceeding line, to work around. 'MessageBox.Show(myReader!FirstName.ToString & &quot; &quot; & 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  Modify the Connection string to use your SQL Server's server name and log on.</li> With the CommandBehavior.SequentialAccess flag set, try to access the columns out of sequence as follows: <ol style="list-style-type: lower-alpha;"> Press the F5 key to compile and run the client application. Notice the error that is displayed.</li> Click OK to dismiss the error. Stop the running project to return to the Design Environment.</li></ol> </li> With the CommandBehavior.SequentialAccess flag set, try to access the columns in the sequence that they are defined in the SELECT statement as follows: <ol style="list-style-type: lower-alpha;">  Uncomment the following line: 'MessageBox.Show(myReader!FirstName.ToString & &quot; &quot; & myReader!LastName.ToString) </li>  Comment the following line: MessageBox.Show(myReader!LastName.ToString & &quot;, &quot; & myReader!FirstName.ToString) </li> Press F5 to compile and run the client application. Notice that the column data is displayed without error.</li> Click OK to dismiss the message box. Stop the running project to return to the Design Environment.</li></ol> </li> With the CommandBehavior.SequentialAccess flag not set, try to access the columns out of sequence as follows: <ol style="list-style-type: lower-alpha;">  Comment the following lines: Dim myReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess) and MessageBox.Show(myReader!FirstName.ToString & &quot; &quot; & myReader!LastName.ToString) </li>  Uncomment the following lines: 'Dim myReader As SqlDataReader = myCommand.ExecuteReader and MessageBox.Show(myReader!LastName.ToString & &quot;, &quot; & myReader!FirstName.ToString) </li> Press F5 to compile and run the client application. Notice that the column data is displayed without error.</li> Click OK to dismiss the message box. Stop the running project to return to the Design Environment.</li></ol> </li></ol>

<div class="references_section">