PRB: Parameterized RDO query results in Error 40002

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE project and add a reference to the Microsoft Remote Data Object 2.0.
2. Run the following SQL Script against the pubs database to create a table with an Identity field.

         DROP TABLE IdentTest
         go
         CREATE TABLE IdentTest
         (
         id    INT       IDENTITY,
         Name   CHAR(20)    NULL
         )
         go
   
   						

3. Add a command button to the default form and add the following code behind the command button's click event. You need to change the connect string to connect to your copy of the pubs database.

         Private Sub Command1_Click()
   
            Dim MyConnect As rdoConnection
            Dim Rs As rdoResultset
            Dim qy As rdoQuery
            Dim szConnect As String
   
            szConnect = "Driver={SQL Server}" & _
              ";Server=YOURSERVERNAME;uid=<username>;pwd=<strong password>;dsn='';Database=pubs"
   
            Set MyConnect = rdoEnvironments(0).OpenConnection _
                  (dsname:="", prompt:=rdDriverNoPrompt, Connect:=szConnect)
   
            Set qy = New rdoQuery
            Set qy.ActiveConnection = MyConnect
   
            'PROBLEM SECTION -- Having the (?) parameter in the below line
            'results in Error 40002
            '---------------------------------------------------------
            qy.SQL = "SET NOCOUNT ON INSERT identtest(name)" _
             & " VALUES( ? ) SELECT @@IDENTITY SET NOCOUNT OFF"
            qy(0).Direction = rdParamInput  '<<Error 40002 occurs on this line
            qy(0) = "NewName"
   
            'WORKAROUND SECTION
            '----------------------------------------------------------
            'qy.SQL = "SET NOCOUNT ON INSERT identtest(name)" _
             & " VALUES('NewName') SELECT @@IDENTITY SET NOCOUNT OFF"
   
            Set Rs = qy.OpenResultset
            Debug.Print "The Identity is " & Rs(0).Value
   
            Set qy = Nothing
            Set Rs = Nothing
            Set MyConnect = Nothing
   
         End Sub
   
   						

4. Run this code and watch for the Error 40002 to occur on the first line on which qy(0) is referenced.