DB2 Store procedure error in VB.NET
-
sudevsu wrote:
I tried the one which you said and Now this is throwing
The one with the "?" question marks for parameters?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
with ? I get this error
SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.
Your help is much appreciated. Thanks Happy Coding!
-
-
Not in the procedure just remove them like this:
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE )
VALUES ( PRMCICID , PRMCIFN , PRMCILN , PRMCIADDR , PRMCICITY , PRMCISTATE , PRMCIZIP , PRMCIPHONE , PRMCIEMAIL , PRMCICLDATE ) ;But your call from vb.net to the database is a prepared statement so that should be changed to:
Try Dim cm As New iDB2Command("CALL DEVSUSH.SP\_CUSTINFO(:PrmCICID,:PrmCIFN,:PrmCILN,:PrmCIADDR,:PrmCICITY,:PrmCISTATE,:PrmCIZIP,:PrmCIPHONE,:PrmCIEMAIL,:PrmCICLDATE)", Cn) strCIPHONE = strCIPHONE.Replace("-", "") cm.Parameters.Add("PRMCICID", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCICID) cm.Parameters.Add("PrmCIFN", iDB2DbType.iDB2VarChar).Value = strCIFN cm.Parameters.Add("PrmCILN", iDB2DbType.iDB2VarChar).Value = strCILN cm.Parameters.Add("PrmCIADDR", iDB2DbType.iDB2VarChar).Value = strCIADDR cm.Parameters.Add("PrmCICITY", iDB2DbType.iDB2VarChar).Value = strCICITY cm.Parameters.Add("PrmCISTATE", iDB2DbType.iDB2VarChar).Value = strCISTATE cm.Parameters.Add("PrmCIZIP", iDB2DbType.iDB2Numeric).Value = Convert.ToInt32(strCIZIP) cm.Parameters.Add("PrmCIPHONE", iDB2DbType.iDB2Numeric).Value = Convert.ToInt64(strCIPHONE) cm.Parameters.Add("PrmCIEMAIL", iDB2DbType.iDB2VarChar).Value = strCIEMAIL cm.Parameters.Add("PrmCICLDATE", iDB2DbType.iDB2Date).Value = strCICLDATE cm.ExecuteNonQuery() Catch ex As Exception LogError.LogErrorIntoTextFile(ex, "InsertCust") End TryShould note that the error message you're getting is not from DB2 but from VB.Net, stating that there is a mismatch in the parameters. Or rather that it can't find an overload of the procedure having the right parameters. The reason it works is that it tries to apply the parameters in the order they've been added like as if you had used
?as a marker. Like this:Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)
Wrong is evil and must be defeated. - Jeff Ello
-
I have a store procedure in my DB2 Database. It is just a simple insert statement. I have 10 columns in the table and each value is passed as a parameter to Store procedure and when I execute it throws an error
Overload resolution failed because no accessible 'Parameters' accepts this number of arguments.
My Store procedure is
CREATE PROCEDURE DEVSUSH.SP_CUSTINFO (
IN PRMCICID INTEGER ,
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 )
LANGUAGE SQL
SPECIFIC DEVSUSH.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
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE ) VALUES ( @PRMCICID , @PRMCIFN , @PRMCILN , @PRMCIADDR , @PRMCICITY , @PRMCISTATE , @PRMCIZIP , @PRMCIPHONE , @PRMCIEMAIL , @PRMCICLDATE ) ;
END P1 ;GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO SUSHDEV ;GRANT EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO VAIGROUP ;How to solve the error?
Your help is much appreciated. Thanks Happy Coding!
A thought, have you checked that there are no triggers spit attached to the table and the error is generated from the trigger instead of your procedure (this is the reason I loathe triggers spit)?
Never underestimate the power of human stupidity RAH
-
I have a store procedure in my DB2 Database. It is just a simple insert statement. I have 10 columns in the table and each value is passed as a parameter to Store procedure and when I execute it throws an error
Overload resolution failed because no accessible 'Parameters' accepts this number of arguments.
My Store procedure is
CREATE PROCEDURE DEVSUSH.SP_CUSTINFO (
IN PRMCICID INTEGER ,
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 )
LANGUAGE SQL
SPECIFIC DEVSUSH.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
INSERT INTO DEVSUSH . CLCUSINF ( CICID , CIFN , CILN , CIADDR , CICITY , CISTATE , CIZIP , CIPHONE , CIEMAIL , CICLDATE ) VALUES ( @PRMCICID , @PRMCIFN , @PRMCILN , @PRMCIADDR , @PRMCICITY , @PRMCISTATE , @PRMCIZIP , @PRMCIPHONE , @PRMCIEMAIL , @PRMCICLDATE ) ;
END P1 ;GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO SUSHDEV ;GRANT EXECUTE
ON SPECIFIC PROCEDURE DEVSUSH.SP_CUSTINFO
TO VAIGROUP ;How to solve the error?
Your help is much appreciated. Thanks Happy Coding!
Remember, when you fix this post back under your original post with how you fixed it ;) (this can be very useful to other people who come to this forum looking for a solution to the same issue you had)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Remember, when you fix this post back under your original post with how you fixed it ;) (this can be very useful to other people who come to this forum looking for a solution to the same issue you had)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens