Microsoft KB Archive/183085

= PRB: Parameterized RDO query results in Error 40002 =

Article ID: 183085

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q183085



SYMPTOMS
Some parameterized RDO queries result in Error 40002, "Invalid Parameter Number," if the SET NOCOUNT ON/OFF option is used within the SQL query text.



RESOLUTION
This problem can be resolved by not using a parameter query and instead hard coding the parameters in the query string.

To do this, in the code below, comment out the lines of code beneath the PROBLEM section. Then uncomment the line of code beneath the WORKAROUND section.



STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



Steps to Reproduce Behavior
 Create a new Visual Basic Standard EXE project and add a reference to the Microsoft Remote Data Object 2.0.  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

  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= ;pwd= ;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 '< Run this code and watch for the Error 40002 to occur on the first line on which qy(0) is referenced.

Additional query words: @@identity kbRDO kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords: kbprb KB183085

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.