Hello experts,
I have a Stored procedure which inserts the data. AS soon as it is inserted, is there a way I can get the inserted data from DB to a Dataset. I know how to get normal data into Dataset from DB. But I want Dataset which consist of inserted data.
Here is my call to Stored procedure which should return auto generated column value which is a primary key. But It shows empty in
Dim strVID = prm.Value
Dim cmdText As String = "CALL LIBR.SP_VEHINFO(@PRMVYEAR,@PRMVVMAKE,@PRMVVMODEL,@PRMVIN,@PRMINSTMI,@PRMREMOMI,@PRMTOTMI,@PRMINSDATE,@PRMPURDATE,@PRMTIRETYP,@PRMCREDIT,@PRMRMCMP,@PRVIVID)"
Dim cm As New iDB2Command(cmdText, cn)
Dim dtinstalled As Date = Convert.ToDateTime(dtInsDate)
Dim dtpurchased As Date = Convert.ToDateTime(dtPurDate)
cm.Parameters.Add("@PRMVYEAR", iDB2DbType.iDB2Numeric).Value = strVyear
cm.Parameters.Add("@PRMVVMAKE", iDB2DbType.iDB2VarChar).Value = strVmake
cm.Parameters.Add("@PRMVVMODEL", iDB2DbType.iDB2VarChar).Value = strVModel
cm.Parameters.Add("@PRMVIN", iDB2DbType.iDB2VarChar).Value = strVin
cm.Parameters.Add("@PRMINSTMI", iDB2DbType.iDB2Numeric).Value = Convert.ToDouble(strInstmiles)
cm.Parameters.Add("@PRMREMOMI", iDB2DbType.iDB2Numeric).Value = Convert.ToDouble(strRemniles)
cm.Parameters.Add("@PRMRTOTMI", iDB2DbType.iDB2Numeric).Value = Convert.ToDouble(strTotMiles)
cm.Parameters.Add("@PRMINSDATE", iDB2DbType.iDB2Date).Value = dtinstalled
cm.Parameters.Add("@PRMPURDATE", iDB2DbType.iDB2Date).Value = dtpurchased
cm.Parameters.Add("@PRMTIRETYP", iDB2DbType.iDB2VarChar).Value = strTireType
cm.Parameters.Add("@PRMCREDIT", iDB2DbType.iDB2VarChar).Value = strCredit
cm.Parameters.Add("@PRMRMCMP", iDB2DbType.iDB2BigInt).Value = strRMCmp
Dim prm As iDB2Parameter = cm.Parameters.Add("@PRVIVID", iDB2DbType.iDB2Integer)
prm.Direction = ParameterDirection.Output
Dim strVID = prm.Value
My Stored procedure in DB is
CREATE PROCEDURE LIBR.SP_CUSTINFO (
IN PRMCIFN VARCHAR(30) ,
IN PRMCILN VARCHAR(30) ,
IN PRMCIADDR VARCHAR(90) ,
IN PRMCICITY VARCHAR(20) ,
IN PRMCISTATE VARCHAR(20) ,
IN PRMCIZIP NUMERIC(9, 0) ,
IN PRMCIPHONE NUMERIC(10, 0) ,
IN PRMCIEMAIL VARCHAR(30) ,
IN PRMCICLDATE DATE ,
IN PRMCMP INTEGER ,
OUT PRMCICID INTEGER )
LANGUAGE SQL
SPECIFIC LIBR.SP_CUSTINFO
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
P1 : BEGIN
SELECT CICID INTO PRMCICID
FROM FINAL TABLE
(
INSERT INTO LIBR. CLCUSINF VALUES ( PRMCIFN , PRMCILN , PRMCIADDR , PRMCICITY , PRMCISTATE , PRMCIZIP , PRMCIPHONE , PRMCIEMAIL , PRMCICLDATE , PRMCMP , DEFAULT )
) ;
END P1 ;
Now return value is showing empty always. However Data is inserted perfectly with in Table and column autogenerated is also working good. But why is it not showing when I return OUTPUT PARAMETER from the same.
Please let me know If anyone have got an idea