Microsoft KB Archive/193348

= PRB: Missing Parameter Error Running Stored Procedure from ADO =

Article ID: 193348

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q193348



SYMPTOMS
When you execute a stored procedure through an ActiveX Data Objects (ADO) Command object that is referenced through an ADO Connection object, the following error can occur:

Run-time error -2147217900 (80040e14), "Procedure expects parameter, which was not supplied".



CAUSE
When executing the ConnectionObject.CommandName, ADO first looks for a Command object whose Name property is CommandName, and then it searches for a stored procedure called CommandName. If a Command object is created with the appropriate parameters appended to its Parameters collection, but the Name property is not set, then this Command object will not be used as intended when executing the stored procedure, which results in the error shown in the SYMPTOMS section.



RESOLUTION
Make sure that the Name property is set on a Command object before attempting to reference it through the Connection object.



STATUS
Microsoft has confirmed this to be a problem in ActiveX Data Objects versions 1.5 and 2.0 for Windows.



Steps to Reproduce Behavior
This sample assumes that there is an ODBC data source name (DSN), named "Test", for a database containing a stored procedure called "TestProc." The following SQL defines TestProc in Microsoft SQL Server: CREATE PROC TestProc @param1 int out AS SELECT @param1 = 5 In order to get the following code to execute without error, uncomment the following line: cmd.Name = "TestProc"

Sample Code
Sub Main

Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim p1 As ADODB.Parameter Dim strConnect As String

strConnect = "DSN=Test" cn.ConnectionString = strConnect cn.CursorLocation = adUseServer cn.Open

cmd.CommandType = adCmdStoredProc cmd.CommandText = "TestProc" 'cmd.Name = "TestProc"

Set p1 = cmd.CreateParameter("p1", adInteger, adParamInputOutput,, 0)

cmd.Parameters.Append p1    Set cmd.ActiveConnection = cn

cn.TestProc

End Sub

Keywords: kbprb kbcode KB193348

-

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

© Microsoft Corporation. All rights reserved.