>We're using MissingSchemaAction.AddWithKey and suddenly had Rows.Find blow up because the value passed didn't match the table PK constraint columns.
>
>I figured out what was happening because I realized that we just changed some of our indexes and it turned out we have a new clustered key that matches the columns ADO.NET thinks comprises a composite "PK" constraint. (This is one of those times where our primary key is not clustered -- trying to optimize our indexes).
>
>I've searched high and low for this behavior, and any work arounds... other than to just add code that sets the DataTable.PrimaryKeys to what we need at any given moment... or settings I've somehow missed.
>
>Seems strange, though, that ADO.NET would "mistake" the clustered key for the primary one... especially where it's using it to set a "primary" constraint?
>
>Am I missing something here? Maybe the answer's "out there" and I'm just using bad search text?
>
>Thanks for any help,
>---J

Jason,

This is actually a bug in the SQL ADO.NET provider. ADO.NET relies on the GetSchemaTable method to discover keys and constraints. If you want to see where the confusion comes from, just look at the help for this method :)

P.S. Shame on you for clustering something other than your PK! Time to move those clustered keys to their own tables. Remember rule #3 - Eliminate columns not dependent on the key (and move them to a separate table).

Either that or shoot the DBA.

JUST JOKING!

- Keith