Unique key constraint in ADO.NET

[Bruce Wood]

I know that this isn't a C# question, but I can't find a newsgroup
specifically devoted to ADO.NET, other than a moribund one that deals
with ADO in general.

This problem is driving me to distraction. Maybe someone else has run
across the same thing.

I'm using ODBC to build a joined table in ADO.NET. The table has a
large number of columns, but only one key column, which has the usual
TABLE1.STOCK_CODE = TABLE2.STOCK_CODE "where" clause to do the join.
This all works fine.

In fact, we have two dozen such table pairs, and it works great for all
but one of them.

For that one pair of tables, I get the data back fine, but when I
attempt to set the primary key column:

table.PrimaryKey = keyColumns;

I get an error:

System.ArgumentException: These columns don't currently have unique
values.

Before you jump on that, yes, I know exactly what that error means: the
joined table (somehow) has two identical stock codes in it.

The only problem is that it doesn't.

I've extracted data from the background tables, sorted it, and ran
comparisons. No duplicates.

I've written code that runs through the ADO.NET table I have in memory
comparing keys. No duplicates.

I've written the keys from the ADO.NET table I have in memory out to a
text file, which I sorted and checked. No duplicates.

Does anyone know how ADO.NET determines uniqueness for key values? Does
anyone know how to get ADO.NET to give up which rows it thinks are
colliding when trying to set primary keys? There are 7482 rows in the
joined table, and every key appears to be unique. Can anyone suggest
something I haven't tried yet to figure out why ADO.NET refuses to set
this column as a primary key column?

 

[Jay B. Harlow [MVP - Outlook]]

|I know that this isn't a C# question, but I can't find a newsgroup
| specifically devoted to ADO.NET, other than a moribund one that deals
| with ADO in general.
Have you tried: microsoft.public.dotnet.framework.adonet?

| Before you jump on that, yes, I know exactly what that error means: the
| joined table (somehow) has two identical stock codes in it.
It sure sounds like the joined table has two identical stock codes in it!

What's the type of the column in question? Is it numeric in the Datatable,
but text in the database & leading zeros are being dropped?

To find them I would do something like:

foreach(DataRow row in table.Rows)
{
DataRow[] rows = table.Select("id = " + row["id"], null);
if (rows.Length > 1)
{
Debug.WriteLine(rows.Length, row["id"].ToString());
}
}


Alternatively you could set the constraint before reading the data. Then
examine the exception that is thrown. The exception will identify what the
duplicate value is. If you set the constraint after filling the datatable,
then you don't get what the duplicate value was...


--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


|I know that this isn't a C# question, but I can't find a newsgroup
| specifically devoted to ADO.NET, other than a moribund one that deals
| with ADO in general.
|
| This problem is driving me to distraction. Maybe someone else has run
| across the same thing.
|
| I'm using ODBC to build a joined table in ADO.NET. The table has a
| large number of columns, but only one key column, which has the usual
| TABLE1.STOCK_CODE = TABLE2.STOCK_CODE "where" clause to do the join.
| This all works fine.
|
| In fact, we have two dozen such table pairs, and it works great for all
| but one of them.
|
| For that one pair of tables, I get the data back fine, but when I
| attempt to set the primary key column:
|
| table.PrimaryKey = keyColumns;
|
| I get an error:
|
| System.ArgumentException: These columns don't currently have unique
| values.
|
| Before you jump on that, yes, I know exactly what that error means: the
| joined table (somehow) has two identical stock codes in it.
|
| The only problem is that it doesn't.
|
| I've extracted data from the background tables, sorted it, and ran
| comparisons. No duplicates.
|
| I've written code that runs through the ADO.NET table I have in memory
| comparing keys. No duplicates.
|
| I've written the keys from the ADO.NET table I have in memory out to a
| text file, which I sorted and checked. No duplicates.
|
| Does anyone know how ADO.NET determines uniqueness for key values? Does
| anyone know how to get ADO.NET to give up which rows it thinks are
| colliding when trying to set primary keys? There are 7482 rows in the
| joined table, and every key appears to be unique. Can anyone suggest
| something I haven't tried yet to figure out why ADO.NET refuses to set
| this column as a primary key column?
|

 

[Bruce Wood]

Thanks very much.

I just discovered that this is happening because there are four keys
that differ only by case, and by default ADO.NET is case INsensitive on
key comparisons. So, I'm going to sort that out.

However, I like your way of finding the offending keys, so I'm going to
add that to my code, too.

 

[Jay B. Harlow [MVP - Outlook]]

| I just discovered that this is happening because there are four keys
| that differ only by case, and by default ADO.NET is case INsensitive on
| key comparisons. So, I'm going to sort that out.
Ah! Case sensitivity...

In case you didn't find it, you can use either DataSet.CaseSensitive or
DataTable.CaseSensitive to control the case sensitivity of your DataTable.
DataSet.Local & DataTable.Local are closely related to the CaseSensitive
property.

--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


| Thanks very much.
|
| I just discovered that this is happening because there are four keys
| that differ only by case, and by default ADO.NET is case INsensitive on
| key comparisons. So, I'm going to sort that out.
|
| However, I like your way of finding the offending keys, so I'm going to
| add that to my code, too.
|