Help with Data Relations

[Gary]

The ultimate goal is to have two data grids on a form.
The first Datagrid holds Contact information. The second
grid holds notes for each contact.

When I click on a row for contacts, I would like all
related notes to show on the Notes grid.

I created a data relation using the following code:
----------------------------------------------------------
'lets set up the relationship between the
contacts and notes table
Dim colContacts As DataColumn
Dim colNotes As DataColumn
colContacts = DataSet21.Tables("Contact").Columns
("SysID")
colNotes = DataSet21.Tables("Notes").Columns
("con_id")

Dim order_rel As DataRelation
order_rel = New DataRelation("order_rel",
colContacts, colNotes)

DataSet21.Relations.Add(order_rel)
DG_Notes.SetDataBinding
(DataSet21, "Contact.order_rel")
----------------------------------------------------------

but when I run this code, I get a message saying that "A
relation already exists for the child columns" I tried
to comment out the Relations.Add line but it didn't
work. I then went to the Enterprise manager to see if I
could delete the relation but can't find it.

How can I make the grids work together and not create
duplicate relations?

Any help and "verbose" code examples or changes would
help.

Thanks in advance,

Gary

 

[Ken Tucker [MVP]]

Hi,

This example assumes you have the northwind database installed on
your sqlserver. You need 3 grids on the form dgEmployees, dgOrders,
dgOrderDetails.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim ds As DataSet

Dim daEmployees As SqlDataAdapter

Dim daOrders As SqlDataAdapter

Dim daOrderDetails As SqlDataAdapter

Dim conn As SqlConnection

Dim strConn As String

Dim strSQL As String

Dim strItem As String

Dim ctrl As Control

For Each ctrl In Me.Controls

If TypeOf ctrl Is DataGrid Then

Dim dg As DataGrid = ctrl

dg.AllowNavigation = False

End If

Next

strConn = "Server = " + Environment.MachineName + "\VSdotNet;"

strConn += "Database = NorthWind;"

strConn += "Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

ds = New DataSet

daEmployees = New SqlDataAdapter("Select * from Employees", conn)

daOrders = New SqlDataAdapter("Select * from Orders", conn)

daOrderDetails = New SqlDataAdapter("Select * from [Order Details]", conn)

daEmployees.Fill(ds, "Employee")

daOrders.Fill(ds, "Orders")

daOrderDetails.Fill(ds, "OrderDetails")

ds.Relations.Add("EmployeeOrder",
ds.Tables("Employee").Columns("EmployeeID"), _

ds.Tables("Orders").Columns("EmployeeID"))

ds.Relations.Add("Order2Details", ds.Tables("Orders").Columns("OrderID"), _

ds.Tables("OrderDetails").Columns("OrderID"))

dgEmployees.SetDataBinding(ds, "Employee")

dgOrders.SetDataBinding(ds, "Employee.EmployeeOrder")

dgOrderDetails.SetDataBinding(ds, "Employee.EmployeeOrder.Order2Details")

End Sub



Ken

--------------------