Microsoft KB Archive/298118

= PRB: Error When You Combine ADO Refresh Method with CreateParameter =

Article ID: 298118

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 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.



Steps to Reproduce Behavior
 Create a new Standard EXE project in Visual Basic. Form1 is created by default. Set a reference to Microsoft ActiveX Data Objects (ADO).  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 &quot;Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;&quot; & _ &quot;Integrated Security=SSPI&quot; Set cmd = New ADODB.Command With cmd Set .ActiveConnection = cn   .CommandType = adCmdStoredProc .CommandText = &quot;CustOrderHist&quot; .Parameters.Refresh Set prm = .CreateParameter(&quot;CustID&quot;, adChar, adParamInputOutput, 5) .Parameters.Append prm .Parameters(&quot;CustID&quot;).Value = &quot;ALFKI&quot; End With For Each prm In cmd.Parameters Debug.Print prm.Name Next Debug.Print &quot;Count: &quot; & cmd.Parameters.Count Set rs = cmd.Execute  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.  Insert a breakpoint on the last line (&quot;Set rs = cmd.Execute&quot;), 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.  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 &quot;duplicate&quot; parameters are created in these circumstances even if you assign them the same names that they have in the database.</li></ol>

Additional query words: refresh -2147217900 (80040e14)

Keywords: kbdatabase kbprb KB298118

-

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

© Microsoft Corporation. All rights reserved.