VB.NET - Show Two tables in one DataGrid

[Pirate]

Using ADO.NET and Relations . Check the code it's commented .

[Pirate]

I created it with VS.NET 2003 , if you have the previous version , then you need to use the converter under my sig . And the database , I used MS Access XP .

[iqueen]

        'First command for first table          cmd1 = New OleDbCommand          cmd1.Connection = conn          cmd1.CommandText = "SELECT * FROM Tab1"              'Second command for Second table          cmd2 = New OleDbCommand          cmd2.Connection = conn          cmd2.CommandText = "SELECT * FROM Tab2" 

why donot

cmd1.CommandText = "SELECT * FROM Tab1,Tab2" 

[iqueen]

'First command for first table
cmd1 = New OleDbCommand
cmd1.Connection = conn
cmd1.CommandText = "SELECT * FROM Tab1"


'Second command for Second table
cmd2 = New OleDbCommand
cmd2.Connection = conn
cmd2.CommandText = "SELECT * FROM Tab2"

why donot u

cmd1.CommandText = "SELECT * FROM Tab1,Tab2"

[Pirate]

No , I found it more organized when iterating two separate obj with two different sort of data .

[siomai]

interesting program you have there...It might be useful to me but my problem is I can't open it...the computer i'm working on doesn't have a win rar application and I can't install one since this is not mine...I was thinking if u could post the code cause I'm looking for a code in deleting master/detail records. I have one already but it actually deletes the first record in my database tables. Hoping for a most positive reply from u soon!

[brown monkey]

sig of pirate has link to winrar.

[Pirate]

That's the whole code .

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Path to database
        Dim dbpath As String = Application.StartupPath & "\mydb.mdb"
 
        'Connection obj to database
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & ";Jet OLEDB:Database Password=")
 
        'Open the Connetion 
        conn.Open()
        'Dataset that holds data in disconnected mode
        Dim ds As New DataSet
 
        'Two commands for two tables (tab1 and tab2)
        Dim cmd1 As OleDbCommand
        Dim cmd2 As OleDbCommand
 
        'Two datapaters to fill the dataset from two tables
        Dim adp1 As OleDbDataAdapter
        Dim adp2 As OleDbDataAdapter
 
        'This handles the relationship between the two columns 
        Dim datarelation As DataRelation
        Dim dc1 As DataColumn
        Dim dc2 As DataColumn
 
        'It's not important but gives your code more better way to 
        'compare strings between tables
        ds.CaseSensitive = True
 
        'First command for first table
        cmd1 = New OleDbCommand
        cmd1.Connection = conn
        cmd1.CommandText = "SELECT * FROM Tab1"
 
 
        'Second command for Second table
        cmd2 = New OleDbCommand
        cmd2.Connection = conn
        cmd2.CommandText = "SELECT * FROM Tab2"
 
        'Now , we will fill the first table and add it to the dataset
        adp1 = New OleDbDataAdapter
        adp1.SelectCommand = cmd1
        adp1.TableMappings.Add("Table", "Tab1")
        adp1.Fill(ds)
 
 
        'As we did in the previous step , here for the Second table
        adp2 = New OleDbDataAdapter
        adp2.SelectCommand = cmd2
        adp2.TableMappings.Add("Table", "Tab2")
        adp2.Fill(ds)
 
 
 
        dc1 = ds.Tables("Tab1").Columns("ID")
        dc2 = ds.Tables("Tab2").Columns("ID")
 
        'Here we combined two datacolumns to the relations obj 
        datarelation = New DataRelation("Tab1andTab2", dc1, dc2)
        ds.Relations.Add(datarelation)
 
        'Simple one , bind the dataset after all operation to the 
        'Datagrid
        DataGrid1.DataSource = ds.DefaultViewManager
        'Show the first table in the grid because it's the primary table
        DataGrid1.DataMember = "tab1"
 
        'That's all folks :)
        'Pirate
 
    End Sub

[SonicBoomAu]

Pirate,

This is a great piece of code. It is really helpful. I've been pulling my hair out trying to do this.

I have placed your code into another forum Link so that other people can appriecate your hard work.

Once again thank you for placing this code onto the web.

SonicBoomAu.

[extreme.aly]

i'm getting a problem...when i update the second table, i give me this error: Syntax error in INSERT INTO statement.

[vks.gautam1]

Thanks. Good Tutorial. It is working.
But what i want is like.
Select
t1.*,t2.*
from
tab1 as t1
inner join
tab2 as t2
on t1.id=t2.id

Can we show both tables related data with the help of two data tables in DataGridview.