Hi guys!
I've been searching all day about this and can't find a solution..
I'm fairly new to VB and SQL Queries, so I apologize if I sound a little daft.
Basically I'm working on a project in VB 2010 where a user can add new teams, players, also create matches between teams and record the score. I have done nearly all forms, but now I need to show data from 2 tables on one form and the method I used for 1 table doesn't work.
I have 4 tables: team, player, match & matchdata and they are linked like this:
http://droap.com/images/krASD.png
I used this code for querying a single table:
Public Class Teams
Private MyADONetConnection As New OleDb.OleDbConnection
Private MyDataAdapter As New OleDb.OleDbDataAdapter
Private MyCommandBuilder = New OleDb.OleDbCommandBuilder
Private MyDataTableTeam As New DataTable
Private Sub Teams_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
MyADONetConnection.ConnectionString = _
"Provider=Microsoft.jet.OLEDB.4.0;Data Source=datab1.mdb"
MyADONetConnection.Open()
MyDataAdapter = New OleDb.OleDbDataAdapter("Select * from team", _
MyADONetConnection)
MyCommandBuilder = New OleDb.OleDbCommandBuilder(MyDataAdapter)
MyDataAdapter.Fill(MyDataTableTeam)
Dim RecCount As Byte = MyDataTableTeam.Rows.Count
For Rowposition = 0 To RecCount
teamlist.Items.Add(MyDataTableTeam.Rows(Rowposition)("TeamName").ToString)
teamlist.SelectedIndex = 0
Next
End Sub
Private Sub ShowCurrentRecord()
txtTeamName.Text = MyDataTableTeam.Rows(Rowposition)("TeamName").ToString
txtCoachName.Text = MyDataTableTeam.Rows(Rowposition)("TeamCoachName").ToString
txtScoredLast.Text = MyDataTableTeam.Rows(Rowposition)("GoalsScoredLast").ToString
txtConcededLast.Text = MyDataTableTeam.Rows(Rowposition)("GoalsConcededLast").ToString
txtScoredTotal.Text = MyDataTableTeam.Rows(Rowposition)("GoalsScoredTotal").ToString
txtConcededTotal.Text = MyDataTableTeam.Rows(Rowposition)("GoalsConcededTotal").ToString
End Sub
Private Sub teamlist_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles teamlist.SelectedIndexChanged
Rowposition = teamlist.SelectedIndex
Call ShowCurrentRecord()
End Sub
My problem now is that I need to display data from the 'players' table and display/write data to the 'matchdata' table on one form.
Is it possible to achieve this by using the code I used above but with an appropriate SQL query?
Or would I need to change the code?
Thank you!