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!

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....

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

And, Rowposition is declared in a Module!

Cheers

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!

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.

commented: Brilliant, thanks! +0

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.