kkoehler
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 .Connectio n")
Connection.Open Application("DB2Connection ")
Set Command = Server.CreateObject("ADODB .Command")
Command.ActiveConnection = Connection
Command.ActiveConnection.C ursorLocat ion = 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_C OD", 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_T YP_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
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.Open Application("DB2Connection
Set Command = Server.CreateObject("ADODB
Command.ActiveConnection = Connection
Command.ActiveConnection.C
Command.CommandText = "DBO.SP_TP_OE_CONFIRM"
Command.CommandType = adCmdStoredProc
Set AH = Server.CreateObject("ADODB
if len(IDNum) > 0 THEN
Set Param = Command.CreateParameter("P
else
Set Param = Command.CreateParameter("P
end if
Command.Parameters.Append Param
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
Command.Parameters.Append Command.CreateParameter("P
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
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") .NumericSc ale = 2
Command.Parameters.Append Command.CreateParameter("P
command.Parameters("P_MTLY
command.Parameters("P_MTLY
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the update.