Results 1 to 16 of 16

Thread: Results rankings database Help Windows Forms Application, VB.Net

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Results rankings database Help Windows Forms Application, VB.Net

    TL: DR - I need some pointers on my Database structure and suggestions how to proceed thereafter.


    Hi as a Formula 1 fan, there is always discussion about who is the greatest. with many results citing number of World Championship Titles or overall points as their sources.

    Since the way Championships have been scored over the years has changed, it is possible that different drivers might have taken the World Championship if modern points scoring rules had been applied. At the very least, All Time Points will be very different if all races were scored using the 2020 points system.


    What I need to be able to do...

    For each Race

    Select Race Name, Year, Number of Laps and if it was a Half Points Race:
    e.g. [ 2020 ] [ British GP ] [ 52 ] [ N ]

    Input entrants to the race (Entrant = Driver and Constructor/Team) their Final Position, Laps Completed and if they recorded the fastest lap.
    e.g [ Lewis Hamilton ] [ Mercedes ] [ 1st ] [ 52 ] [ N ]

    Score Entrants based on their finishing Position using the scores 25,18,15,12,10,8,6,4,2,1 + 1 point for fastest lap (2020 points system for Formula 1)

    If a driver finishes in the top 10 but has not completed 90% laps then they would be not classified and get 0 points

    Fastest Lap can be shared if two or more drivers record the same time (more common in 1950s when analogue stopwatches were the norm) and the point is divided between them.

    Fastest Lap Point is only shared between drivers who complete 90% race distance.


    For Each Season

    Tally scores for each Driver and Each Team to determine Champions

    All Time
    Tally Total Points for Drivers and Teams to determine All Time Points
    Take Total Points and divide by Races entered to determine Average Points Per Race.
    Tally Championships for Drivers and Teams to determine All Time Championships
    At a later date I might want to tally wins/podiums as a tie breaker but not important now.

    Visualise All Time data in a bar chart race or similar



    Right now I am working on design for the database project, trying to figure out how I should organise my tables, I will come back to the code portion later..

    There is a public API available which contains most of what I need at http://ergast.com/mrd/ the problem with this is that it doesn't hold data for the "Non-Championship" Formula 1 Races that were held, and it also uses the official scoring for the relevant seasons.

    I am willing to manually enter the data the way I want it, but first I need to set up the database and forms to accept the data.

    Below is my thought process - some code and some remarks where I'm not yet sure what to use for code. It will be full of errors but I would appreciate any help

    Code:
    +----------------------+
    | Tables_in_f1db       |
    +----------------------+
    | constructorResults   |
    | constructorStandings |
    | constructors         |
    | driverStandings      |
    | drivers              |
    | gp                   |
    | races                |
    | results              |
    | seasons              |
    | status               |
    +----------------------+
    
    
    constructorResults
    +----------------------+--------------+------+-----+---------+----------------+
    | Field                | Type         | Null | Key | Default | Extra          |
    +----------------------+--------------+------+-----+---------+----------------+
    | constructorResultsId | int(11)      | NO   | PRI | NULL    | auto_increment |
    | raceId               | int(11)      | NO   |     | 0       |                |
    | constructorId        | int(11)      | NO   |     | 0       |                |
    | points               | float        | YES  |     | NULL    |                |
    +----------------------+--------------+------+-----+---------+----------------+
    
    constructorStandings
    +------------------------+--------------+------+-----+---------+----------------+
    | Field                  | Type         | Null | Key | Default | Extra          |
    +------------------------+--------------+------+-----+---------+----------------+
    | constructorStandingsId | int(11)      | NO   | PRI | NULL    | auto_increment |
    | raceId                 | int(11)      | NO   |     | 0       |                |
    | constructorId          | int(11)      | NO   |     | 0       |                |
    | position               | int(11)      | YES  |     | NULL    |                |
    | points                 | float        | NO   |     | 0       |                |
    | wins                   | int(11)      | NO   |     | 0       |                |
    +------------------------+--------------+------+-----+---------+----------------+
    
    constructors.csv
    +----------------+--------------+------+-----+---------+----------------+
    | Field          | Type         | Null | Key | Default | Extra          |
    +----------------+--------------+------+-----+---------+----------------+
    | constructorId  | int(11)      | NO   | PRI | NULL    | auto_increment |
    | cName          | varchar(255) | NO   | UNI |         |                |
    +----------------+--------------+------+-----+---------+----------------+
    
    driverStandings.csv
    +-------------------+--------------+------+-----+---------+----------------+
    | Field             | Type         | Null | Key | Default | Extra          |
    +-------------------+--------------+------+-----+---------+----------------+
    | driverStandingsId | int(11)      | NO   | PRI | NULL    | auto_increment |
    | raceId            | int(11)      | NO   |     | 0       |                |
    | driverId          | int(11)      | NO   |     | 0       |                |
    | position          | int(11)      | YES  |     | NULL    |                |
    | points            | float        | NO   |     | 0       |                |
    | wins              | int(11)      | NO   |     | 0       |                |
    +-------------------+--------------+------+-----+---------+----------------+
    
    drivers
    +-------------+--------------+------+-----+---------+----------------+
    | Field       | Type         | Null | Key | Default | Extra          |
    +-------------+--------------+------+-----+---------+----------------+
    | driverId    | int(11)      | NO   | PRI | NULL    | auto_increment |
    | forename    | varchar(255) | NO   |     |         |                |
    | surname     | varchar(255) | NO   |     |         |                |
    +-------------+--------------+------+-----+---------+----------------+
    
    gp
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | gpId       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | gpname     | varchar(255) | NO   |     |         |                |
    | location   | varchar(255) | YES  |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+
    
    races
    +-----------+--------------+------+-----+------------+----------------+
    | Field     | Type         | Null | Key | Default    | Extra          |
    +-----------+--------------+------+-----+------------+----------------+
    | raceId    | int(11)      | NO   | PRI | NULL       | auto_increment |
    | year      | int(11)      | NO   |     | 0          |                |
    | gpid      | int(11)      | NO   |     | 0          |                |
    | name      | varchar(255) | NO   |     |            |                |
    +-----------+--------------+------+-----+------------+----------------+
    
    results
    +-----------------+--------------+------+-----+---------+----------------+
    | Field           | Type         | Null | Key | Default | Extra          |
    +-----------------+--------------+------+-----+---------+----------------+
    | resultId        | int(11)      | NO   | PRI | NULL    | auto_increment |
    | raceId          | int(11)      | NO   |     | 0       |                |
    | driverId        | int(11)      | NO   |     | 0       |                |
    | constructorId   | int(11)      | NO   |     | 0       |                |
    | position        | int(11)      | YES  |     | NULL    |                |
    | points          | float        | NO   |     | 0       |                |
    | laps            | int(11)      | NO   |     | 0       |                |
    | fastestLap      | int(11)      | YES  |     | NULL    |                |
    | statusId        | int(11)      | NO   |     | 0       |                |
    +-----------------+--------------+------+-----+---------+----------------+
    
    seasons
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | year  | int(11)      | NO   | PRI | 0       |       |
    | url   | varchar(255) | NO   | UNI |         |       |
    +-------+--------------+------+-----+---------+-------+
    
    status
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | statusId | int(11)      | NO   | PRI | NULL    | auto_increment |
    | status   | varchar(255) | NO   |     |         |                |
    +----------+--------------+------+-----+---------+----------------+

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,979

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Interesting idea. As an F1 fan that's wondered similar things, would be interested in seeing where this goes.
    That said... my notes are as follows:
    1) What database system are you using - I see auto_increment, which suggests Access, but not sure.
    2) What is the Stats table for? Depending on what it's holding, it may not be needed.
    3) I wouldn't have a table for ConstructorResults, ConstructorStandings, or DriverStandings... I'd use views for those. Otherwise the updates and maintenance on those tables post-race is icky. With views, you simply select the data, it crunches the numbers, filters by the race, and as long as the results table is filled in, bam! there's your data.
    4) Seasons table - doesn't seem to be used... Also, why is the url a unique key? The year as the pKey, by definition should be unique.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Results rankings database Help Windows Forms Application, VB.Net

    In terms of the database I agree with tg, but I'm assuming the Seasons table is used to keep track of the data source for that year (so you can easily update data if needed).



    It is worth noting that the Fastest Lap point only gets awarded if the driver finishes in the top 10. I am also unsure if the idea of splitting the points for 2+ drivers with the same time, as it should ideally be awarded to just the driver who set the time first (as happens in qualifying), but I suspect it would be hard to get the data required to work it out.

    Another philosophical issue is something said by many drivers (including Lewis Hamilton and Sir Stirling Moss), which is that drivers race based on the circumstances at the time - so for example in the days when points were only awarded to the top 6 finishers drivers wouldn't take big risks to finish 10th, but these days they would.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,979

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Quote Originally Posted by si_the_geek View Post
    In terms of the database I agree with tg, but I'm assuming the Seasons table is used to keep track of the data source for that year (so you can easily update data if needed).
    I figured the same, but didn't see it used anywhere... but I see the year is in the results... so... with the year also being the PKey, I'm not sure what value it adds other than the URL. *shrug*

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Thank you for the responses.

    I am going to be using sql for the database from within a windows forms application (add object > service based database > dataset )

    The url in the seasons is a typo, not even sure if I "need" the seasons table either.

    I am starting completely from scratch here and as such I looked at the schema from http://ergast.com/mrd/ as my starting point for design.

    I would be curious to see if there is an official ruling on fastest lap point being shared or not. Api listed does allow lap time searches so I might be able to discover who gets the point when it was historically shared (by 7 drivers in one race in the 50s)

    Additionally drivers would often share cars mid race and still be awarded points. Under current rules there is nothing to stop drivers sharing a car, but they do not get points. So these drivers will be "not classified"

    Also to score they have to finish 90% race distance. Even some of the top 10 drivers in a race did not do this in the 50s and again would not be classified.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,979

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Quote Originally Posted by kragen2179 View Post
    Thank you for the responses.

    I am going to be using sql for the database from within a windows forms application (add object > service based database > dataset )
    Um, yeah, SQL is a language syntax Structured Query Language is what it stands for... it's not a database system...
    So by saying "I am going to be using sql for the database" ... is a given, since Access uses SQL, as does SWQL Server, Oracle, MySQL, and many others... but it doesn't tell us which DBMS you're planning to use (not that it's totally important, but does have implications on what you'll have at your disposal for solutions later).





    Quote Originally Posted by kragen2179 View Post
    Additionally drivers would often share cars mid race and still be awarded points. Under current rules there is nothing to stop drivers sharing a car, but they do not get points. So these drivers will be "not classified"

    Also to score they have to finish 90% race distance. Even some of the top 10 drivers in a race did not do this in the 50s and again would not be classified.
    Then you also have situations like Nico who is just filling in... if I heard right, if he finishes the season, he gets the points from Sergio, plus his own... but if Sergio comes back... Sergio doesn't get any of Nico's points... so it's pretty messed up.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Sorry, I think its SQL Server Express LocalDB, as I said, I add a service based database from the add menu in a windows forms application.

    As for Nico Hulkenberg, Martin Brundle explained it during the British GP.

    Nico only scores points for races he competes in, but the team gets both his and sergio's points from the car towards the constructors championship

  8. #8
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,172

    Re: Results rankings database Help Windows Forms Application, VB.Net

    kragen, in case you didn't know, there is a Formula 1 thread in Chit-Chat. It's a pretty slow thread, but gets a few posts around each race. Another F1 fan would be great to contribute.

    https://www.vbforums.com/showthread....Champion/page5

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    TL: DR
    Is this Schema better (what else do I need to add?)

    What am I doing wrong with my ComboBoxes?

    Have been playing around and have got got to this stage

    Name:  F1.jpg
Views: 51
Size:  27.5 KB

    Tables in F1.mdf
    TblCircuits
    TblConstructors
    TblDrivers
    TblRaces
    TblResults
    TblSeasons
    Code:
    CREATE TABLE [dbo].[TblCircuits] (
        [CirID]   INT          IDENTITY (1, 1) NOT NULL,
        [CirName] VARCHAR (20) NOT NULL,
        PRIMARY KEY CLUSTERED ([CirID] ASC)
    );
    
    
    CREATE TABLE [dbo].[TblConstructors] (
        [ConID]   INT          IDENTITY (1, 1) NOT NULL,
        [ConName] VARCHAR (20) NOT NULL,
        PRIMARY KEY CLUSTERED ([ConID] ASC)
    );
    
    
    CREATE TABLE [dbo].[TblDrivers] (
        [DrvID]    INT          IDENTITY (1, 1) NOT NULL,
        [DrvFName] VARCHAR (20) NOT NULL,
        [DrvLName] VARCHAR (20) NOT NULL,
        PRIMARY KEY CLUSTERED ([DrvID] ASC)
    );
    
    
    CREATE TABLE [dbo].[TblRaces] (
        [RacID]    INT IDENTITY (1, 1) NOT NULL,
        [SeaID]    INT NOT NULL,
        [CirID]    INT NOT NULL,
        [Distance] INT NOT NULL DEFAULT 0,
        PRIMARY KEY CLUSTERED ([RacID] ASC),
        FOREIGN KEY ([SeaID]) REFERENCES [dbo].[TblSeasons] ([SeaID]),
        FOREIGN KEY ([CirID]) REFERENCES [dbo].[TblCircuits] ([CirID])
    );
    
    
    CREATE TABLE [dbo].[TblResults] (
        [ResID]      INT        IDENTITY (1, 1) NOT NULL,
        [RacID]      INT        NOT NULL,
        [HalfPoints] BIT        DEFAULT ((0)) NOT NULL,
        [DrvID]      INT        NOT NULL,
        [ConID]      INT        NOT NULL,
        [Position]   INT        NOT NULL,
        [Laps]       INT        NOT NULL,
        [Classified] BIT        DEFAULT ((1)) NOT NULL,
        [FastLap]    BIT        DEFAULT ((0)) NOT NULL,
        [Points]     FLOAT (53) DEFAULT ((0)) NOT NULL,
        PRIMARY KEY CLUSTERED ([ResID] ASC),
        FOREIGN KEY ([RacID]) REFERENCES [dbo].[TblRaces] ([RacID]),
        FOREIGN KEY ([ConID]) REFERENCES [dbo].[TblConstructors] ([ConID]),
        FOREIGN KEY ([DrvID]) REFERENCES [dbo].[TblDrivers] ([DrvID])
    );
    
    
    CREATE TABLE [dbo].[TblSeasons] (
        [SeaID] INT IDENTITY (1946, 1) NOT NULL,
        [Year]  INT NOT NULL,
        PRIMARY KEY CLUSTERED ([SeaID] ASC)
    );

    In the original schema that I posted, there were also tables for Driver/Constructor Standings. I'm not convinced that I need those provided that I can create a leaderboard from a query. What do you think?

    Assuming the above will work, I have created forms to populate TblCircuits, TblConstructors, TblDrivers, TblRaces and Tbl Seasons.

    Now here is where it is getting tricky...

    First [TblRaces] should combine [SeaID] from [TblSeasons] with [RacID] from [TblRaces] and Race Distance [Distance] so that the RacID can be called later.

    However when I run the app as it stands I am having issues. Once I have data to the tables [TblCircuits] and [TblSeasons] in their respective forms, then I open the FrmRaces to create a race and add a new record.

    When I click add new record,

    [RacID] Label autoincrements (check)
    [SeaID] ComboBox DropDown can select Season (check)
    [CirID] ComboBox DropDown can select Circuit (ERROR) - I can drop the box down and click on any of the circuits I have added to [TblCircuits] but I CANNOT click out of the ComboBox.
    Name:  Error.jpg
Views: 50
Size:  19.3 KB

    Both ComboBoxes are set up in the same method so I don't know why one works and the other does not.

    For Each ComboBox I clicked on the tiny arrow to open the ComboBox Tasks window and checked [Use Data Bound Items]

    for [CBxSeasons]
    Data Source: TblSeasonsBindingSource
    Display Member: Year
    Display Value: SeaID
    Selected Value: TblRacesBindingSource - SeaID

    for [CBxCircuits]
    Data Source: TblCircuitsBindingSource
    Display Member: CirName
    Display Value: CirID
    Selected Value: TblRacesBindingSource - CirID

    I swapped the ComboBox for a ListBox with the same constraints and it worked perfectly but I want it to drip down like the ComboBox.

    I even tried changing the ComboBox DropDown to DropDownList still didn't work.
    Last edited by kragen2179; Aug 12th, 2020 at 01:02 AM.

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,579

    Re: Results rankings database Help Windows Forms Application, VB.Net

    here a Sub that you can use to fill Comboboxes

    put this in a Modul
    Code:
     Public Sub FillCombo(ByVal comboBox As ComboBox, _
                              ByVal sql As String, _
                              ByVal valueMember As String, _
                              ByVal displayMember As String)
            Using sda As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
                Dim dt As New DataTable()
                sda.Fill(dt)
                comboBox.ValueMember = valueMember
                comboBox.DisplayMember = displayMember
                comboBox.DataSource = dt
            End Using
        End Sub
    and then in the Form Load
    Code:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    'adjust to your Table
            Module1.FillCombo(ComboBox1, _
                            "SELECT CategoryName,Categories.CategoryID FROM Categories ORDER BY CategoryName ASC", _
                            "CategoryID", _
                            "CategoryName")
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Label1.Text = ComboBox1.SelectedValue
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Results rankings database Help Windows Forms Application, VB.Net

    The new schema looks good
    Quote Originally Posted by kragen2179 View Post
    In the original schema that I posted, there were also tables for Driver/Constructor Standings. I'm not convinced that I need those provided that I can create a leaderboard from a query. What do you think?
    In simple terms that would be fine, but unfortunately there have been times that drivers and/or constructors have been penalised for rule violations (such as Schumacher in about 1997) and docked points.

    Given your intentions with the system, I don't know if that is something you would want to take account of or not.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Quote Originally Posted by si_the_geek View Post
    The new schema looks good
    In simple terms that would be fine, but unfortunately there have been times that drivers and/or constructors have been penalised for rule violations (such as Schumacher in about 1997) and docked points.

    Given your intentions with the system, I don't know if that is something you would want to take account of or not.
    I will be manually filling the race results once I have the database set up. So where drivers were docked points I can apply this as I go.

    Similarly back in the 1950's a driver could retire his car and take over his teammate's car to win the race. Then Both driver's were credited with the position, but now - while it's is not prohibited to swap driver's in a car during the race - those drivers/cars would not score points. Example, in In the 1954 German Grands Prix Mike Hawthorn retired with a broken Axle on lap 3, but took over from his teammate José Froilán González on lap 16. Both Drivers were credited with points for 2nd place (which were divided between them) And Maurice Trintignant was given 3rd.

    When I come to input this race, Mike Hawthorn and José Froilán González would become "Not Classifeid" as per 2020 rules and Maurice Trintignant would move up to 2nd (and all other driver's would move up one place also). I intend to manually apply this rule every time cars were shared. Or if a driver/team has their points removed later by the FiA they will be moved down and other teams/drivers will move up.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Quote Originally Posted by ChrisE View Post
    here a Sub that you can use to fill Comboboxes

    put this in a Modul
    Code:
     Public Sub FillCombo(ByVal comboBox As ComboBox, _
                              ByVal sql As String, _
                              ByVal valueMember As String, _
                              ByVal displayMember As String)
            Using sda As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
                Dim dt As New DataTable()
                sda.Fill(dt)
                comboBox.ValueMember = valueMember
                comboBox.DisplayMember = displayMember
                comboBox.DataSource = dt
            End Using
        End Sub
    and then in the Form Load
    Code:
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    'adjust to your Table
            Module1.FillCombo(ComboBox1, _
                            "SELECT CategoryName,Categories.CategoryID FROM Categories ORDER BY CategoryName ASC", _
                            "CategoryID", _
                            "CategoryName")
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Label1.Text = ComboBox1.SelectedValue
        End Sub
    Thank you. I had trouble with that code. But it showed me how to work around my problem.

    Instead of pulling the ComboBoxes from the table I want to fill, I left those fields as labels and pulled ComboBoxes from the tables I'm referencing.

    Then I used the databinding window to select the DisplayMember and ValueMember properties.

    Then double click the ComboBox and added this line of code
    Code:
    Label1.Text = ComboBox1.SelectedValue
    now it works.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    OK so onto the results form.

    I can easily create a form to fill in the results one by one, line by line. But, is it possible to fill multiple records in one click?

    So instead of [add record] "input data" [save record] repeat.

    Could I select constants such as year, circuit and race distance and then add the number of entrants to have it generate a list where I only have to select drivers/teams in their finish order and the position/ points fields are auto filled when the page is generated?

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,579

    Re: Results rankings database Help Windows Forms Application, VB.Net

    I will chip in some Code for this Project as I am a F1 Fan.

    here a way to display Values into a Calendar
    I kept this as simple as possible, a Table with 3 Fields for the Races each Year.

    you'll need a Modul, Datagridview and a Combobox

    the Module
    Code:
    Imports System.Reflection
    
    Module modHelper
        Public Sub DoubleBuffered(ByVal dgv As DataGridView, ByVal setting As Boolean)
            Dim dgvType As Type = dgv.[GetType]()
            Dim pi As PropertyInfo = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance Or BindingFlags.NonPublic)
            pi.SetValue(dgv, setting, Nothing)
        End Sub
    End Module
    all of this in a Form
    Code:
    Option Strict On
    Imports System.Data.OleDb
    
    
    Public Class calVertical
    
        Dim ActivYear As Integer
    
        Private Sub calVertical_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            modHelper.DoubleBuffered(DataGridView1, True)
            With DataGridView1
                .RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
                .ScrollBars = ScrollBars.None
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .RowHeadersVisible = False
            End With
            ActivYear = If(ActivYear = 0, Year(Now), ActivYear)
            IniCombos()
        End Sub
    
        Sub IniCombos()
            For i As Integer = 1999 To 2099
                cboYear.Items.Add(i)
            Next
            cboYear.SelectedIndex = CInt(ActivYear - 1999)
        End Sub
    
        Private Sub MakeHeader()
            Dim col As New List(Of String)
            col.Add("Jan")
            col.Add("Feb")
            col.Add("Mar")
            col.Add("Apr")
            col.Add("May")
            col.Add("Jun")
            col.Add("Jul")
            col.Add("Aug")
            col.Add("Sep")
            col.Add("Oct")
            col.Add("Nov")
            col.Add("Dec")
            With DataGridView1
                Dim Ft As New Font("Tahoma", 8)
                .Font = Ft
                For i As Integer = 1 To 12
                    Dim key As String = "C" & i.ToString("00")
                    .Columns.Add(key, col(i - 1))
                    .Columns(key).Width = 40
                    .Columns(key).SortMode = DataGridViewColumnSortMode.NotSortable
                Next
            End With
            DataGridView1.Rows.Add(31)
            DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End Sub
    
        Private Sub MakeKalendar(ByVal ActiveYear As Integer)
            With DataGridView1
                For i As Integer = 1 To 12
                    Dim d As Date = New Date(ActiveYear, i, 1)
                    For j As Integer = 1 To Date.DaysInMonth(ActiveYear, i)
                        Dim col As Integer = (i - 1)
                        .Rows(j - 1).Cells(col).Value = d.ToString("ddd dd")
                        .Rows(j - 1).Height = 20
                        If d.DayOfWeek = DayOfWeek.Saturday Or d.DayOfWeek = DayOfWeek.Sunday Then
                            .Rows(j - 1).Cells(col).Style.BackColor = Color.LightSteelBlue
                        End If
                        d = d.AddDays(1)
                    Next
                Next
            End With
        End Sub
    
        
        Private Sub AddRacesDates(ByVal nYear As Integer)
    
            'Database Table(tbl_SeasonRaces) Fields:
            'R_No = Autoincrement
            'R_RaceDate = Date
            'Race = Text
    
            Dim sDB As String = "D:\DotNet\Sample DotNet\sample DGV\Cal F1\Formula1.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Dim sSql As String = "SELECT R_RaceDate, R_No, race"
            sSql &= "  FROM tbl_SeasonRaces "
            sSql &= "WHERE R_RaceDate Is Not Null AND Format(R_RaceDate,'yyyy')=@RacesYear;"
    
            Dim Cmd As New OleDb.OleDbCommand(sSql, Cn)
            Cmd.Parameters.AddWithValue("@RacesYear", nYear)
            Cn.Open()
            Dim dr As OleDbDataReader = Cmd.ExecuteReader()
    
            While dr.Read()
                With DataGridView1
                    For i As Integer = 1 To 12
                        Dim d As Date = New Date(nYear, i, 1)
                        For j As Integer = 1 To Date.DaysInMonth(nYear, i)
                            Dim col As Integer = (i - 1)
                            If Format(dr.Item(0), "dd.MM.yyyy") = d.ToString("dd.MM.yyyy") Then
                                Dim race As String = CStr(dr.Item(2).ToString)
                                .Rows(j - 1).Cells(col).Value = race
                                .Rows(j - 1).Cells(col).Style.BackColor = Color.PaleGreen
                            End If
                            d = d.AddDays(1)
                        Next
                    Next
                End With
            End While
            dr.Close()
            Cmd.Dispose()
            Cn.Close()
        End Sub
    
        Private Sub cboYear_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboYear.SelectedIndexChanged
            With DataGridView1
                .Rows.Clear()
                .Columns.Clear()
                .SuspendLayout()
                MakeHeader()
                MakeKalendar(CInt(cboYear.Text))
                AddRacesDates(CInt(cboYear.Text))
                .ResumeLayout()
                .AllowUserToAddRows = False
            End With
    
        End Sub
    
        Private Sub DataGridView1_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            'If DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value IsNot Nothing Then
            '    MessageBox.Show(DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString())
            'End If
           
        End Sub
    
        Private Sub DataGridView1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseUp
            'standard width
            For Each c As DataGridViewColumn In DataGridView1.Columns
                c.Width = 40
            Next
            'change width on selected column
                If e.Button = Windows.Forms.MouseButtons.Left Then
                    Dim hitRow As Integer, hitCol As Integer
                hitRow = e.RowIndex
                hitCol = e.ColumnIndex
                    Dim column As DataGridViewColumn = DataGridView1.Columns(hitCol)
                    column.Width = 120
                End If
        End Sub
    
        Private Sub DataGridView1_MouseLeave(sender As Object, e As System.EventArgs) Handles DataGridView1.MouseLeave
            'set standard width
            For Each c As DataGridViewColumn In DataGridView1.Columns
                c.Width = 40
            Next
        End Sub
    
        Private Sub DataGridView1_CellPainting(sender As Object, e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
            For Each row As DataGridViewRow In DataGridView1.Rows
                For Each cell As DataGridViewCell In row.Cells
                    Dim value As String = Convert.ToString(cell.Value)
                    If String.IsNullOrEmpty(value) Then
                        cell.Style.BackColor = Color.Silver
                    End If
                Next cell
            Next row
        End Sub
    End Class
    here a Image

    Name:  F1.jpg
Views: 31
Size:  103.9 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    64

    Re: Results rankings database Help Windows Forms Application, VB.Net

    Thank you. Really appreciate the input. Not 100% sure the calendar fits the needs of my application but I am grateful nonetheless.

    I will look over the code properly later as I'm off to bed right now.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width