We help IT Professionals succeed at work.

Stored procedure error: wrong number of parameters

kkoehler
kkoehler asked
on
1,551 Views
Last Modified: 2020-04-13
Hello:

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)
      else
            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)

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

Karla
Comment
Watch Question

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



Commented:
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

Commented:
lrygiel:
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.  

Karla

Commented:
which parameters did you add?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Thanks for the update.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.