DOCUMENT:Q170368  09-JAN-2000  [vbwin]
TITLE   :PRB: "Invalid Cursor Position" Using UserConnection to GetChunk
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:
KEYWORDS:kbGrpDSVBDB

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

When you use the UserConnection Designer to establish a connection and retrieve
an RDO result set, you will encounter the following error:

   "Run-Time error '40002'
   S1109:[Microsoft][ODBC SQL Server Driver] Invalid Cursor Position"

if the RDO result set includes a BLOB (Binary Large Object) column and you do not
change any default values.

This error occurs at the GetChunk method.

CAUSE
=====

Due to the ODBC limitation, GetChunk fails when you use a Forward only cursor
with Rowset Size > 1. Using UserConnection Designer, the default cursor is
Forward only, and Rowset Size is 500. This causes "Invalid Cursor Position"
error.

RESOLUTION
==========

To get BLOB data using a Forward-only cursor, you can:

 - Set the Rowset Size to 1 on the Advanced tab of UserConnection Queryset
   object Properties page.

   -and-

 - Set Cursor Library to "Use server-side cursors" or "Use None" on the
   Miscellaneous tab of UserConnection object Properties page.

You could also open a Keyset type resultset in code by setting the CursorType
property of your RdoQuery object before executing the query, such as the
following:

      Dim cn As New UserConnection1
      Dim rs as rdoResultset

      cn.EstablishConnection                            'establish connection
      cn.rdoQueries("Query1").CursorType = rdOpenKeyset 'define cursortype
      cn.Query1                                         'execute the query
      Set rs = cn.LastQueryResults

MORE INFORMATION
================

The following example uses pub_info table in Pubs database, because pub_info has
a TEXT column, pr_info.

Steps to Reproduce Behavior
---------------------------

Task One: Create the UserConnection

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is
   created by default.

2. On the Project menu, click Components, click the Designer tab, and then place
   a check next to Microsoft UserConnection.

3. On the Project menu, click Add ActiveX Designer, and then click Microsoft
   UserConnection. This will bring up a dialog titled UserConnection1
   Properties.

4. On the Connection tab, select either a DSN or DSN-less connection and fill in
   the appropriate information. If you select DSN-less, make sure you specify a
   database in the Other ODBC Attributes area with database=Pubs. On the
   Authentication tab, fill in your username and password, and place a check
   next to Save Connection Information for Design Time.

5. Click OK to save this information, and return to the Designer window.

6. Insert a new Query by right-clicking on UserConnection1 and choosing Insert
   Query or by clicking on the Insert Query toolbar icon.

7. From the Source of Query, choose Based on User-Defined SQL. Then type the
   following SQL into the TextBox:

   Select pub_id, pr_info From pub_info

8. Click OK to save this information.

Task Two: Build the Visual Basic Code

1. Add a CommandButton, Command1, to Form1.

2. Add a TextBox, Text1, to Form1. Set the MultiLine property to True;
   ScrollBars property to 3 - Both.

3. In the Command1_Click() event, paste the following code:

         Private Sub Command1_Click()
            Dim cn As New UserConnection1
            Dim rs As rdoResultset
            Dim ColSize As Long
            With cn
               .EstablishConnection       'Establish connection

       ' **** Un-comment the following line to change default cursor to Keyset
       '       .rdoQueries("Query1").CursorType = rdOpenKeyset
       ' ****
               .Query1
            End With

            Set rs = cn.LastQueryResults
            If rs!pr_Info.ChunkRequired Then
               ColSize = rs!pr_info.ColumnSize
               Text1.Text = rs!pr_info.GetChunk(ColSize)
            Else
               Text1.Text = rs!pr_info
            End If

            rs.Close
            cn.Close
         End Sub

4. Start the program or press the F5 key.

REFERENCES
==========

For additional information, please see the following articles in the Microsoft
Knowledge Base:

   Q166281 : HOWTO: Create and Implement a UserConnection

   Q153238 : HOWTO: Use GetChunk and AppendChunk Methods of RDO Object

(c) Microsoft Corporation 1997, All Rights Reserved.
Contributions by Adrian Chiang, Microsoft Corporation


Additional query words: kbVBp500 kbVBp600 kbVBp kbdse kbDSupport

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.