473,385 Members | 1,727 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Unique key constraint in ADO.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?

Jan 4 '06 #1
3 10046
|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
"Bruce Wood" <br*******@canada.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
|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?
|
Jan 4 '06 #2
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.

Jan 5 '06 #3
| 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
"Bruce Wood" <br*******@canada.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...
| 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.
|
Jan 5 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: KULJEET | last post by:
foreign key also refer to unique constraint. (GREAT...) 1.then table that containt unique constraint act as master table????? 2.IS unique constraint will replace with primary key?? 3.Is unique...
26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
5
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
4
by: Dave | last post by:
Can you create a unique constraint on multiple columns, or does it have to be implemented as a unique index? If possible can someone please post some sample code? Thanks,
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
5
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a...
7
by: Brian Keating | last post by:
Hi there, Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...? i.e....
10
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
2
by: rorajoey | last post by:
Violation of UNIQUE KEY constraint 'IX_surveyQuestions'. Cannot insert duplicate key in object 'dbo.surveyQuestions'. This might seem like a simple matter of trying to insert a row with ID=20 when...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.