getting specific column data from access into VB2013

Question

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

Answer 1

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