PRB: Missing Parameter Error Running Stored Procedure from ADO

ID: Q193348

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0

SYMPTOMS

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

   Run-time error -2147217900 (80040e14), "Procedure <procedure name>
   expects parameter <parameter name>, 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.

MORE INFORMATION

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

Additional query words: kbADO150 kbADO200

Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: September 30, 1998