PRB: Missing Parameter Error Running Stored Procedure from ADO

Retired KB Content Disclaimer

View products that this article applies to.

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 <procedure name> expects parameter <parameter name>, which was not supplied".

Back to the top

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.

Back to the top

RESOLUTION

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

Back to the top

STATUS

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

Back to the top

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"

Back to the top

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

Back to the top

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

Back to the top

kbcode kbprb KB193348

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top