Link to home
Start Free TrialLog in
Avatar of kkoehler
kkoehlerFlag for United States of America

asked on

Stored procedure error: wrong number of parameters

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
Avatar of lrygiel
lrygiel
Flag of United States of America image

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.



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
Avatar of kkoehler

ASKER

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
which parameters did you add?
ASKER CERTIFIED SOLUTION
Avatar of kkoehler
kkoehler
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the update.