ConstraintException Not Being Caught

Question

First time poster. I've enjoyed many years of help here. Thanks everyone.

I have a situation that looks like it should not be able to happen.

Using VB.NET in VS2017 Community, I am getting a System.Data.ConstraintException in a Try block where I have specifically trapped for that exact exception.

Here is what the message looks like:

System.Data.ConstraintException: 'Column 'PAIR1, PAIR2, PAIR3' is constrained to be unique. Value 'CHATBTC, ETHBTC, CHATETH' is already present.'

https://www.dropbox.com/s/d91rgtwsjwioqhm/SO_error.jpg?dl=0

As you can tell by the logic, I am counting on the exception to fire so that I can build a table of unique rows and add to my duplicate rows value. Checking for duplicates before the ADD takes a lot of time as the table grows in size so this approach is the fastest.

It does not happen every time, only about 30%. My application is not far enough along to run in production yet so everything I see is while debugging.

My code is here:

  tblTriArbPairs.PrimaryKey = New DataColumn() {tblTriArbPairs.Columns("PAIR1"), tblTriArbPairs.Columns("PAIR2"), tblTriArbPairs.Columns("PAIR3")}

  Try
      tblTriArbPairs.Rows.Add(
    Pairs(0), Pairs(1), Pairs(2),
    idxPair0, idxPair1, idxPair2,
    result.TD1, result.TD2, result.TD3,
    CoinOnly(Pairs(0)), CurrOnly(Pairs(0)),
    CoinOnly(Pairs(1)), CurrOnly(Pairs(1)),
    CoinOnly(Pairs(2)), CurrOnly(Pairs(2)),
    FindLoopCoin(CoinOnly(Pairs(0)), CurrOnly(Pairs(0)), CoinOnly(Pairs(1)), CurrOnly(Pairs(1)), CoinOnly(Pairs(2)), CurrOnly(Pairs(2))),
    GetSymbolLIQ(Pairs(0)), GetSymbolLIQ(Pairs(1)), GetSymbolLIQ(Pairs(2))
    )
      RowsAdded += 1
  Catch ex As System.Data.ConstraintException
      DupRows += 1
  Catch ex As Exception
  Finally
  End Try

When table is populated I end up with 3480 rows added and 2640 duplicates. There is no consistency as to when the error occurs. Sometimes right away, other times almost at the end.

I have looked all over and not found anything that addresses the ConstraintException not being caught. Other exceptions, yes.

Any help is very much appreciated. Hopefully I have posted a good question. :)

Answer 1

I have read that catching Exceptions is a rather cumbersome way to direct program flow and it is better to prevent the exception.

I think this is a Datatable you are dealing with so the following bit of Linq might do the trick. According to the docs "The enumeration of source is stopped as soon as the result can be determined."

I just used my sample DB to test.

    Dim CoffeeName As String = "Black Tiger"
    Dim CoffeeType = "Decaf"

    Dim dup As Boolean = dt.AsEnumerable().Any(Function(Row) CoffeeName = Row.Field(Of String)("Name") And CoffeeType = Row.Field(Of String)("Type"))
    MessageBox.Show(dup.ToString)

EDIT Code written with your variables and 3 fields.

    Dim dup As Boolean = tblTriArbPairs.AsEnumerable().Any(Function(Row) Pairs(0) = Row.Field(Of String)("Pair1") _
        And Pairs(1) = Row.Field(Of String)("Pair2") And Pairs(2) = Row.Field(Of String)("Pair3"))
    If dup Then
        DupRows += 1
        MessageBox.Show("Sorry, duplicate")
        Exit Sub
    End If
    'Add the row

Answer 2

I did not ever solve the issue about why the exception was being thrown although I explicitly trapped for it. I did heed the suggestions here for checking if duplicates exist before attempting to add them to my datatable. Here is what I came up with and it seems to work just fine.

Dim iRow As DataRow() = tblTriArbPairs.Select("PAIR1 = '" & Pairs(0) & "' AND PAIR2 = '" & Pairs(1) & "' AND PAIR3 = '" & Pairs(2) & "'")
If iRow.Count = 0 Then
    tblTriArbPairs.Rows.Add(
      Pairs(0), Pairs(1), Pairs(2),
      idxPair0, idxPair1, idxPair2,
      result.TD1, result.TD2, result.TD3,
      CoinOnly(Pairs(0)), CurrOnly(Pairs(0)),
      CoinOnly(Pairs(1)), CurrOnly(Pairs(1)),
      CoinOnly(Pairs(2)), CurrOnly(Pairs(2)),
      FindLoopCoin(CoinOnly(Pairs(0)), CurrOnly(Pairs(0)), CoinOnly(Pairs(1)), CurrOnly(Pairs(1)), CoinOnly(Pairs(2)), CurrOnly(Pairs(2))),
      GetSymbolLIQ(Pairs(0)), GetSymbolLIQ(Pairs(1)), GetSymbolLIQ(Pairs(2))
      )
    RowsAdded += 1
Else
    DupRows += 1
End If

Thanks again to everyone who helped. I made it through my first SO question! Yea!