Question Football prognostics project, datagrid wrong

[Svekke]

Hello all,

I'm trying to make a program in which football (or some other sport) prognostics can be entered and scores can be calculated automatically.

I've set up the database and I've created 3 layers. In the data layer, I've added my access 2007 database (it already has the right links between the tables). These are my 'PrognoMakerDataSet.xsd'

I've also created an sql statement in the table 'teams' to collect the teamitems (name, ID, foundationyear) and to collect the coach's name and the countryname from the 'COACHES' and 'COUNTRIES' tables.

When I run this sql in preview, the correct output show up. So far so good. The SQL code is the following:

Select TEAMS.TeamID as TeamID, strconv((TEAMS.TeamName),3) as Name, 
strconv((COUNTRIES.CountryName),3)as Country, strconv(COACHES.CoachName,3) as Coach,
 TEAMS.FoundationYear as Foundation 
FROM ((TEAMS LEFT JOIN COACHES ON TEAMS.TeamCoach = COACHES.CoachID) 
LEFT JOIN COUNTRIES ON TEAMS.TeamCountry = COUNTRIES.CountryID) 
ORDER by TEAMS.TEAMNAME

I've also created a presentation layer, with 2 things on it. A button (btnGetAllTeams) (if clicked, a gridview shows up) and a gridview grdvAllTeams.

In my presentation layer, I have the following code

Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Teams

    Dim BLLteams As New BLL_TEAMS

    Private Sub Teams_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim teams As New DataTable
        Me.grdvAllTeams.Visible = False
        teams = BLLteams.getAllTeams
        Me.grdvAllTeams.DataSource = teams

    End Sub

    Private Sub btnGetAllTeams_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetAllTeams.Click
        Me.grdvAllTeams.Visible = True
        grdvAllTeams.Refresh()
    End Sub

End Class

This code uses the Business layer to reach the data layer. So far so good ? Or not?

In my business layer, I have the following code:

Imports Microsoft.VisualBasic
Imports Prognomaker.PrognomakerDataSetTableAdapters
Imports System.Data


Public Class BLL_TEAMS

    Dim m_TeamNaam As String
    Dim teamadapter As New PrognomakerDataSetTableAdapters.TEAMSTableAdapter
    Dim m_rowsaffected As Integer

    <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, False)> _
    Public Function getAllTeams() As PrognomakerDataSet.TEAMSDataTable
        ' deze functie geeft ALLE teams terug aan de presentation layer
        Try
            Dim teams As New DataTable
            teams = teamadapter.GetAllTeams
            ' vul de datatable met alle klanten, en stuur deze terug naar de PLL
            Return teams

        Catch exc As Exception
            MsgBox("Er zijn nog geen teams aangemaakt.")
        End Try
        Return Nothing

    End Function
End Class

The problem is, when I click the btnGetAllTeams, the gridview pops up, but it contains 'TEAMID', 4 blank columns (teamname, teamcountry, ...), and then the correct columns that i need in my output.

I did notice that if i click 'execute query' in the dataset (when configuring the sql), I get the perfect output. When I click 'preview' in the dataset (after clicking on a table itself), I get the wrong output.

I'm only a beginner, so I'm probably making a lot of big mistakes that don't make any sense, but could someone please have a look at my code and try to tell me what's wrong about it?



PS: I've added a screenshot of the output in attachment:

 

[cjard]

Mmmmhhhh. I think this approach will trip you up when you come to want to edit the data

Instead do:

New form
Show Data Sources window in visual studio
Drag the TEAMS table to the form. Grid appears with ID columns
Expand the TEAMS node in the data sources window.. notice it has coaches and countries below it (as well as them being in the main level)
Drag each of these child versions of coaches/countries to the form, then delete both the grid AND the bindingnavigator that were made (leave just the bindingsource)
Edit the columns of the grid (columns property)
Make the coaches column a combobox type
Set its data source to be the coachesbindingsource on the "form list instances"
Set its display and value members to be CoachName and CouchID respectively
Repeat for Countries


Now you have:
a grid that shows names for countries and coaches
The combo boxes do the decoding of id -> name and back again
You can change the country by editing the combo
the value saved in the table in the db is the ID not the displayed name


For more info read the DW3 (or 4) link in my signature, section on Displaying related Data (i think.. its been a while since I was asked this one)

Also remember to edit the column headers of the grid.. the displayed text, not the source column! (otherwise youll get blank columns like you have here)

 

[cjard]

note that your button doesnt really "get" the teams;; they get elsewhere.. Ditch that button for now and just leave the grid visible right from the start of the app

Remember to leave alone the lines of code put int he form load. they look like this, probably:
Me._countriesTableAdapter.Fill(Me.dataset.Countries)


These lines of code fill the countries into locally held tables so that the combos can decode them. without the data being local, you will get errors

 

[Svekke]

I'll try this out, but am I not creating an application with only 1 or 2 layers instead of 3 if I use this approach?

 

[cjard]

I dont understand the question, sorry

 

[Svekke]

I'll try to rephrase the question:

I'm trying to make a 3-Layer (3-tier) application with a Presentation layer (the datagrid, the form, ...), a datalayer (the database and the datasets) and a business logic layer (the methods for calling the datasets, ...).

If I use your approach, will I not be using only 2 layers instead of 3?

 

[cjard]

Your app will still have forms, datasets and a variety of classesthat have no user interaction, though? I'm not sure how removing a INNER JOIN from a query in a table adapter and delegating the work it was doing to two extra tableadapter/datatable/datagridviewcolumns will "flatten" your app to a 2 tier one?

 

[Svekke]

Jeah you're probably right, I'm just a newbie I'll try this one out as soon as possible

Thank you very much