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:
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?