Insert command not working in .net to DB2 connection-CodePudding

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

CodePudding user response：

If ExecuteNonQuery returns zero then that means that the operation was successful but no records were modified, which means that the database doesn't contain any records that match your WHERE clause. There is nothing at all wrong with the code you posted and the issue is purely related to the data, which you didn't show us and, apparently, didn't even look at.

CodePudding user response：

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