PRB: RDO Forward-Only Cursor Doesn't Allow MoveLast

This article was previously published under Q141171
This article has been archived. It is offered "as is" and will no longer be updated.
If you try to use the MoveLast method on a forward-only remote data objectrecordset, you receive the error
"S1106: [Microsoft][ODBC SQL Server Driver]Fetch type out of range".
Forward-only resultsets are the only type returned by the OpenResultsetmethod.
Forward-only cursors only allow the use of MoveNext methods. This is alimitation of ODBC.
There is no direct way to implement an exact copy of MoveLast with codewhen using a resultset with a forward-only cursor. The following code loopsthrough and touches all records in a resultset. When the loop finishes, thecurrent record will be the EOF marker.
   'The following code loops through all the records in a resultset   While Not rdoToMove.EOF      'do any processing here      rdoToMove.MoveNext   Wend				
To determine the number of records in a resultset, code a loop like the oneabove and increment a count variable inside the loop.
This behavior is by design.
When RDO works with a forward-only resultset, it uses a call to the ODBCAPI function SQLExtendedFetch to obtain data. As one of its parameters,this function takes a value that specifies the type of fetch to beperformed. ODBC specifies that when a resultset has a forward-only cursor,this fetch argument may only be SQL_FETCH_NEXT. If the argument issomething besides this constant, ODBC returns 'S1106: Fetch type out ofrange,' indicating that the argument was invalid. For more information, seethe reference listed in the References section below.

Steps to Reproduce

  1. Start Visual Basic.
  2. On the File menu, click New Project.
  3. Double-click the Command button icon in the Toolbox to create a command button on the form.
  4. Insert the following code in the command button's Click event. Replace strDSN, strConnect, and strSQL with the relevant values for your database.

    Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.
       Private Sub Command1_Click()             Dim ps As rdoPreparedStatement             Dim conn As rdoConnection             Dim strDSN As String             Dim strConnect As String             Dim strSQL As String             strDSN = "pubs"             strConnect = "Username=<username>;PWD=<strong password>;database=pubs"             strSQL = "Select * From Authors"             With rdoEnvironments(0)                 .CursorDriver = rdUseOdbc             Set conn = .OpenConnection _               (strDSN, rdDriverNoPrompt, False, strConnect)             End With             Set ps = conn.CreatePreparedStatement("", strSQL)             Set rs = ps.OpenResultset(rdOpenForwardOnly)             rs.MoveLast      'this line causes the error   End Sub						
  5. On the Run menu, click Start (ALT, R, S) or press the F5 key. The program will stop when it reaches the rs.Movelast line, and give the error listed above.
Microsoft ODBC 2.0 Programmer's Reference and SDK Guide: SQLExtendedFetchfunction - return code S1106.
4.00 vb4win vb432

Article ID: 141171 - Last Review: 01/09/2015 05:27:59 - Revision: 2.0

Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

  • kbnosurvey kbarchive kbenv kbdatabase kbprb KB141171