PRB: ADO Error -2147217896 when Calling MoveFirst()

• ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.01, 2.1, 2.1 SP2

SYMPTOMS

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:

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 perform the MoveFirst and the error occurs.

RESOLUTION

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.

STATUS

This behavior is by design.

Additional query words: 0x80040E18 kbdse kbsweepnext

Keywords : kberrmsg kbADO kbADO100 kbADO150 kbADO200 kbADO210 kbDatabase kbGrpMDAC
Version : WINDOWS:1.0,1.5,2.0,2.01,2.1,2.1 SP2
Platform : WINDOWS
Issue type : kbprb