GetErrors is not returning all rows in error

[Guest]

I am using .NET 2.0 and writing code using C#.

I have a datatable that is being filled with data and it has a primary key,
which is a single column ID. There are cases where there can be duplicate
records inserted into this table. I am trying to capture these errors by
setting EnforceConstraints = false and populating the table.

When I set EnforceConstraints = true after populating the table I catch the
ConstraintException and call GetErrors on the table to retrieve the rows in
error.
However, when there is more than one duplicate row in the table it only
seems to provide me with the first set of rows that caused it to fail not all
of them like I would expect and have seen in other cases like Null contraint
violations.'

Does anybody know if this is supposed to be the behavior or am I missing
something?

 

[Cowboy \(Gregory A. Beamer\)]

Put the data in a temp table first (this can be a true temp tabl or an
inport table). Then, you can create a temp table with the following type of
statement:

SELECT ColumnID, COUNT(*) As ColumnIDCount
INTO #TempTable
FROM ImportTable

You then migrate and delete any records that are unique (count(*) = 1).

INSERT INTO MainTable ({column list here})
SELECT t1.* FROM ImportTable t1
JOIN #Temptable t2
ON t1.ColumnID = t2.ColumnID

DELETE FROM ... (you should have this one down)

Next, you can loop through the rest and only take first instance. Saving off
the one's you have already done to avoid dupes. There is probably a SQL way
to do this, but I am working on 3 hours of sleep right now.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Guest]

But my problem is with an ADO.NET datatable and has nothing to do with SQL or
the database.

 

[Guest]

Oh, I think I understand why you responded the way you did.
I am not "Fill"ing the table from a datasource but populating it
programmatically.
I can definitely write some logic to check before inserting into the
datatable but I thought that the way that I'm currently doing it is better
and more efficient.