PRB: ADO Error -2147217896 when Calling MoveFirst()

• ActiveX Data Objects (ADO), version 1.0

SYMPTOMS

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

   HRESULT = -2147217896 (0x80040E18)
   The rowset was built over a live data feed and cannot be restarted

CAUSE

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 do the MoveFirst, and the above error appears.

RESOLUTION

The following are possible solutions:

• Do not call MoveFirst or MovePrevious on this type of recordset. MoveNext will work as expected.
• Instead of using parameters, build up a query string with concatenation -- that is, 'STRSQL = "Select EmployeeID, FirstName, LastName from Employees where Firstname = '" + fname + "'"' -- and use the non-parameterized string as the CommandText of the Command Object. The above 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.