Microsoft KB Archive/170368

From BetaArchive Wiki

Article ID: 170368

Article Last Modified on 1/8/2003



APPLIES TO

  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition



This article was previously published under Q170368

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

  1. 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:

166281 : HOWTO: Create and Implement a UserConnection

153238 : 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: kbprb KB170368