Displaying datagrid on second form
 

I have a form which has a listbox, 5 textboxes and a datagrid, what I'm trying to do is when a user double clicks a row on the datagrid this then displays another datagrid on the second form with the details of the orders placed from the Items Table for the selected Customer ID on the first form

I have already set up a data relation to link the Customers and Orders Table on the first form through the Customer ID and have tried to link the Orders Table and Items Table through the Order ID to display the info in the second datagrid but doesn't work, getting an error on the relation declaration line

My coding is below any advice would be appreciated on this

Thanks

Imports System.Data.OleDb

Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

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

'Declares and instantiates a command object

Dim SqlCustomers As String = "SELECT * FROM customers"

'Sets the connection string

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"

'Declares and instantiates a new OleDbconnection object

Dim myconnection As OleDbConnection = New OleDbConnection(connString)

'Sets the connection string property

myconnection.ConnectionString = connString

'Declares and instantiates a data adapter

Dim dacustomers As OleDbDataAdapter = New OleDbDataAdapter(SqlCustomers, myconnection)

'Declares and instantiates a new data set

Dim ds As DataSet = New DataSet

'Fills the data adapter with the customers table

dacustomers.Fill(ds, "customers")

'Declares and instantiates a command object

Dim sqlorders As String = "SELECT * FROM Orders "

'Declares and instantiates the datadapter

Dim daorders As OleDbDataAdapter = New OleDbDataAdapter(sqlorders, myconnection)

'Fills the data adapter with the orders table

daorders.Fill(ds, "orders")

'Declares and instantiates a new data relation linking together the customers and the orders table

Dim relation As DataRelation = New DataRelation("CustomersOrders", ds.Tables("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID"))

'Adds the relation to the collection

ds.Relations.Add(relation)

ListBox1.DisplayMember = "CustomerID"

ListBox1.DataSource = New DataView(ds.Tables("customers"))

'Binds the customers table details to the text boxes

TextBox1.Text = ds.Tables("customers").Rows(0).Item("FirstName")

TextBox2.Text = ds.Tables("customers").Rows(0).Item("LastName")

TextBox3.Text = ds.Tables("customers").Rows(0).Item("City")

TextBox4.Text = ds.Tables("customers").Rows(0).Item("State")

TextBox5.Text = ds.Tables("customers").Rows(0).Item("ZipCode")

End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

'Declares a data row view

Dim drv As DataRowView = CType(ListBox1.SelectedItem, DataRowView)

'Declares a data row

Dim childRows() As DataRow = drv.Row.GetChildRows("CustomersOrders")

TextBox1.Text = drv("FirstName")

TextBox2.Text = drv("LastName")

TextBox3.Text = drv("City")

TextBox4.Text = drv("State")

TextBox5.Text = drv("ZipCode")

'Sets the data grid data source property

DataGrid1.DataSource = drv.CreateChildView("CustomersOrders")

End Sub

Private Sub DataGrid1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.DoubleClick

'Declares and instantiates a new form

Dim myForm As New Form2

'Displays the second form

myForm.ShowDialog()

End Sub

End Class

Imports System.Data.OleDb

Imports System.IO

Public Class Form2

Inherits System.Windows.Forms.Form

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

End

End Sub

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

Dim sqlOrderItems As String = "SELECT * FROM Items"

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\The Doughnut Shop.mdb"

Dim myconnection As OleDbConnection = New OleDbConnection(connString)

myconnection.ConnectionString = connString

Dim daOrderItems As OleDbDataAdapter = New OleDbDataAdapter(sqlOrderItems, myconnection)

Dim ds As DataSet = New DataSet

daOrderItems.Fill(ds, "Items")

Dim relation As DataRelation = New DataRelation("OrdersItems", ds.Tables("Orders").Columns("OrderID"), ds.Tables("Items").Columns("OrderID"))

ds.Relations.Add(relation)

Dim drv As DataRowView

Dim childRows() As DataRow = drv.Row.GetChildRows("OrdersItems")

DataGrid1.DataSource = drv.CreateChildView("OrdersItems")

End Sub

End Class