Avatar of deleyd
deleydFlag for United States of America

asked on 

DataRelation - These columns currently don't have unique values

I have a database with tables like this:

TableA:
ID
--
A
B
C

Open in new window

XrefAB table:
ID   Name
--   ----
A    Alpha
B    Bravo
C    Charlie

Open in new window

TableB:
Name    Data
----    ----
Alpha   1
Bravo   2
Charlie 3

Open in new window

I have a DataGridView displaying TableA.
And I have a TextBox to display Data from TableB.
DataGridView
+-----+         TextBox
|  A  |        +------+
|  B  |        |      |
|  C  |        +------+
+-----+

Open in new window


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");

Open in new window

and this works OK.
Now I change the XrefAB table to:
ID   Name
--   ----
A    Alpha
B    Alpha
C    Charlie

Open in new window

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?

C#

Avatar of undefined
Last Comment
deleyd
ASKER CERTIFIED SOLUTION
Avatar of David H.H.Lee
David H.H.Lee
Flag of Malaysia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of deleyd
deleyd
Flag of United States of America image

ASKER

Another solution my co-worker came up with. She solved it by joining tables in the SQL statement:
DataSet ds = new DataSet();

string sql = @"
SELECT c.DATA, c.NAME, a.ID FROM TABLEB c
INNER JOIN XREFAB b ON c.NAME = b.NAME
INNER JOIN TABLEA a on b.ID = a.ID;";

OracleDataAdapter adapter = new OracleDataAdapter(sql, connection);

adapter.Fill(ds, "SqlResult");

DataRelation dr = new DataRelation("ResultTable",       ds.Tables["TableA"].Columns["ID"],
ds.Tables["SqlResult"].Columns["ID"]);
ds.Relations.Add(dr);

BindingSource grid = new BindingSource();
grid.DataSource = ds;
grid.DataMember = "TableA";

dataGridView.DataSource = grid;

BindingSource textBoxBS = new BindingSource();
textBoxBS.DataSource = grid;
textBoxBS.DataMember = "ResultTable

textBox.DataBindings.Add("Text", textBoxBS, "Data");

Open in new window

The SQL gives a resulting table of:
DATA   NAME      ID               
------ --------- ---
1      Alpha     A                      
1      Alpha     B                      
3      Charlie   C  

Open in new window

We then define relation between TableA.ID and this table ID.
Now as user browses TableA in a DataGridView, the text box fills with corresponding value in DATA column of this table.
C#
C#

C# is an object-oriented programming language created in conjunction with Microsoft’s .NET framework. Compilation is usually done into the Microsoft Intermediate Language (MSIL), which is then JIT-compiled to native code (and cached) during execution in the Common Language Runtime (CLR).

98K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo