I'm working on a project for a VB class where I connect a access database to the VB project and separate the data for display. I'm having a hard time finding the specific line code that would get this done.
All I'm trying to do is to separate a column of ages into 3 groups and then put that info into 3 text lines.
I know loops pretty good but I can't find the exact code wording to go to the database and parse that out...
Here's all the code so far...
Public Class frmSportsLeague
Private Sub TeamBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TeamBindingNavigatorSaveItem.Click
Me.Validate()
Me.TeamBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.LittleLeagueDataSet)
End Sub
Private Sub frmSportsLeague_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'LittleLeagueDataSet.Team' table. You can move, or remove it, as needed.
Try
TeamTableAdapter.Fill(Me.LittleLeagueDataSet.Team)
Catch
MsgBox("The database is not there!!!")
End Try
End Sub
Private Sub btnAge_Click(sender As Object, e As EventArgs) Handles btnAge.Click
'strSQL is an SQL statement that selects and imports all fields from the gardener database
Dim strsql As String = "SELECT * FROM Team"
Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source = C:\Windows\Temp\LittleLeague.accdb"
Dim odaLittleLeague As New OleDb.OleDbDataAdapter(strsql, strPath)
Dim datPlayers As New DataTable
Dim intCount As Integer
Dim intTwelve As Integer = 0
Dim intThirteen As Integer = 0
Dim intFourteen As Integer = 0
Dim decAllAges As Decimal = 0D
Dim decAverage As Decimal = 0D
Dim intPlayers As Integer
btnAge.Visible = False
'The datCost DataTable gets the table data imported
odaLittleLeague.Fill(datPlayers)
'The connection to the database is closed
odaLittleLeague.Dispose()
For intCount = 0 To datPlayers.Rows.Count - 1
decAllAges += Convert.ToDecimal(datPlayers.Rows(intCount)("Age"))
Next
intPlayers = datPlayers.Rows.Count
decAverage = decAllAges / intPlayers
lblTwelve.Visible = True
lblTwelve.Text = "The number of 12 year old players is: " & intTwelve.ToString()
lblThirteen.Visible = True
lblThirteen.Text = "The number of 13 year old players is: " & intThirteen.ToString()
lblFourteen.Visible = True
lblFourteen.Text = "The number of 14 year old players is: " & intFourteen.ToString()
lblAverage.Visible = True
lblAverage.Text = "The average age of all the players is: " & decAverage.ToString("F2")
End Sub
End Class
assuming that the age is stored as text in your database (don't have the database specs, but I can see that you have a conversion to decimal in your code) adding a "select case" to your code should do the trick. The modified loop:
For intCount = 0 To datPlayers.Rows.Count - 1
decAllAges += Convert.ToDecimal(datPlayers.Rows(intCount)("Age"))
Select Case (datPlayers.Rows(intCount)("Age"))
Case "12"
intTwelve += 1
Case "13"
intThirteen += 1
Case "14"
intFourteen += 1
Case Else
MsgBox("Error: Age not in range 12-14!")
End Select
Next