troubleshooting Question

DataRelation - These columns currently don't have unique values

Avatar of deleyd
deleydFlag for United States of America asked on
C#
2 Comments1 Solution16 ViewsLast Modified:
I have a database with tables like this:

TableA:
ID
--
A
B
C
XrefAB table:
ID   Name
--   ----
A    Alpha
B    Bravo
C    Charlie
TableB:
Name    Data
----    ----
Alpha   1
Bravo   2
Charlie 3
I have a DataGridView displaying TableA.
And I have a TextBox to display Data from TableB.
DataGridView
+-----+         TextBox
|  A  |        +------+
|  B  |        |      |
|  C  |        +------+
+-----+

User selects a line from DataGridView, and TextBox displays corresponding data.

For example, User selects line A. The XrefAB table says A maps to "Alpha". TableB name "Alpha" corresponds to data "1". Display "1" in TextBox.

This I can accomplish as follows: (I'm using an Oracle database. Assume 'connection' is a connection to the Oracle database.)
DataSet ds = new DataSet();
OracleDataAdapter adapterTableA = new OracleDataAdapter("Select * from TABLEA", connection);
OracleDataAdapter adapterXrefAB = new OracleDataAdapter("Select * from XREFAB", connection);
OracleDataAdapter adapterTableB = new OracleDataAdapter("Select * from TABLEB", connection);

adapterTableA.Fill(ds, "TableA");
adapterXrefAB.Fill(ds, "XrefAB");
adapterTableB.Fill(ds, "TableB");

DataRelation drA2Xref = new DataRelation("TableAXref", 
      ds.Tables["TableA"].Columns["ID"],
      ds.Tables["XrefAB"].Columns["ID"]);
ds.Relations.Add(drA2Xref);

DataRelation drXref2B = new DataRelation("Xref2TableB", 
      ds.Tables["XrefAB"].Columns["Name"],
      ds.Tables["TableB"].Columns["Data"]);
ds.Relations.Add(drXref2B);

BindingSource xrefBS = new BindingSource();
BindingSource textBoxBS = new BindingSource();

xrefBS.DataSource = ds;
xrefBS.DataMember = "TableA";

textBoxBS.DataSource = xrefBS;
textBoxBS.DataMember = "Xref2TableB";

dataGridView.DataSource = ds;
dataGridView.DataMember = "TableA";

textBox.DataBindings.Add("Text", textBoxBS, "Data");
and this works OK.
Now I change the XrefAB table to:
ID   Name
--   ----
A    Alpha
B    Alpha
C    Charlie
Suppose user now selects from the DataGridView row B.
B maps to "Alpha".
"Alpha" maps to "1".
I'd like the TextBox to display a "1".

But it doesn't. Instead the whole thing blows up with the operating system complaining my XrefAB table Name column does not have unique values. The error message thrown is "These columns currently don't have unique values".

Why won't this work, and, how can I make this work?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros