NewPassionnate NewPassionnate - 1 month ago 9
C# Question

create relation between two tables

I am trying to create a relationship between two tables.
User choose the first table (choice1) then choose the second table (choice2) and choose the field in the second table (field2). He then gives a name to the relationship (namerelationship)

I get this error


Invalid field definition 'ForeignKey' in definition of index or relationship


string namerelationship = txtNameRelationship.Text;
string choice1 = cboTable1.SelectedItem.ToString();
string choice2 = cboTable2.SelectedItem.ToString();
string field2 = cboField2.SelectedItem.ToString();

Relation myrel = clsDataSource.mydb.CreateRelation(namerelationship, choice1, choice2);
Field myfield = new Field();
myfield = myrel.CreateField(choice1);
myfield.ForeignName = "ForeignKey";
myrel.Fields.Append(myfield);
clsDataSource.mydb.Relations.Append(myrel);

Answer

When using the Relation object of DAO you need to specify for the first field the name of an existing field from the first table (usually this is the primary key) and for the ForeignName property the name of a field of the second table that act as ForeignKey to the first table.

So suppose you have a Deparment table with

IDDepartment (integer, primary key)
Description (text, not null)

And a table Employee

IDEmployee (integer, primary key)
Name (text not null)
IDDepartment (integer, foreign key to Department table)

where a relationship exists between the Department and the Employee table of this kind: An employee work in one (and only one) department. (1 to 1)

Then your code should be

string choice1 = "Department";
string choice2 = "Employee";
string fieldPK = "IDDepartment"; // It is the primary key of Department
string fieldFK = "IDDepartment"; // It is the foreign key in table Employee that links to the Department table
Relation myrel = clsDataSource.mydb.CreateRelation(namerelationship,  
                 choice1, choice2);
Field myfield = myrel.CreateField(fieldPK);
myfield.ForeignName = fieldFK;
myrel.Fields.Append(myfield);
clsDataSource.mydb.Relations.Append(myrel);

Said that I really recommend you to switch as soon as possible from DAO to ADO.NET