Hi vis781 thanks for the reply
To give you a better idea of what I'm trying to achieve I'll show you 2 lots of coding, the first lot is the completed version 1 I've done and the second lot is the version 2 that I've done so far. The only difference between the 2 versions is that I have to create custom controls for version 2
Version 1 code
Imports System.Data.OleDb
Imports System.IO
Public Class frmorders
Inherits System.Windows.Forms.Form
Public Class InvalidCustomerIDException
Inherits ApplicationException
Public Sub New()
MyBase.New("Invalid CustomerID")
End Sub
End Class
Private Sub frmorders_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=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 information from the customers table
dacustomers.Fill(ds, "customers")
'Declares and instantiates a command object
Dim sqlorders As String = "SELECT * FROM Orders "
'Declares and instantiates a 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)
'Sets the display member property
lstcustid.DisplayMember = "CustomerID"
'Instantiates a new data view for the customers table
lstcustid.DataSource = New DataView(ds.Tables("customers"))
'Binds the customers table details to the text boxes
txtfirstname.Text = ds.Tables("customers").Rows(0).Item("FirstName")
txtlastname.Text = ds.Tables("customers").Rows(0).Item("LastName")
txtcity.Text = ds.Tables("customers").Rows(0).Item("City")
txtstate.Text = ds.Tables("customers").Rows(0).Item("State")
txtzipcode.Text = ds.Tables("customers").Rows(0).Item("ZipCode")
End Sub
Private Sub lstcustid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstcustid.SelectedIndexChanged
' Try catch statement
Try
ValidateCustomerID(Integer.MinValue = 1)
ValidateCustomerID(Integer.MaxValue = 6)
Catch ex As InvalidCustomerIDException
End Try
'Declares a data row view
Dim drv As DataRowView = CType(lstcustid.SelectedItem, DataRowView)
'Declares a data row
Dim childRows() As DataRow = drv.Row.GetChildRows("CustomersOrders")
txtfirstname.Text = drv("FirstName")
txtlastname.Text = drv("LastName")
txtcity.Text = drv("City")
txtstate.Text = drv("State")
txtzipcode.Text = drv("ZipCode")
'Sets the data grid data source property
dgorders.DataSource = drv.CreateChildView("CustomersOrders")
End Sub
Private Sub ValidateCustomerID(ByVal CustomerID As Integer)
If CustomerID < 0 Or CustomerID > 6 Then
Dim myException As New InvalidCustomerIDException
Throw New InvalidCustomerIDException
End If
End Sub
Private Sub dgorders_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgorders.DoubleClick
'Declares and instantiates a new form
Dim myForm As New frmitems
'This sets the index of the current row and the column number
Dim int As Integer = CStr(dgorders.Item(dgorders.CurrentRowIndex, 0).ToString)
'Sets the value of the customer ID property
myForm.CustID = int
'Displays the second form
myForm.ShowDialog()
End Sub
End Class
Imports System.Data.OleDb
Imports System.IO
Public Class frmitems
Inherits System.Windows.Forms.Form
' Declares an integer variable to hold the value of the property
Private ID As Integer
Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
End
End Sub
'A property which can be set once the form is created
Public Property CustID() As Integer
Get
Return ID
End Get
Set(ByVal Value As Integer)
ID = Value
End Set
End Property
Private Sub frmitems_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Declares and instantiates command objects
Dim sqlOrderItems As String = "SELECT * FROM Items"
Dim sqlOrders As String = "SELECT * FROM orders"
'Sets the connection string
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=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 daOrderItems As OleDbDataAdapter = New OleDbDataAdapter(sqlOrderItems, myconnection)
Dim daOrders As OleDbDataAdapter = New OleDbDataAdapter(sqlOrders, myconnection)
'Declares and instantiates a new data set
Dim ds As DataSet = New DataSet
'Fills the data adapter with the items table
daOrderItems.Fill(ds, "Items")
'Fills the data adapter with the orders table
daOrders.Fill(ds, "Orders")
'Declares and instantiates a new data relation linking together the items table and the orders table
Dim relation As DataRelation = New DataRelation("OrdersItems", ds.Tables("Orders").Columns("OrderID"), ds.Tables("Items").Columns("OrderID"))
ds.Relations.Add(relation)
'A DataView based on the Items table
Dim dv As New DataView(ds.Tables("Items"))
'Sets the RowFilter to the value of the CustID property
dv.RowFilter = "OrderID = '" & CustID & " ' "
'This sets the DataSource of the DataGrid to the DataView
dgitems.DataSource = dv
End Sub
End Class
Version 2 code so far
Public Class ctlCustomerDetails
Inherits System.Windows.Forms.UserControl
Private ReadOnly strFirstName As String
Private ReadOnly strLastName As String
Private ReadOnly strCity As String
Private ReadOnly strState As String
Private ReadOnly strZipCode As String
Private strCustomerID As String
Public ReadOnly Property FirstName() As String
Get
Return strFirstName
End Get
End Property
Public ReadOnly Property LastName() As String
Get
Return strLastName
End Get
End Property
Public ReadOnly Property City() As String
Get
Return strCity
End Get
End Property
Public ReadOnly Property State() As String
Get
Return strState
End Get
End Property
Public ReadOnly Property ZipCode() As String
Get
Return strZipCode
End Get
End Property
Public Property CustomerID() As String
Get
Return strCustomerID
End Get
Set(ByVal Value As String)
strCustomerID = Value
End Set
End Property
End Class
If you need any more info let me know
Thanks for your help
