Constraint error on fill of dataset

[Flomo Togba Kwele]

I've defined a dataset containing 3 tables via the DataSource
Configuration Wizard.

In the UserControl.Load event, I place:

Try
clientAdapter.Fill(DsClient.Client)
personAdapter.Fill(DsClient.Person)
addressAdapter.Fill(DsClient.Address)
Catch
PrintAllErrs(DsClient)
End Try

At the first fill (client), I get the following error:

A first chance exception of type 'System.Data.ConstraintException'
occurred in System.Data.dll

In the PrintAllErrs routine, I get a reference to each table's errors
(GetErrors) and list each column in error along with its error:

If table.HasErrors Then
' Get an array of all rows with errors.
rowsInError = table.GetErrors()
' Print the error of each column in each row.
For i = 0 To rowsInError.GetUpperBound(0)
For Each column In table.Columns
Console.WriteLine(column.ColumnName, _
rowsInError(i).GetColumnError(column))
Next

It writes out each column in the first table, with an empty string for
the column error for each column.

If I precede the Try above with ds.EnforceConstraints = False,
everything looks like it's working OK.

What's wrong here?

TIA

 

[WenYuan Wang]

Hi Flomo,
According to your description, you noticed that
clientAdapter.Fill(DsClient.Client) will throw an exception
(System.Data.ConstraintException), but it will work fine if you have set
ds.EnforceConstraints = False. You want to know the root cause of this
issue and how to resolve it. If I misunderstand anything here, please don't
hesitate to correct me.

EnforceConstraints property indicates whether constraint rules are followed
when attempting any update operation. If you set this property to false,
the DataAdatper will not check the constraint (Foreign Key Constraint). For
this reason, DataAdatper will now throw any Constraint Exception.

I have also read your other posts in newsgroups such as "BindingSource,
DataSet and multiple tables not syncing" and "Concurreny violation on
delete in dataset". It seems like that there is a foreign key constraint
between Client table and Person table. You should have to fill Person table
before filling the Client table. Base on my experience, if you change the
order of DataAdapters, this issue will be resolved.

Try
addressAdapter.Fill(DsClient.Address)
personAdapter.Fill(DsClient.Person)
clientAdapter.Fill(DsClient.Client)
Catch
PrintAllErrs(DsClient)
End Try

Please try the method above and let me know if this is what you need.

Hope this will help.
Best regards,
Wen Yuan

 

[Flomo Togba Kwele]

Thanks, Wen Yuan.

When I rearranged the order of fills, it got the same error.

Also, the error routine showed there were two rows in error (the exact
number in the table).

I rewrote the error routine to display only those columns which had
errors:

If table.HasErrors Then
' Get an array of all rows with errors.
rowsInError = table.GetErrors()
' Print the error of each column in each row.
For i = 0 To rowsInError.GetUpperBound(0)
For Each column In table.Columns
If rowsInError(i).GetColumnError(column) <>
String.Empty Then
Console.WriteLine(column.ColumnName, _
rowsInError(i).GetColumnError(column))
End If
Next
' Clear the row errors
rowsInError(i).ClearErrors()
Next i
End If

The Output window had only:
A first chance exception of type 'System.Data.ConstraintException'
occurred in System.Data.dll

with no columns showing.

Flomo

 

[WenYuan Wang]

Hi Flomo,
I'm sorry for the late reply.

I think "dr.RowError" will show more information for you. In general,the
reason why the "DataAdapter.fill" method will throw ConstraintException is
the data in table doesn't accord with constrain. We suggest you can check
the table(Client) and output the RowError property for each Row(Error).

For example:
If table.HasErrors Then
' Get an array of all rows with errors.
rowsInError = table.GetErrors()
' Print the error of each column in each row.
For i = 0 To rowsInError.GetUpperBound(0)
For Each column In table.Columns

'output the RowError property for each Row(Error).
Console.WriteLine(rowsInError(i). RowError)

End If
Next
' Clear the row errors
rowsInError(i).ClearErrors()
Next i
End If

Hope this will hope.
Best Regards,
Wen Yuan