Insert command not working in .net to DB2 connection

Question

I have a legacy application written in VB.NET, it uses an ODBC connection to write to an IBM DB2 database.

It is not working.

I've tried to see if there is a space in the values, if they are not from the same type, both are Strings, the values are correct but I think there is a problem in Command.ExecuteNonQuery() which is returning zero and I am getting the message that there was insertion to the database which is not the case.

h is always ZERO, no exceptions are thrown, just no update. Any ideas?

Private Sub Merge(ByVal connectionString As String, ByVal CaseNo As String, ByVal CancelledSurgeryId As String, ByVal ActiveSurgeryId As String, ByVal Cancelled As String, ByVal Active As String, ByVal location As String)

    Using connection As OdbcConnection = New OdbcConnection(connectionString)

        Dim Command As New OdbcCommand()
        Dim transaction As OdbcTransaction
        Command.Connection = connection

        Dim a, b, c, d, e, f, g, h As Integer
        Try
            connection.Open()
            transaction = connection.BeginTransaction()
            Command.Connection = connection
            Command.Transaction = transaction

            Command.CommandText = "Update QS36F.ORPATSURG SET REQSTAT = 'F' WHERE CASENO = ? AND SURGERYID = ?"
            Command.Parameters.Add("@CASENO", Odbc.OdbcType.VarChar).Value = CaseNo
            Command.Parameters.Add("@SURGERID", Odbc.OdbcType.VarChar).Value = CancelledSurgeryId
            a = Command.ExecuteNonQuery()

            Command.CommandText = "delete FROM QS36F.orpctrx WHERE orderno = ? AND CASENO = ? " & _
                        "AND oper IN (SELECT oper FROM QS36F.orpctrx WHERE caseno = ? AND orderno = ?)"

            Command.Parameters.Add("@orderno", Odbc.OdbcType.VarChar).Value = CancelledSurgeryId
            Command.Parameters.Add("@CASENO", Odbc.OdbcType.VarChar).Value = CaseNo
            Command.Parameters.Add("@caseno", Odbc.OdbcType.VarChar).Value = CaseNo
            Command.Parameters.Add("@orderno", Odbc.OdbcType.VarChar).Value = ActiveSurgeryId
            b = Command.ExecuteNonQuery()

            Command.CommandText = "DELETE FROM QS36F.ORPCTRXM WHERE CASENO = ? AND ORDERNO  = ?"
            Command.Parameters.Add("@caseno", Odbc.OdbcType.VarChar).Value = CaseNo
            Command.Parameters.Add("@orderno", Odbc.OdbcType.VarChar).Value = ActiveSurgeryId
            d = Command.ExecuteNonQuery()

                Command.CommandText = "update libor.invtrx set referno = ? , pono  = ? where referno = ? and pono  = ?"
                Command.Parameters.Add("referno", Odbc.OdbcType.VarChar).Value = Active
                Command.Parameters.Add("pono", Odbc.OdbcType.VarChar).Value = ActiveSurgeryId
                Command.Parameters.Add("referno", Odbc.OdbcType.VarChar).Value = Cancelled
                Command.Parameters.Add("pono", Odbc.OdbcType.VarChar).Value = CancelledSurgeryId
                h = Command.ExecuteNonQuery()
       
                transaction.Commit()
                MessageBox.Show("Records successfully written to database.", "Success")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Try
                transaction.Rollback()
            Catch
            End Try
        End Try
    End Using
End Sub

This is some sample data for the last one.

|ITEMID|TRXDATE|TRXTYPE|REFERNO        |PONO       |CASENO  |
|------|-------|-------|---------------|-----------|--------|
|00244 |220609 |13     |31215380077    |196474     |01631892|

The thing I posted in my comments is present on all query, yet only this one is not working? And it is not getting updated, any workarounds?

---

Subsequently to the initial question, another experienced person gave it a look: if I run the query, it works and the database is filled with the correct values. Also searching using breakpoints is giving

Overload resolution failed because no accessible 'Item' accepts this number of arguments

Answer 1

The solution was clearing the parameters between queries using Command.Parameters.Clear().