DB2 Store procedure error in VB.NET

[sudevsu]

How to check calling a stored procedure from SQL Client?

Your help is much appreciated. Thanks Happy Coding!

[sudevsu]

I removed from Stored procedure. It inserts the data into table but sill throws exception. But if I remove from my call in VB.NET code, it says no such parameter found and so no insertion of data.

Your help is much appreciated. Thanks Happy Coding!

[GuyThiebaut]

You will need something like Toad [^]then call the stored procedure from there. I always test stored procedures form a client before running them from .Net.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[GuyThiebaut]

Instead of this:

Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(@PrmCICID,@PrmCIFN,@PrmCILN,@PrmCIADDR,@PrmCICITY,@PrmCISTATE,@PrmCIZIP,@PrmCIPHONE,@PrmCIEMAIL,@PrmCICLDATE)", Cn)

Try this:

Dim cm As New iDB2Command("CALL DEVSUSH.SP_CUSTINFO(?,?,?,?,?,?,?,?,?,?)", Cn)

and read this[^] [edit] ? added

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[sudevsu]

Ok Thank you. I did that now, it is perfectly ok there. But from code it goes into catch and throws same error no matter what. :(

Your help is much appreciated. Thanks Happy Coding!

[sudevsu]

I tried the one which you said and Now this is throwing

SQL0440 Routine QZ9AFC1C3BBACE2001 in not found with specified parameters.

Your help is much appreciated. Thanks Happy Coding!

[GuyThiebaut]

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

[sudevsu]

with ? I get this error

SQL0104 Token PRMCICID was not valid. Valid tokens: ) ,.

Your help is much appreciated. Thanks Happy Coding!

[GuyThiebaut]

Read this[^] article - I think it should help.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[sudevsu]

Thanks Guy. I wish this should resolve ASAP. It is taking my whole lot of time. :doh:

Your help is much appreciated. Thanks Happy Coding!

[Jorgen Andersson]

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 Try

Should 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

[Mycroft Holmes]

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

[GuyThiebaut]

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

[sudevsu]

Sure. But I didn't fix this yet.

Your help is much appreciated. Thanks Happy Coding!