#Region "FillListView"
Sub FillListview()
LV.Items.Clear()
myqry = "SELECT AccResult.StudNo,Exercises.ID from AccResult INNER JOIN Exercises ON AccResult.StudNo = Exercises.ID ORDER BY AccResult.FirstName,AccResult.YrandSec Asc;"
mycmd = New OleDbCommand(myqry, con)
con.Open()
mydr = mycmd.ExecuteReader
While mydr.Read
With LV
.Items.Add(mydr("StudNo"))
With .Items(.Items.Count - 1).SubItems
.Add(mydr("CNumber"))
.Add(mydr("FirstName"))
.Add(mydr("LastName"))
.Add(mydr("YrandSec"))
.Add(mydr("Exer1"))
.Add(mydr("Exer2"))
.Add(mydr("Exer3"))
.Add(mydr("Exer4"))
.Add(mydr("Exer5"))
End With
End With
End While
con.Close()
End Sub
#End Region
AccResult
is the name of my first table and Exercises
is the second.
My PK for AccResult
is StudNo
and for Exercises
is ID
.
How can I join these two tables to display in ListView
?
AccResult
Table:
StudNo (PK)
CNumber
FirstName
LastName
YrandSec
Exercises
Table:
ID (PK)
StudNo
Exer1
Exer2
Exer3
Exer4
Exer5
Your JOIN condition is incorrect.
AccResult INNER JOIN Exercises ON AccResult.StudNo = Exercises.ID
is joining the primary key of the parent table (AccResult.StudNo) with the primary key of the child table (Exercises.ID). You need to join the primary key of the parent table (AccResult.StudNo) with the foreign key of the child table (Exercises.StudNo), i.e.,
AccResult INNER JOIN Exercises ON AccResult.StudNo = Exercises.StudNo