PRB: Missing Parameter Error Running Stored Procedure from ADO (193348)

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 <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.

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