Question Creating a program for football prognostics

[Svekke]

Dear experts,

I'm currently creating a windows form application to automate the calculation of prognostics for football events (WC, EC, ...).

I've already created the database

(Players, Stadia, Teams, Coaches, ...)

I've also created a working back-end for the COACHES table. I can update, delete, insert, ... coaches using the form. The database that is connected is an access 2007 database. I don't use stored procedures, I use hard-coded sql statements in my presentation layer ( I know this is not the best way to do this ,but I couldn't get it to work with 3 layers ).

Currently, I'm creating the 'PLAYER' backend. Every player has a "TEAMID" in its database (which is a foreign key linking to the TEAM table). I can insert, update, ... players if I fill in a number myself.

Here is the situation of this page:

In the left corner, I have a button 'GET ALL PLAYERS' which fills a datagridview with the information about the players. I also have some textboxes to the right, which show the details of the item selected in the datagridview (and which are used to update a record)

Now here is the problem: my TEAMID is shown as a number. This number can be updated, but it's not very usefull to just fill in a number. I would like to see the teamname instead.

1) How can I display the TEAM NAME in the datagridview, instead of the ID? (this comes from another table, should I create extra datatables, data-adapters, ...?

2) How can I update the player's team? Instead of just filling in a number, I want to display the team name (e.g.: in a listbox of something like that), so the person updating the record can select the team name in a list, but the ID is stored in the PLAYERS table.

I'm really a newbie in VB.NET, I know the basics. I tried searching the forum for an answer to this, but I could only find some general info which I also found on Google, but it doesn't really answer my specific question.

Thanks in advance

(editor = Visual Studio 2010)
(I could post some of my code I you like)

 

[jmcilhinney]

I'm not sure exactly how you want the UI to work but one option would be to have the team in the grid using a combo box column. If that sounds good, check this out:

Adding a ComboBox Column to a DataGridView

 

[Svekke]

Hi jmcilhinney (and others)

This is the UI that I want: (I added a simple paint-made example of how it looks, see attachment)

In the datagridview (left on the form) I want to show these fields:

PLAYERNAME, PLAYERFIRSTNAME, PLAYERBIRTHDATE, .., PLAYERTEAM (I want to show the Name of the team he's currently playing in, instead of the number (ID). This does not have to be a combobox, since there will be no updating in the datagridview itself).

On the right of the form, I have a number of textboxes, datetimepickers, ... which automatically display the record selected in the datagridview (databound).

There, I want a combobox/listview/... to show all the possible teams, with the correct team selected (like the datetimepicker is showing the birthdate). If I change something in the listbox, and afterwards click the UPDATE button (self-made, not one of the gridview), then I want to write the foreign key to the database.

I had a look at the page you sent me, and this could be helpfull a bit, but this page focusses more on the datagridview itself, not on the updating of the record.

I will try to add a new data-adapter and bind it to the datagridview this evening, but could you also please tell me how the update itself is best handled outside of the datagridview?

Thanks

 

[jmcilhinney]

The UI is irrelevant to how you save data. You have a DataAdapter and a DataTable. You call Fill to retrieve the data and Update to save the changes. That's the same no matter how the data is displayed and/or edited.

 

[Svekke]

Alright, I will have a look at the code tonight. I'll let you know the result

 

[Svekke]

Hi again,

I tried to make the above work, but I can't seem to get it to work

I understand the principle of how it should be handled, but I'm facing a problem. I've added the datagridview in the DESIGN view, but the columns are created by an SQL query. So I can't alter the type of the column in the DESIGN view, I have to alter it in the code. But I don't really know how to do that. (I'm a newb in VB.NET).

Here is the complete code of this page (how it is now):

Public Class Players

    Dim connectiestring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Prognomaker\Database\Prognomaker.accdb"
    Dim connectie As New OleDb.OleDbConnection(connectiestring)
    Dim dtTeams As New DataTable
    Dim daTeams As New OleDb.OleDbDataAdapter("select TeamID, TeamName, TeamCountry from TEAMS", connectie)
    Dim dtPlayers As New DataTable
    Dim daPlayers As New OleDb.OleDbDataAdapter("select PlayerID, PlayerName, PlayerFirstName, PlayerTeam, PlayerBirthDate, EntryDateTime from PLAYERS order by  PlayerTeam, PlayerName, PlayerFirstName asc", connectie)

    Private Sub ReadPlayers()
        Try
            dtPlayers.Clear()
            daPlayers.Fill(dtPlayers)
            dtTeams.Clear()
            daTeams.Fill(dtTeams)
        Catch ex As Exception
            MessageBox.Show("Error during opening tables", ex.Message)
        End Try
    End Sub

    Private Sub Players_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Application.Exit()
    End Sub

    Private Sub Players_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Location = New Point(Home.Location.X, Home.Location.Y)
    End Sub

    Private Sub btnGetPlayers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetPlayers.Click
        'Clear databindings'
        txtPlayerID.DataBindings.Clear()
        txtPlayerName.DataBindings.Clear()
        txtPlayerFirstName.DataBindings.Clear()
        dtpPlayerBirthdate.DataBindings.Clear()
        lstPlayerTeam.DataBindings.Clear()
        dgvPlayers.DataBindings.Clear()

        'fill new databindings'
        ReadPlayers()
        dgvPlayers.DataSource = dtPlayers
        dgvPlayers.Columns(0).Visible = False
        dgvPlayers.Columns(1).HeaderText = "Name"
        dgvPlayers.Columns(2).HeaderText = "First Name"
        dgvPlayers.Columns(3).HeaderText = "Team"
        dgvPlayers.Columns(4).HeaderText = "Birth Date"
        dgvPlayers.Columns(5).Visible = False
        txtPlayerID.DataBindings.Add("Text", dtPlayers, "PlayerID", False, DataSourceUpdateMode.Never)
        txtPlayerFirstName.DataBindings.Add("Text", dtPlayers, "PlayerFirstName", False, DataSourceUpdateMode.Never)
        txtPlayerName.DataBindings.Add("Text", dtPlayers, "PlayerName", False, DataSourceUpdateMode.Never)
        lstPlayerTeam.DataBindings.Add("Text", dtPlayers, "PlayerTeam", False, DataSourceUpdateMode.Never)
        dtpPlayerBirthdate.DataBindings.Add("Text", dtPlayers, "PlayerBirthDate", False, DataSourceUpdateMode.Never)
        'lstPlayerTeam.ValueMember(
    End Sub

    Private Sub btnPlayersInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlayersInsert.Click
        Try
            Dim Answer As String
            Answer = MsgBox("Are you sure you want to insert this record?" & Environment.NewLine & "Name =         " & txtPlayerName.Text _
                            & Environment.NewLine & "First Name = " & txtPlayerFirstName.Text, MsgBoxStyle.Question + MsgBoxStyle.YesNo)
            If Answer = vbNo Then Exit Sub

            Dim sqlinsert As String
            sqlinsert = "Insert into PLAYERS( PlayerName, PlayerFirstName, PlayerTeam, PlayerBirthDate) VALUES ( @PLAYERNAME, @PLAYERFIRSTNAME, @PLAYERTEAM, @PLAYERBIRTHDATE)"
            Dim cmd As New OleDb.OleDbCommand(sqlinsert, connectie)
            cmd.Parameters.Add(New OleDb.OleDbParameter("@PlAYERNAME", txtPlayerName.Text))
            cmd.Parameters.Add(New OleDb.OleDbParameter("@PLAYERFIRSTNAME", txtPlayerFirstName.Text))
            cmd.Parameters.Add(New OleDb.OleDbParameter("@PLAYERBIRTHDATE", dtpPlayerBirthdate.Value.Date))
            cmd.Parameters.Add(New OleDb.OleDbParameter("@PLAYERTEAM", lstPlayerTeam.SelectedValue))
            connectie.Open()
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error during insert")

        Finally
            connectie.Close()

        End Try
        ReadPlayers()
    End Sub


End Class

Could you please tell me how I can make the datagridview display the TEAMNAME (from teams) instead of the TEAMID (from players), based on the code above? (And make the TEAM updateable in the listview to the right?)

I've also added a screenshot of the DESIGN view.

 

[jmcilhinney]

I suggest that you follow the link in post #2 and follow the instructions it provides. It tells you, step by step, exactly what to. Forget your project for the time being and follow the instructions precisely. Once you see it in action, hopefully it will sink in.

 

[Svekke]

Hi jmcilhinney,

I think it worked now. The datagridview contains a listbox with the correct teamname in it. To the right, I have a couple of textboxes which are correctly bound to the data selected in the datagridview (cheers!)

However, and this might be a very stupid question, but I'm not able to fill a combox :-(

So I also have a combobox below the textboxes. In the combox, there is the teamname als displaymember, and the teamid as datamember. This works fine, except that it is not related to the data in the datagridview yet.

This is the code I have:

cmbPlayerTeam.Displaymember = "TeamName"
cmbPlayerTeam.ValueMember = "TeamID"
cmbPlayerteaM.Datasource= bsTeam

So indeed this shows all the possible teams, but how can I link them? I can link them with .DATABINDINGS.ADD , but then it shows the ID, not the name. How can I make the combobox show the Name as displaymember, but the value as datamember AND linked to the data in the datagridview?

 

[jmcilhinney]

Again, the link I posted earlier tells you exactly what to do. Stage 2 relates to populating the combo boxes.

 

[Svekke]

Hi jmcilhinney,

It works now, I had a small piece of code somewhere that would overwrite the settings again.


So basically everything seems to work now, except creating new records (updating, deleting, .. I haven't done them yet).

So here is the problem:

1) My Combobox is bound to the value from the datagridview. If I want to insert a new record, I get the error 'Datatype mismatch'.

My insert uses parameters, something like

cmd.parameter.add(...)

This works for all text and date fields. But for the combobox, it does not.


If I debug cmbPlayerTeam.selectedvalue.toString() , it gives me the ID (in String format). If I convert this to Int32, I would think I got this ID in number format. If I try to insert it into ACCESS however, I get the datatype mismatch.

The datatype in ACCESS is NUMBER (Long Integer). To which datatype should I cast the SELECTEDVALUE.TOSTRING then? I already tried INT32, DOUBLE, ... but they all seem to fail :-(

 

[jmcilhinney]

You shouldn't have to cast anything.It should all work automatically. The data in the two tables in Access should be related via a foreign key, so the data types must match. The data in your DataTables should all be the same types as in the database and related via the same foreign key. Whenever you make a change in combo box cell, the appropriate value is automatically pushed to the DataTable bound to the grid. You then just call Update on a DataAdapter to save the data. There is no casting and you never actually touch the data in code. If you think you have to then you're doing something wrong. I'm not sure how many times I can say the same thing but if you simply follow the instructions in the link I posted then it will work. You simply have to swap out the code that creates the DataTables for your own ADO.NET code that queries the database. If your database is constructed correctly then it wilol just work.

 

[Svekke]

Hi JMCILHINNEY,

It works now. The combobox wasn't the problem at all. It was the datetimepicker. It was sending a different dateformat then ACCESS. So the access database was the actual error

Kind regards

 

[jmcilhinney]

Again, if you're having problems with dates then you're doing it wrong. You should be using Date/Time columns in Access and DateTime values in VB, in which case there is no date format because the values are always in binary form. Format is only an issue for dates when converting them to or form Strings, which you should absolutely not be doing in this case.