How to insert data in Db2 database using store procedure with Vb6
For i = 1 To lvwDatalist.ListItems.Count
If Trim(lvwDatalist.ListItems(i).Text) = "" Then Exit For
With oCmd
.Parameters.Refresh
.ActiveConnection = cnAP
.CommandText = ("{CALL DB2ADMIN.INSERT_MISYS_ACTSTAT(?,?,?,?,?,?,?,?,?)}")
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("p_ACTTIT", adVarChar, adParamInputOutput, 5000)
.Parameters.Append .CreateParameter("p_BRANCD", adVarChar, adParamInputOutput, 200)
.Parameters.Append .CreateParameter("p_CUSTID", adVarChar, adParamInputOutput, 50)
.Parameters.Append .CreateParameter("p_ACSUFIX", adVarChar, adParamInputOutput, 100)
.Parameters.Append .CreateParameter("p_ACTNUM", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_BLOCKED", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_INACTIVE", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_CLOSED", adVarChar, adParamInputOutput, 25)
.Parameters.Append .CreateParameter("p_CURRBAL", adDecimal, adParamInputOutput, 5)
oCmd.Parameters("p_ACTTIT").Value = Trim(lvwDatalist.ListItems(i).Text)
oCmd.Parameters("p_BRANCD").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(1))
oCmd.Parameters("p_CUSTID").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(2))
oCmd.Parameters("p_ACSUFIX").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(3))
oCmd.Parameters("p_ACTNUM").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(4))
oCmd.Parameters("p_BLOCKED").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(5))
oCmd.Parameters("p_INACTIVE").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(6))
oCmd.Parameters("p_CLOSED").Value = fixQuotes(lvwDatalist.ListItems(i).SubItems(7))
''oCmd.Parameters("p_CURRBAL").Value = IIf(fixQuotes(lvwDatalist.ListItems(i).SubItems(8)) = "", 0, lvwDatalist.ListItems(i).SubItems(8))
oCmd.Parameters("p_CURRBAL").Value = Val(lvwDatalist.ListItems(i).SubItems(8))
.Execute
End With
I am using this code and its give me error ---
Runtime Error 2147217904(80040e10)
No value given for one or more required parameters.
AND THIS IS MY SP IN DB2
CREATE PROCEDURE INSERT_MISYS_ACTSTAT (IN P_ACTTIT VARCHAR(5000),
IN P_BRANCD VARCHAR(50),
IN P_CUSTID VARCHAR(100),
IN P_ACSUFIX VARCHAR(3),
IN P_ACTNUM VARCHAR(25),
IN P_BLOCKED VARCHAR(1),
IN P_INACTIVE VARCHAR(1),
IN P_CLOSED VARCHAR(1),
IN P_CURRBAL DECIMAL(8,0) DEFAULT 0)
P1: BEGIN
INSERT INTO DB2ADMIN.MISYS_ACTSTAT (
ACTTIT,
BRANCD,
CUSTID,
ACSUFIX,
ACTNUM,
BLOCKED,
INACTIVE,
CLOSED,
CURRBAL)
VALUES (P_ACTTIT,
P_BRANCD,
P_CUSTID,
P_ACSUFIX,
P_ACTNUM,
P_BLOCKED,
P_INACTIVE,
P_CLOSED,
P_CURRBAL
);
END P1