Populating datagridview from multiple tables via access database

[Yaw_1]

I have been trying fruitlessly to populate my datagridview using multiple tables from my ms access database. My code is shown below. Thanks in advance for your help.

Imports System.Data.OleDb
Public Class Stock_Check_Options
    Dim MyConnection As OleDbConnection
    Dim MyDataAdapter As OleDbDataAdapter
    Dim MyDataAdapter1 As OleDbDataAdapter
    Dim MyDataSet As DataSet
    Dim StockDisplayTable As DataTableCollection
    Dim StockDataBase As New BindingSource

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Back_Button.Click
        Me.Close()
    End Sub

    Private Sub Label1_Click(sender As System.Object, e As System.EventArgs) Handles StockPopulation.Click
        'StockPopulation = "No. Of Items: " & "Value: £ "
    End Sub
    Private Sub Close_Button_Click(sender As System.Object, e As System.EventArgs) Handles Close_Button.Click
        Me.Close()
    End Sub

    Private Sub Label1_Click_1(sender As System.Object, e As System.EventArgs) Handles StockPopulationAndVAlue_Label.Click

        'StockPopulationAndVAlue_Label = "Items: " &
    End Sub

    Private Sub View_Button_Click(sender As System.Object, e As System.EventArgs) Handles View_Button.Click
        MyConnection = New OleDbConnection
        MyConnection.ConnectionString = "Provider= Microsoft.ace.oledb.12.0;Data Source=Stock Database.accdb"
        'provides access to ms access database
        MyDataSet = New DataSet
        StockDisplayTable = MyDataSet.Tables
        MyDataAdapter = New OleDbDataAdapter("Select ChemicalName, ChemicalID, UseQuantity, QuantityAvailable, ThresholdLimit, SupplierName, SupplierID, PurchasePrice, DateSent, DateReceived  from [Chemical],[SupplierTable],[OrderTable],[ChemicalUsage] where Chemical.ChemicalID = OrderTable.ChemicalID AND SupplierTable.SupplierID=OrderTable.SupplierID ", MyConnection.ConnectionString) 'Selects from the table(s) specified
        MyDataAdapter.Fill(MyDataSet, "Chemical" And "SupplierTable" And "ChemicalUsage")
        Dim View As New DataView(StockDisplayTable(0))
        StockDataBase.DataSource = View
        StockDisplayDataGrid.DataSource = View
    End Sub

[djjeavons]

Hi

Does your Checmical, SupplierTable, OrderTable and ChecmicalUsage tables all have the same table definition? That is, do they all contain the same fields?

If so, you should use a UNION query to combine all of the data from the multiple tables into one return table. Something like:

SELECT *
FROM Chemical
UNION
SELECT * FROM
SupplierTable

However, I have a feeling that they probably don't have the same definition based on the names of the tables. So maybe if you could tell us how you expect the data to be presented then we might be able to come up with a solution.

HTH

[Yaw_1]

Hello
Sorry for the late reply. My Pc was being repaired.

No they dont each table contains different fields however the order table is the link table

in the datagridview i want to display all the fields in both the suppliertable table and the chemical table