PRB: Error When You Combine ADO Refresh Method with CreateParameter (298118)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
This article was previously published under Q298118

SYMPTOMS

ADO offers two methods to populate the list of input parameters that a parameterized stored procedure requires:
  • The Refresh method of the Command object's Parameters collection, which makes a trip to the database server to retrieve the parameter list.
  • The explicit CreateParameter method, which you use to build the parameter list in the client application.
If you combine the two methods, however, the list of parameters contains duplicates and generates ADO errors when you run the Command object. For example, Microsoft Data Access Components (MDAC) 2.6 and the Microsoft OLE DB Provider for SQL Server return the following error message:
Run-time error '-2147217900 (80040e14)':
Procedure or function [stored procedure name] has too many arguments specified.

CAUSE

If you call Parameters.Refresh to build the parameter list and then call CreateParameter, you effectively add each parameter to the list twice. When you try to run the Command, the error message informs you that you have more parameters than are defined in the stored procedure.

RESOLUTION

To work around this problem, use only one of the two above-mentioned methods to populate the Parameters collection. For performance reasons, Microsoft recommends that you use the CreateParameter syntax to avoid an unnecessary trip to the database server.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. Set a reference to Microsoft ActiveX Data Objects (ADO).
  3. Paste the following code in the Form_Load event procedure; or, if you prefer, add a CommandButton control to Form1, and paste the code into the Command1_Click event procedure:
      Dim cn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim prm As ADODB.Parameter
  Dim rs As ADODB.Recordset
  Set cn = New ADODB.Connection
  cn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;" & _
          "Integrated Security=SSPI"
  Set cmd = New ADODB.Command
  With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "CustOrderHist"
    .Parameters.Refresh
    Set prm = .CreateParameter("CustID", adChar, adParamInputOutput, 5)
    .Parameters.Append prm
    .Parameters("CustID").Value = "ALFKI"
  End With
  For Each prm In cmd.Parameters
    Debug.Print prm.Name
  Next
  Debug.Print "Count: " & cmd.Parameters.Count
  Set rs = cmd.Execute
  4. Modify the database connection string in the cn.Open statement as appropriate to point to an available Microsoft SQL Server 7.0 or SQL Server 2000 server with the Northwind sample database.
  5. Insert a breakpoint on the last line ("Set rs = cmd.Execute"), and run the code to that point. In the Debug window, you see the following output: 
    @RETURN_VALUE
@CustomerID
CustID
Count: 3
    The Parameters.Refresh method has populated the list with @RETURN_VALUE (which SQL Server always assumes, but which you can disregard) and @CustomerID (the stored procedure's one input parameter). CreateParameter adds this same input parameter to the list a second time, with the name that you assign.
  6. Run the last line of code. You receive the above-mentioned error message because the Parameters collection contains two input parameters, but the stored procedure is only expecting one. Note that these "duplicate" parameters are created in these circumstances even if you assign them the same names that they have in the database.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbDatabase kbprb KB298118
©2002 Microsoft Corporation. All rights reserved.