Constraint violated but no exception thrown

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
Tue Jan 04 18:08:16 CST 2005


"davisonm" <davisonm@discussions.microsoft.com> wrote in message
news:61C08D6E-34EA-4706-B1D8-B8DE9C23ED36@microsoft.com...
> 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

davisonm
Tue Jan 04 18:33:03 CST 2005

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" wrote:

>
> "davisonm" <davisonm@discussions.microsoft.com> wrote in message
> news:61C08D6E-34EA-4706-B1D8-B8DE9C23ED36@microsoft.com...
> > 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
>
>
>

Fredrik
Tue Jan 04 18:51:24 CST 2005


"davisonm" <davisonm@discussions.microsoft.com> wrote in message
news:58022B6C-1D21-45BD-B324-8146D0C8F3AF@microsoft.com...
> 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" wrote:
>
> > Truncated

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

/ Fredrik

davisonm
Tue Jan 04 21:37:02 CST 2005

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

"Fredrik Wahlgren" wrote:

>
> "davisonm" <davisonm@discussions.microsoft.com> wrote in message
> news:58022B6C-1D21-45BD-B324-8146D0C8F3AF@microsoft.com...
> > 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" wrote:
> >
> > > Truncated
>
> What other problems are there? Haven't you validated the data before you try
> to do an update?
>
> / Fredrik
>
>
>

Uwe
Wed Jan 05 02:57:15 CST 2005

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

"davisonm" <davisonm@discussions.microsoft.com> schrieb im Newsbeitrag
news:61C08D6E-34EA-4706-B1D8-B8DE9C23ED36@microsoft.com...
> 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
>

davisonm
Wed Jan 05 09:25:03 CST 2005

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



"Uwe Hafner" wrote:

> 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
>
> "davisonm" <davisonm@discussions.microsoft.com> schrieb im Newsbeitrag
> news:61C08D6E-34EA-4706-B1D8-B8DE9C23ED36@microsoft.com...
> > 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
Wed Jan 05 14:19:36 CST 2005


"davisonm" <davisonm@discussions.microsoft.com> wrote in message
news:C579AADE-F49D-4CD6-A421-A806D623CD64@microsoft.com...
> 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

davisonm
Thu Jan 06 12:01:02 CST 2005

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

"davisonm" wrote:

> 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
>