DOCUMENT:Q199128  04-MAR-2002  [vbwin]
TITLE   :PRB: Error 40041 Calling RDO Parameters Through User Connection
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER::5.0,6.0
OPER/SYS:
KEYWORDS:kbDatabase kbRDO kbSQLServ kbUCDesigner kbVBp

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

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

SYMPTOMS
========

Referencing parameters by name using the User Connection Designer (UCD)
generates the following error:

   Error 40041 "Object collection: Couldn't find item indicated by text".

The error occurs if the parameter's properties have been viewed in the Query
Properties page prior to calling the parameter by name.

CAUSE
=====

When calling parameters by name using UCD or Remote Data Objects (RDO), each
parameter's name should be preceded by an "@." When viewing the parameter's
properties, the "@" prefix is removed from the parameter name. This causes RDO
not to recognize the parameter anymore and error 40041 occurs.

RESOLUTION
==========

There are two ways to work around this behavior:

 - Once the error 40041 occurs, the only way to work around it is to recreate
   the UserConnection object.

-or-

 - Reference parameters by their position in the parameters collection.

STATUS
======

This behavior is by design.

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

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

Stored Procedure:

Run the following script within ISQL:

        CREATE PROCEDURE Test @ID1 int,
                              @ID2 int
        AS
        DECLARE @RetParam int
        SELECT @RetParam = @ID1 + @ID2
        RETURN @RetParam
        Go

Create the UserConnection:

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

2. From the Project menu, select Components, click the Designers tab, then
   select "Microsoft UserConnection."

3. From the Project menu, choose Add ActiveX Designer, and then select Microsoft
   UserConnection.

4. Double-click UserConnection1. This opens a dialog box named UserConnection1
   Properties.

5. On the Connection tab, select either a DSN or DSN-less connection and fill in
   the appropriate information. On the Authentication tab, leave User Name and
   Password blank. In the ODBC Prompt Behavior drop-down list box, choose Never.

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

7. Alternate click UserConnection1 and choose Insert Query. This opens a dialog
   box named Query1 Properties.

8. Insert the source of the Query to be based on the stored procedure, and then
   select the Test stored procedure.

9. On the Parameters tab, Select ID1 then Select ID2.

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

Visual Basic Code:

1. Add a command button named Command1 to Form1.

2. Paste the following code in the General Declaration's section of Form1:

          Option Explicit
          Dim cn As New UserConnection1
          Dim qry As New rdoQuery
          Dim lngTemp As Long
          Dim intReturn As Integer
          Dim er As rdoError

          Private Sub Command1_Click()

          On Error GoTo UC_Error
          ' Establish connection.
          cn.EstablishConnection rdDriverNoPrompt

          Set qry = cn.rdoQueries("Query1")

          qry("@ID1") = 2
          qry("@ID2") = 3
          qry.Execute

          lngTemp = qry.rdoParameters("RETURN_VALUE")

          ' Check to see if you have a return value.
          If lngTemp = vbNull Then
             MsgBox "Sorry no value was returned back", , "Error occurred"
             GoTo Free
          Else
             intReturn = lngTemp
          End If

          MsgBox "Sum of ID1 and ID2 = " & intReturn, , "Output"

       Free:
          Set qry = Nothing
          Set cn = Nothing
       Exit Sub

       UC_Error:
      For each er in rdoErrors
         MsgBox er.Number & ": " & er.Description
      Next er

       End Sub

3. Press the F5 key to run the program. This causes error 40041 to occur.

REFERENCES
==========

For more information on the connection designer, please refer to the following
Microsoft Knowledge Base article:

   Q166281 HOWTO: Create and Implement a UserConnection

Additional query words:

======================================================================
Keywords          : kbDatabase kbRDO kbSQLServ kbUCDesigner kbVBp 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Version           : :5.0,6.0
Hardware          : x86
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 2002.