An "Invalid attempt to read from column ordinal" error occurs when you use DataReader in Visual C# (308614)

MORE INFORMATION

Setting the CommandBehavior.SequentialAccess flag causes the DataReader to read both rows and columns sequentially. The rows and columns are not buffered. After you read past a column, it is dropped from memory. 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 using Binary Large Object (BLOB) fields. If you do not use SequentialAccess, all the BLOB data is copied to the client. This can consume a lot of resources.

CommandBehavior.SequentialAccess also improves performance when accessing 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 the column is later than a previous accessed column.
• The data for all of the previously accessed columns is retrieved and then cached for potential 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

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

1. In Visual Studio .NET, create a new Visual C# Windows Application. Form1 is added to the project by default.
2. Add a Button control to Form1; Button1 is added by default.
3. Open the code window for Form1 and then paste the following code at the top of the code window:

   using System.Data;
   using System.Data.SqlClient;
   					

4. Open the code window for Button1 and paste the following code into the button1_Click event procedure:

   SqlConnection myConnection = new SqlConnection();
   
   // Modify this connection string to use your SQL Server and logins.
   myConnection.ConnectionString = "Server=server;uid=login;pwd=password;database=northwind";
   			
   SqlCommand myCommand = new SqlCommand();
   myCommand.CommandText = "SELECT FirstName, LastName FROM Employees";
   		
   myConnection.Open();
   
   myCommand.Connection = myConnection;
   // SequentialAccess gives forward-only reading of columns
   SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);
   
   //Uncomment the following line, and comment the preceding line to work around.
   //SqlDataReader myReader = myCommand.ExecuteReader();
   try {
   	// Read only the first row.
   	myReader.Read();
   
   	// Display the LastName and then the FirstName of the row.
   	MessageBox.Show(myReader["LastName"].ToString() + ", " + myReader["FirstName"].ToString());
   
   	// Uncomment the following line, and comment the preceding line to work around.
   	//MessageBox.Show(myReader["FirstName"].ToString() + " " + myReader["LastName"].ToString());
   	}
   catch (System.Exception ex) { 
   	MessageBox.Show(ex.ToString());
   	}
   finally 
   	{
   	// Always call Close when done reading.
   	myReader.Close();
   	myConnection.Close();
           }

5. Modify the following line:

   myConnection.ConnectionString = "Server=server;uid=login;pwd=password;database=northwind";
   						

   to use your SQL Server server name and login.
6. With the CommandBehavior.SequentialAccess flag set, try to access the columns out of sequence as follows:
   1. Press F5 to compile and run the client application. Note 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 line

         MessageBox.Show(myReader["LastName"].ToString() + ", " + myReader["FirstName"].ToString());
      						

   3. Press F5 to compile and run the client application. 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 lines

      SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.SequentialAccess);
      						

      -and-

      MessageBox.Show(myReader["FirstName"].ToString() + " " + myReader["LastName"].ToString());
      						

   2. Uncomment the lines

      //SqlDataReader myReader = myCommand.ExecuteReader();

      -and-

      MessageBox.Show(myReader["LastName"].ToString() + ", " + myReader["FirstName"].ToString());
      						

   3. Press F5 to compile and run the client application. 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.