Querying & updating multiple tables with VB 2010

[donatas]

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!

[kingsonprisonic]

I found some buggy things on ur code....

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 'Where u declare Rowposition ?
        Call ShowCurrentRecord()
    End Sub

Where u declare Rowposition ?

So declare the Rowposition first..

And yes i think the code is enough for ur purpose. But remember to close the connection after performing tasks with database....

[donatas]

Great thanks! Now just need to write up the SQL query code.

And, Rowposition is declared in a Module!

Cheers

[Reverend Jim]

In English, what data are you trying to display?

[donatas]

Basically this form will display and record data during a match.
So, I want to display..
the number, name & surname from player table of all players from the 2 teams that are selected in the form before that, which I have done already.
And record new data to the matchdata table.

Thanks!

[Reverend Jim]

One way to do it would be to retrieve a recordset with all the data in one query, then step through the records and copy the data to different areas depending on whether the record is for team 1 or team 2.

query = "select PlayerNumber,PlayerForename,PlayerSurname,TeamID" _
      & "  from player" _
      & " where TeamID = " & TeamID1 & " or TeamID = " & TeamID2"

Another way would be to execute two separate queries, one for each TeamID. I'm assuming TeanID is a numeric field. If string then you need single quotes around the field value.

If you want to bind the query to a control then someone else will have to step in. I'm old school ADO and SQL.

[donatas]

Thanks for the help, will implement this later today and see if it does the job!