DB2 Store procedure error in VB.NET

sudevsu

This is how I am calling SP

Dim Cn As iDB2Connection = CreateConnection()
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

Your help is much appreciated. Thanks Happy Coding!

sudevsu

You mean just remove all @. Do I need to add ? or something instead of @?

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Have you tried calling the stored procedure from an SQL client? If you have did, was the call successful? I noticed that the stored procedure declaration does not contain @ in front of the parameter names after the procedure name, is this correct?

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

― Christopher Hitchens

sudevsu

If I remove @ in front of parameters, it throw an error saying no such parameter. If I keep it @ then it just inserts. I am using ibm series.

Your help is much appreciated. Thanks Happy Coding!

sudevsu

How to check calling a stored procedure from SQL Client?

Your help is much appreciated. Thanks Happy Coding!

GuyThiebaut

Judging by this:DB2 stored procedure examples[^] you don't need any @s so try removing them from the stored procedure and your call. Disclaimer - I have never used DB2.

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

― Christopher Hitchens

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!