PDA

Click to See Complete Forum and Search --> : Please help with SQL Stored procedure problem

KentBrown
09-28-2006, 06:24 PM
Please help with SQL Stored procedure problem
I have this ASP VBScript code running in IIS V5 hitting DB2/400 V5R2:

Set cn = Server.CreateObject ("ADODB.Connection")
cn.ConnectionTimeout = 999
cn.CommandTimeout = 999
cn.ConnectionString = "dsn=AS400;uid=CCAP;pwd=CCAP"
cn.Open
Set Cmd1 = Server.CreateObject ("ADODB.Command")
Cmd1.CommandText = "RTVSVC001(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Set Cmd1.ActiveConnection = cn
Cmd1.CommandType = adCmdStoredProc
Set rs1 = Server.CreateObject("ADODB.Recordset")
nVarRec = 0 'Number of records returned from Stored Procedure
Set P01 = Server.CreateObject ("ADODB.Parameter")
Set P02 = Server.CreateObject ("ADODB.Parameter")
Set P03 = Server.CreateObject ("ADODB.Parameter")
Set P04 = Server.CreateObject ("ADODB.Parameter")
Set P05 = Server.CreateObject ("ADODB.Parameter")
Set P06 = Server.CreateObject ("ADODB.Parameter")
Set P07 = Server.CreateObject ("ADODB.Parameter")
Set P08 = Server.CreateObject ("ADODB.Parameter")
Set P09 = Server.CreateObject ("ADODB.Parameter")
Set P10 = Server.CreateObject ("ADODB.Parameter")
Set P11 = Server.CreateObject ("ADODB.Parameter")
Set P12 = Server.CreateObject ("ADODB.Parameter")
Set P13 = Server.CreateObject ("ADODB.Parameter")
Set P14 = Server.CreateObject ("ADODB.Parameter")
Set P15 = Server.CreateObject ("ADODB.Parameter")
Set P16 = Server.CreateObject ("ADODB.Parameter")

Set P01 = Cmd1.CreateParameter("LIBRARY", adChar, adParamInput, 10, "SCSDTAPD")
Set P02 = Cmd1.CreateParameter("GROUP", adChar, adParamInput, 5, "30 ")
Set P03 = Cmd1.CreateParameter("DEALER", adInteger, adParamInput, 7, 36147)
Set P04 = Cmd1.CreateParameter("B3GZCD", adChar, adParamOutput, 5,"")
Set P05 = Cmd1.CreateParameter("B3B1CD", adChar, adParamOutput, 5,"")
Set P06 = Cmd1.CreateParameter("B3CJNB", adInteger, adParamOutput, 11,0)
Set P07 = Cmd1.CreateParameter("B3CINB", adInteger, adParamOutput, 3,0)
Set P08 = Cmd1.CreateParameter("B3CHNB", adInteger, adParamOutput, 3,0)
Set P09 = Cmd1.CreateParameter("B3DCST", adChar, adParamOutput, 5,"")
Set P10 = Cmd1.CreateParameter("B3E0TX", adChar, adParamOutput, 30,"")
Set P11 = Cmd1.CreateParameter("B3CYNB", adInteger, adParamOutput, 7,0)
Set P12 = Cmd1.CreateParameter("B3KPNB", adInteger, adParamOutput, 7,0)
Set P13 = Cmd1.CreateParameter("B3A1NB", adInteger, adParamOutput, 7,0)
Set P14 = Cmd1.CreateParameter("B3LUNB", adInteger, adParamOutput, 7,0)
Set P15 = Cmd1.CreateParameter("B3JKTX", adChar, adParamOutput, 5,"")
Set P16 = Cmd1.CreateParameter("B3BFNB", adInteger,adParamOutput,7,0)
Cmd1.Parameters.Append P01
Cmd1.Parameters.Append P02
Cmd1.Parameters.Append P03
Cmd1.Parameters.Append P04
Cmd1.Parameters.Append P05
Cmd1.Parameters.Append P06
Cmd1.Parameters.Append P07
Cmd1.Parameters.Append P08
Cmd1.Parameters.Append P09
Cmd1.Parameters.Append P10
Cmd1.Parameters.Append P11
Cmd1.Parameters.Append P12
Cmd1.Parameters.Append P13
Cmd1.Parameters.Append P14
Cmd1.Parameters.Append P15
Cmd1.Parameters.Append P16
Set rs1 = Cmd1.Execute(,,adCmdStoredProc)

The Stored procedure is: (it runs OK when hit on 400 with test tool.)
CREATE PROCEDURE RTVSVC001(
IN LIBRARY CHARACTER(10),
IN GROUP CHARACTER(5),
IN DEALER DECIMAL(7,0),
OUT B3GZCD CHARACTER(5),
OUT B3B1CD CHARACTER(5),
OUT B3CJNB DECIMAL(11,0),
OUT B3CINB DECIMAL(3,0),
OUT B3CHNB DECIMAL(3,0),
OUT B3DCST CHARACTER(5),
OUT B3EOTX CHARACTER(30),
OUT B3CYNB DECIMAL(7,0),
OUT B3KPNB DECIMAL(7,0),
OUT B3A1NB DECIMAL(7,0),
OUT B3LUNB DECIMAL(7,0),
OUT B3JKTX CHARACTER(5),
OUT B3BFNB DECIMAL(7,0))
LANGUAGE SQL READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
SELECT B3GZCD,B3B1CD,B3CJNB,B3CINB,B3CHNB,B3DCST,
B3E0TX,B3CYNB,B3KPNB,B3A1NB,B3LUNB,B3JKTX,B3BFNB
FROM SCSVCP
WHERE B3GZCD = GROUP AND B3A1NB = DEALER;
END

The error I get is:

icrosoft OLE DB Provider for ODBC Drivers error '80040e10'

No value given for one or more required parameters.

/ContractsSP.asp, line 83
line 83 is Set rs1 = Cmd1.Execute(,,adCmdStoredProc)

Can you advise?
Thanks
chazzy
09-28-2006, 06:44 PM
try using Cmd1.Execute('','',adCmdStoredProc) instead.
KentBrown
09-28-2006, 06:48 PM
Thanks,
Same result
mattyblah
09-29-2006, 01:38 PM
I've bolded what I think the problem is.

Please help with SQL Stored procedure problem
I have this ASP VBScript code running in IIS V5 hitting DB2/400 V5R2:

Set cn = Server.CreateObject ("ADODB.Connection")
cn.ConnectionTimeout = 999
cn.CommandTimeout = 999
cn.ConnectionString = "dsn=AS400;uid=CCAP;pwd=CCAP"
cn.Open
Set Cmd1 = Server.CreateObject ("ADODB.Command")
Cmd1.CommandText = "RTVSVC001(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
Set Cmd1.ActiveConnection = cn
Cmd1.CommandType = adCmdStoredProc
Set rs1 = Server.CreateObject("ADODB.Recordset")
nVarRec = 0 'Number of records returned from Stored Procedure
Set P01 = Server.CreateObject ("ADODB.Parameter")
Set P02 = Server.CreateObject ("ADODB.Parameter")
Set P03 = Server.CreateObject ("ADODB.Parameter")
Set P04 = Server.CreateObject ("ADODB.Parameter")
Set P05 = Server.CreateObject ("ADODB.Parameter")
Set P06 = Server.CreateObject ("ADODB.Parameter")
Set P07 = Server.CreateObject ("ADODB.Parameter")
Set P08 = Server.CreateObject ("ADODB.Parameter")
Set P09 = Server.CreateObject ("ADODB.Parameter")
Set P10 = Server.CreateObject ("ADODB.Parameter")
Set P11 = Server.CreateObject ("ADODB.Parameter")
Set P12 = Server.CreateObject ("ADODB.Parameter")
Set P13 = Server.CreateObject ("ADODB.Parameter")
Set P14 = Server.CreateObject ("ADODB.Parameter")
Set P15 = Server.CreateObject ("ADODB.Parameter")
Set P16 = Server.CreateObject ("ADODB.Parameter")

Set P01 = Cmd1.CreateParameter("LIBRARY", adChar, adParamInput, 10, "SCSDTAPD")
Set P02 = Cmd1.CreateParameter("GROUP", adChar, adParamInput, 5, "30 ")
Set P03 = Cmd1.CreateParameter("DEALER", adInteger, adParamInput, 7, 36147)
Set P04 = Cmd1.CreateParameter("B3GZCD", adChar, adParamOutput, 5,"")
Set P05 = Cmd1.CreateParameter("B3B1CD", adChar, adParamOutput, 5,"")
Set P06 = Cmd1.CreateParameter("B3CJNB", adInteger, adParamOutput, 11,0)
Set P07 = Cmd1.CreateParameter("B3CINB", adInteger, adParamOutput, 3,0)
Set P08 = Cmd1.CreateParameter("B3CHNB", adInteger, adParamOutput, 3,0)
Set P09 = Cmd1.CreateParameter("B3DCST", adChar, adParamOutput, 5,"")
Set P10 = Cmd1.CreateParameter("B3E0TX", adChar, adParamOutput, 30,"")
Set P11 = Cmd1.CreateParameter("B3CYNB", adInteger, adParamOutput, 7,0)
Set P12 = Cmd1.CreateParameter("B3KPNB", adInteger, adParamOutput, 7,0)
Set P13 = Cmd1.CreateParameter("B3A1NB", adInteger, adParamOutput, 7,0)
Set P14 = Cmd1.CreateParameter("B3LUNB", adInteger, adParamOutput, 7,0)
Set P15 = Cmd1.CreateParameter("B3JKTX", adChar, adParamOutput, 5,"")
Set P16 = Cmd1.CreateParameter("B3BFNB", adInteger,adParamOutput,7,0)
Cmd1.Parameters.Append P01
Cmd1.Parameters.Append P02
Cmd1.Parameters.Append P03
Cmd1.Parameters.Append P04
Cmd1.Parameters.Append P05
Cmd1.Parameters.Append P06
Cmd1.Parameters.Append P07
Cmd1.Parameters.Append P08
Cmd1.Parameters.Append P09
Cmd1.Parameters.Append P10
Cmd1.Parameters.Append P11
Cmd1.Parameters.Append P12
Cmd1.Parameters.Append P13
Cmd1.Parameters.Append P14
Cmd1.Parameters.Append P15
Cmd1.Parameters.Append P16
Set rs1 = Cmd1.Execute(,,adCmdStoredProc)

The Stored procedure is: (it runs OK when hit on 400 with test tool.)
CREATE PROCEDURE RTVSVC001(
IN LIBRARY CHARACTER(10),
IN GROUP CHARACTER(5),
IN DEALER DECIMAL(7,0),
OUT B3GZCD CHARACTER(5),
OUT B3B1CD CHARACTER(5),
OUT B3CJNB DECIMAL(11,0),
OUT B3CINB DECIMAL(3,0),
OUT B3CHNB DECIMAL(3,0),
OUT B3DCST CHARACTER(5),
OUT B3EOTX CHARACTER(30),
OUT B3CYNB DECIMAL(7,0),
OUT B3KPNB DECIMAL(7,0),
OUT B3A1NB DECIMAL(7,0),
OUT B3LUNB DECIMAL(7,0),
OUT B3JKTX CHARACTER(5),
OUT B3BFNB DECIMAL(7,0))
LANGUAGE SQL READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
SELECT B3GZCD,B3B1CD,B3CJNB,B3CINB,B3CHNB,B3DCST,
B3E0TX,B3CYNB,B3KPNB,B3A1NB,B3LUNB,B3JKTX,B3BFNB
FROM SCSVCP
WHERE B3GZCD = GROUP AND B3A1NB = DEALER;
END

The error I get is:

icrosoft OLE DB Provider for ODBC Drivers error '80040e10'

No value given for one or more required parameters.

/ContractsSP.asp, line 83
line 83 is Set rs1 = Cmd1.Execute(,,adCmdStoredProc)

Can you advise?
Thanks


I'd change this
Set P10 = Cmd1.CreateParameter("B3E0TX", adChar, adParamOutput, 30,"")
to this
Set P10 = Cmd1.CreateParameter("B3EOTX", adChar, adParamOutput, 30,"")
russell
09-29-2006, 07:31 PM
try testing by changing those empty strings to at least one space. might need to explicitly cast param values to expected data types.

also don't use dsn. did u recently upgrade to V5R2?

should be using client access oledb provider for db2 (preferred) or ms oledb provider for db2