-
Aug 4th, 2020, 11:42 PM
#1
Thread Starter
Lively Member
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 | | | |
+----------+--------------+------+-----+---------+----------------+
-
Aug 5th, 2020, 07:12 AM
#2
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
-
Aug 5th, 2020, 09:27 AM
#3
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.
-
Aug 5th, 2020, 09:52 AM
#4
Re: Results rankings database Help Windows Forms Application, VB.Net
Originally Posted by si_the_geek
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
-
Aug 5th, 2020, 10:20 AM
#5
Thread Starter
Lively Member
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.
-
Aug 5th, 2020, 10:39 AM
#6
Re: Results rankings database Help Windows Forms Application, VB.Net
Originally Posted by kragen2179
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).
Originally Posted by kragen2179
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
-
Aug 5th, 2020, 08:25 PM
#7
Thread Starter
Lively Member
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
-
Aug 5th, 2020, 11:02 PM
#8
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
-
Aug 12th, 2020, 12:16 AM
#9
Thread Starter
Lively Member
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
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.
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.
-
Aug 12th, 2020, 01:15 AM
#10
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.
-
Aug 12th, 2020, 08:50 AM
#11
Re: Results rankings database Help Windows Forms Application, VB.Net
The new schema looks good
Originally Posted by kragen2179
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.
-
Aug 12th, 2020, 12:12 PM
#12
Thread Starter
Lively Member
Re: Results rankings database Help Windows Forms Application, VB.Net
Originally Posted by si_the_geek
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.
-
Aug 15th, 2020, 12:52 AM
#13
Thread Starter
Lively Member
Re: Results rankings database Help Windows Forms Application, VB.Net
Originally Posted by ChrisE
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.
-
Aug 15th, 2020, 01:15 AM
#14
Thread Starter
Lively Member
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?
-
Aug 15th, 2020, 01:33 AM
#15
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
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.
-
Aug 15th, 2020, 01:44 AM
#16
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|