Stored procedure error: wrong number of parameters

Posted on 2007-10-04
Last Modified: 2010-10-05

I have a stored procedure that was written in COBOL and I am passing the parameters to ASP.  It was working fine until I added extra parameters.  I am getting an error in ASP that tells me:
IBM OLE DB Provider for DB2 error '80004005'
CLI0100E Wrong number of parameters. SQLSTATE=07001

I have compared the parameter being sent from the stored procedure to the ones in my page and they're exactly the same number of parameters.  Could the error be something else?

Below is my ASP:

        Set Connection = Server.CreateObject("ADODB.Connection")
      Connection.Open Application("DB2Connection")
      Set Command = Server.CreateObject("ADODB.Command")
      Command.ActiveConnection = Connection      
      Command.ActiveConnection.CursorLocation = 3
      Command.CommandText = "DBO.SP_TP_OE_CONFIRM"
      Command.CommandType = adCmdStoredProc
      Set AH = Server.CreateObject("ADODB.Recordset")
      if len(IDNum) > 0 THEN
            Set Param = Command.CreateParameter("P_M100_ID", adChar, adParamInput, 9, IDNum)
            Set Param = Command.CreateParameter("P_M100_ID", adChar, adParamInput, 9, null)
      end if
      Command.Parameters.Append Param
    Command.Parameters.Append  Command.CreateParameter("P_CCYY", adInteger, adParamInput, number)
      Command.Parameters.Append  Command.CreateParameter("P_SQLCODE", adInteger, adParamOutput, number)
      Command.Parameters.Append  Command.CreateParameter("P_SQLSTATE", adChar, adParamOutput, 5)
      Command.Parameters.Append  Command.CreateParameter("P_COV_SMK_COD", adChar, adParamOutput, 1)
      Command.Parameters.Append  Command.CreateParameter("P_CVRG_COD_DES", adChar, adParamOutput, 25)
    Command.Parameters.Append  Command.CreateParameter("P_CVRG_TYP_COD", adChar, adParamOutput, 1)
      Command.Parameters.Append  Command.CreateParameter("P_CVRG_PLAN_COD", adChar, adParamOutput, 3)
      Command.Parameters.Append  Command.CreateParameter("P_MTLY_DED_AMT", adInteger, adParamOutput, number)
      Command.Parameters.Append  Command.CreateParameter("P_TABLEDTL", adChar, adParamOutput, 3)
      Command.Parameters.Append  Command.CreateParameter("P_CVRG_COD_DES2", adChar, adParamOutput, 25)
      Command.Parameters.Append  Command.CreateParameter("P_EBC_CMT_TYP_COD", adChar, adParamOutput, 4)
      Command.Parameters.Append  Command.CreateParameter("P_DECD", adChar, adParamOutput, 8)

      Set AH = Command.Execute()

This are the parameters being sent from the stored procedure:

    IN  P_M100_ID                     CHARACTER(9)
  ,IN   P_CCYY                        INTEGER
  ,OUT  P_SQLCODE                     INTEGER
  ,OUT  P_SQLSTATE                    CHARACTER(5)
  ,OUT  P_SMK_COD                     CHARACTER(1)
  ,OUT  P_CVRG_COD_DES                CHARACTER(25)
  ,OUT  P_CVRG_TYP_COD                CHARACTER(1)
  ,OUT  P_CVRG_PLAN_COD               CHARACTER(3)
  ,OUT  P_MTLY_DED_AMT                DECIMAL(6, 2)
  ,OUT P_TABLEDTL                     CHARACTER(7)
  ,OUT  P_CVRG_COD_DES2               CHARACTER(25)
  ,OUT  P_EBC_CMT_TYP_COD             CHARACTER(4)
  ,OUT  P_DECD                        CHARACTER(8)

Any help or suggestions will be appreciated.

Question by:kkoehler
    LVL 4

    Expert Comment

    I'm not a current  DB2 expert, but you are creating   P_MTLY_DED_AMT  as a adInteger BUT the parm is returning a DECIMAL (6,2). That might be it.

    LVL 4

    Expert Comment

    I think it should be something like the following:

    Command.Parameters.Append  Command.CreateParameter("P_MTLY_DED_AMT", adDecimal, adParamOutput, number)
    command.Parameters("P_MTLY_DED_AMT").Precision = 6
    command.Parameters("P_MTLY_DED_AMT").NumericScale = 2

    Author Comment

    You're right the type of thet field was wrong, but that's not what's causing this since I'm still receiving the same error.  

    LVL 4

    Expert Comment

    which parameters did you add?

    Accepted Solution


    I figured it out.  The problem was that the  P_CCYY  parameter was not passing to ASP.  That's why the number of parameters was wrong.  


    LVL 4

    Expert Comment

    Thanks for the update.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Foreword (May 2015) This web page has appeared at Google.  It's definitely worth considering! How to Know You are Making a Difference at EE In August, 2013, one … provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
    Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now