Constraint violated but no exception thrown

[Guest]

I have a VB Windows form that calls the DataAdapter.Update method, passing a
DataTable of one row with RowState = 'Added'. This is the stripped-down code:

Try
Dim Count As Integer = MySqlAdapter.Update(MyDataTable.GetChanges)
Catch ex As System.Data.SqlClient.SqlException
MessageBox.Show("Update failed: " & ex.Message)
End Try

The underlying table, in SQL Server 2000, has a unique constraint defined on
a non-PK data column. When that column has a DataTable value which violates
the constraint, the DB refuses to add the row, Update returns zero for a
rowcount, but there is no exception thrown.

Why not?

Client: Windows XP SP1, Visual Studio 2003 version 7.1.3088, .NET Framework
1.1 version 1.1.4322 SP1, MDAC 2.8 RTM
Server: Windows NT 5.0 (Build 2195: Service Pack 2), SQL Server 2000 version
8.00.760

 

[Fredrik Wahlgren]

I have a VB Windows form that calls the DataAdapter.Update method, passing a
DataTable of one row with RowState = 'Added'. This is the stripped-down code:

Try
Dim Count As Integer = MySqlAdapter.Update(MyDataTable.GetChanges)
Catch ex As System.Data.SqlClient.SqlException
MessageBox.Show("Update failed: " & ex.Message)
End Try

The underlying table, in SQL Server 2000, has a unique constraint defined on
a non-PK data column. When that column has a DataTable value which violates
the constraint, the DB refuses to add the row, Update returns zero for a
rowcount, but there is no exception thrown.

Why not?

Client: Windows XP SP1, Visual Studio 2003 version 7.1.3088, .NET Framework
1.1 version 1.1.4322 SP1, MDAC 2.8 RTM
Server: Windows NT 5.0 (Build 2195: Service Pack 2), SQL Server 2000 version
8.00.760

Does it matter since you get a 0 value back?

/ Fredrik

 

[Guest]

Sure, 'cuz there's other problems that could cause Update to return zero
rows. I have several potential workarounds for this issue, but frankly, I
don't want to use any of them. I just want to catch the exception that I
know the DB is throwing. It's getting lost or ignored somewhere, and I want
to know where and why.

 

[Fredrik Wahlgren]

Sure, 'cuz there's other problems that could cause Update to return zero
rows. I have several potential workarounds for this issue, but frankly, I
don't want to use any of them. I just want to catch the exception that I
know the DB is throwing. It's getting lost or ignored somewhere, and I want
to know where and why.

What other problems are there? Haven't you validated the data before you try
to do an update?

/ Fredrik

 

[Guest]

Thanks for taking the time to answer, but... Yes, I have data validation in
the form, but that's really beside the point. SQL Books Online lists
hundreds of errors that the DB can return. If the form is not catching this
one, then how many others will it fail to catch and report on? Silently
ignoring a DB exception is just not acceptable. Nor is treating all possible
exceptions identically, with a generic 'Something bad happened' message. I
really need to know why the exception is not being seen by the VB code in the
form. BTW, if I perform the identical operation, but with a data value that
is not already in the DB, it works properly.

Mark

 

[Uwe Hafner]

Hi,

Haven't looked at this issue for a while now but doesn't the Adapter throw a
DBConcurrencyException rather than a SqlException in this case?
Are you sure there is no other exception thrown?

Do you handle OnRowUpdated event? how?
Do you maybe set ContinueUpdateOnError to true?

hth
Uwe

 

[Guest]

The online doc does say that DBConcurrencyException is thrown whenever the
result is zero rows. My 'real' try-catch block is more like this:

Try
Dim MyChanges As DataTable = MyDataTable.GetChanges
Dim Count As Integer = MySqlAdapter.Update(MyChanges)
MyDataSet.AcceptChanges()
MyDataSet.Merge(MyChanges, False, MissingSchemaAction.Error)
MyDataSet.AcceptChanges()
Catch dbcx As DBConcurrencyException
Catch ex As System.Data.SqlClient.SqlException
Catch ex As Exception
End Try

My understanding is that 'ex as Exception' will catch everything not
previously caught, but, in fact, none of the catchers catch. Do you see a
problem with those catch statements, or the ordering?

As for ContinueUpdateOnError , I have always left that in the default/false
state, except for flipping it for a test to confirm that it made no
difference, then resetting it.

I do have a OnRowUpdated event to manage the merge of returned PK identity
values. The event code is:

If e.StatementType = StatementType.Insert Then
e.Status = UpdateStatus.SkipCurrentRow
End If

And it fires (I had checked that before), and, whuddayaknow, the error is
visible there (I had not checked that before):

"Violation of UNIQUE KEY constraint 'akChemicalCasNumber'.

So, is this where the exception is getting consumed? As well as the place
where I must handle it? If so, this is manageable, although it's
inconvenient to catch the same exceptions in two places. Everything is so
deeply intertwingled in .NET, sometimes it makes me crazy.

I'd appreciate it if you would confirm your diagnosis, and provide any other
comments you have.

Thanks,
Mark

 

[Fredrik Wahlgren]

The online doc does say that DBConcurrencyException is thrown whenever the
result is zero rows. My 'real' try-catch block is more like this:

Try
Dim MyChanges As DataTable = MyDataTable.GetChanges
Dim Count As Integer = MySqlAdapter.Update(MyChanges)
MyDataSet.AcceptChanges()
MyDataSet.Merge(MyChanges, False, MissingSchemaAction.Error)
MyDataSet.AcceptChanges()
Catch dbcx As DBConcurrencyException
Catch ex As System.Data.SqlClient.SqlException
Catch ex As Exception
End Try

This makes sense. Attempting to update a table which violates a primary key
won't cause an exception. Consequently, you need to alter your code such
that it will catch exceptions that you are interested in.

/Fredrik

 

[Guest]

Just to wrap up the thread I started, and describe the solution...

I had a RowUpdated event handler on my DataAdapter with just three lines of
code:

If e.StatementType = StatementType.Insert Then
e.Status = UpdateStatus.SkipCurrentRow
End If

Problem was that e.Status has multiple functions. When an error occurs it
contains the enumeration value ErrorsOccurred. That's how you know that
Something Bad happened (SB error). But, to have the error throw an
exception, it's necessary to _exit_ the handler with e.Status set to
ErrorsOccurred. When I unconditionally changed e.Status to SkipCurrentRow
(at least for all inserts), that "turned off" all exceptions thrown by the
call to DataAdapter.Update. So now the handler is:

If (e.StatementType = StatementType.Insert AndAlso e.Status =
UpdateStatus.Continue) Then
e.Status = UpdateStatus.SkipCurrentRow
End If

Which works just right! And so it goes...