PRB: ADO Error -2147217896 when Calling MoveFirst()

Performing a parameterized query such as "Select EmployeeID, FirstName, LastName from Employees where FirstName = ?" and calling the MoveFirst() method of the recordset, may cause the following error:

This error occurs when using the ODBC Provider for OLE DB.

The error can be misleading, but the recordset most likely has a forward-only cursor.

The default cursor type for a recordset returned from a Command Execute() or from the Open()method of a recordset is forward only.

Since it is a forward-only cursor, in order to move backwards (as required by a MoveFirst command), ADO re-executes the query and then moves to the requested record.

When you set up Parameters in the Parameters collection of the Command object and then call Execute, ADO no longer considers the parameters valid after the Execute. Without valid parameters, the command cannot be re- executed to perform the MoveFirst and the error occurs.

Three possible solutions follow:

• Do not call MoveFirst or MovePrevious on this type of recordset. MoveNext works as expected.
• Instead of using parameters, build up a query string with concatenation (as in the example below) and use the non-parameterized string as the CommandText of the Command Object:
  

     'STRSQL = "Select EmployeeID, FirstName,
      LastName from Employees where Firstname = '" + fname + "'"'
  					

  
  NOTE: The error appears only with parameterized queries.
  
  
• Change the cursor type of the recordset to something other than adOpenForwardOnly. If you are using a Command object, set the Source property of a recordset to the command object and change the cursor type in the recordset's Open() call.

This behavior is by design.