Creating Relations with 3 tables

[Guest]

I have created a dataset with 3 tables. The first table in the dataset is tblCustomer that has customer_id as the primary key and relates to the foreign key customer_id in table tblCustomerCategory. tblCustomerCategory is a "bridge" table and has as its primary key customer_id, category_id and business_id. The category_id and business_id in tblCustomerCategory relates to the foreign keys category_id and business_id in tblCategory. tblCategory has as its primary key category_id and business_id. I have set up the primary keys and relations in code, but when I run the code I get an error on the line dsDataSet11.Relations.Add(drDataRelation2) (before catch exception): "These columns don't currently have unique values". I am not sure why this is happening. Here is the code

'Define primary key for tblCustome
sqlDataAdapter1.Fill(dsDataSet11, "tblCustomer"
tCustomerTable = dsDataSet11.Tables("tblCustomer"
tCustomerTable.PrimaryKey = New DataColumn() {tCustomerTable.Columns("customer_id")

'Set criteria for CustomerCategory tabl
strCriteria2 = "Select t.customer_id, t.category_id, t.business_id, t.customerCategory_timestamp " &
"from tblCustomerCategory t join tblCustomer c " &
"on t.customer_id = c.customer_id where

If strFindFname <> "" The
fFname = Tru
strCriteria2 = strCriteria2 + "c.customer_fname = " + "'" + strFindFname + "'
End I

If strFindLname <> "" The
fLname = Tru
If fFname The
strCriteria2 = strCriteria2 + " and c.customer_lname = " + "'" + strFindLname + "'
Els
strCriteria2 = strCriteria2 + " customer_lname = " + "'" + strFindLname + "'
End I
End I

If strFindCompany <> "" The
fCompany = Tru
If fFname Or fLname The
strCriteria2 = strCriteria2 + " and c.customer_companyName like " + "'%" + strFindCompany + "%'
Els
strCriteria2 = strCriteria2 + " c.customer_companyName like " + "'%" + strFindCompany + "%'
End I
End I

Tr

'Define primary key for tblCustomerCategor
sqlDataAdapter5 = New SqlClient.SqlDataAdapter(strCriteria2, SqlConnection1
sqlDataAdapter5.Fill(dsDataSet11, "tblCustomerCategory"
tCustomerCategoryTable = dsDataSet11.Tables("tblCustomerCategory"
tCustomerCategoryTable.PrimaryKey = New DataColumn() {tCustomerCategoryTable.Columns("customer_id"),
tCustomerCategoryTable.Columns("category_id"),
tCustomerCategoryTable.Columns("business_id")
'Define promary key for tblCategor
sqlDataAdapter6 = New SqlClient.SqlDataAdapter("Select * from tblCategory", SqlConnection1
sqlDataAdapter6.Fill(dsDataSet11, "tblCategory"
tCategoryTable = dsDataSet11.Tables("tblCategory"
tCategoryTable.PrimaryKey = New DataColumn() {tCategoryTable.Columns("category_id"),
tCategoryTable.Columns("business_id")

'Build relationship between the Customer and CustomerCategory table
colParent1 = dsDataSet11.Tables("tblCustomer").Columns("customer_id"
colChild1 = dsDataSet11.Tables("tblCustomerCategory").Columns("customer_id"
drDataRelation1 = New DataRelation("drCustomer",
colParent1, colChild1
dsDataSet11.Relations.Add(drDataRelation1

'Build relationship between the CustomerCategory and Category table
colParent2(0) = dsDataSet11.Tables("tblCustomerCategory").Columns("category_id"
colParent2(1) = dsDataSet11.Tables("tblCustomerCategory").Columns("business_id"

colChild2(0) = dsDataSet11.Tables("tblCategory").Columns("category_id"
colChild2(1) = dsDataSet11.Tables("tblCategory").Columns("business_id"
bConstraints = True
drDataRelation2 = New DataRelation("drCustomerCategory", _
colParent2, colChild2, bConstraints)
dsDataSet11.Relations.Add(drDataRelation2)


Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

 

[Val Mazur]

Hi Gary,

Since combination of the "customer_id, category_id and business_id" fields
gives you a primary key, then you cannot set relations only for two rows
from this PK. You have to have relation between tblCustomerCategory and
tblCategory, where you will have all three fields from the parent and all
three fields from the child. Otherwise it will not work