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. :)

My only complaint with your question is that you posted code with a fat wad of leading whitespace in your code snippet. You should use the `Alt` key in VS to enable you to select and arbitrary rectangle of code and thus avoid selecting the leading whitespace in the first place. Failing that, you can select the code in the SO editor and click the CODE button repeatedly to remove leading whitespace. — jmcilhinney, Mar 18 '19 at 06:02
That does seem very odd. I thought maybe you have configured the IDE to always break on that exception type but, in that case, it should "Exception Thrown" rather than "Exception Unhandled" on the Exception Assistant title bar. What does the stack trace look like? Try deleting the bin and obj folders and run it again, just to make sure that you are running the most recent code. — jmcilhinney, Mar 18 '19 at 06:04
@jmcilhinney Thank you for the editing suggestions. I did not know either of those until now. I always delete my bin/Debug folder and yes, it occurs even still. Stack trace shows nothing unusual. Just the execution path it is supposed to have. — KenH, Mar 18 '19 at 06:35

Mary · Accepted Answer · 2019-03-18T17:51:00.227

0

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

edited Mar 18 '19 at 17:51

answered Mar 18 '19 at 05:57

Mary

14,926
3
18
27

thank you. I have a 3 column primary key so I am trying to adapt your response to this fact. I can go searching for an example. – KenH Mar 18 '19 at 06:40
@KenH A `Select` will have to scan the entire table each time but an `Any` will stop as soon as it finds a `True` . So, in theory, it should be a bit faster. – Mary Mar 18 '19 at 17:56
Mary, I tried the "ANY" function and it took 40 secs to process my table. Using the "SELECT" approach takes under 1 second. So at this point, I will stick with the "SELECT" method. I will still mark your response as the answer because it pointed me in the right direction. Thank you! – KenH Mar 18 '19 at 23:14

score 0 · Answer 2 · answered Mar 18 '19 at 08:23

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!

ConstraintException Not Being Caught

2 Answers2