CODEWITHSUNDEEP
sqlvb.net
user3029567
user3029567

Reputation: 3

SQL calculation within VB.Net not working

This is a pre-assignment for a class I'm in. Supposed to be pretty simple and act as a warm-up, but I can't get it working. Basically the code is bringing in a test database and performing a calculation. In this case I'm trying to find the highest average batting average in a set of baseball players.

So my end result should be the name of the player with the highest batting average, or a few players if they are tied for the highest average.

Here is the code:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt As DataTable = New DataTable()
    Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Baseball.accdb"
    Dim sqlStr As String = "SELECT * FROM Players"
    Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlStr, connStr)

    dataAdapter.Fill(dt)
    dataAdapter.Dispose()

    Dim average, pastAverage, highestAverage As Double
    For i As Integer = 0 To dt.Rows.Count - 1
        average = CDbl(dt.Rows(i)("hits/atBats"))
        If average > pastAverage Then
            highestAverage = average
        End If
        pastAverage = average
    Next

    For i As Integer = 0 To dt.Rows.Count - 1
        If dt.Rows(i)("hits/atBats") = highestAverage Then
            lstBoxHighest.Items.Add(dt.Rows(i)("name"))
        End If
    Next

  End Sub
End Class

The debugger won't go past the "average = Cdbl(dt.Rows(i)("hits/atBats"))" line in the first For Loop. Can I not do calculations like that in the loop? I am sure the column titles (hits and atBats are correct)

The database looks like this in case you were wondering:

name              Team              atBats        hits

Derek Jeter       New York Yankees  511           158
Joe Mauer         Minnesota Twins   545           174
etc...

Thanks!

Upvotes: 0

Views: 196

Answers (2)

peterm
peterm

Reputation: 92795

Disclaimer: it's not a direct answer to the question.

Instead of pulling all the data to the client and then using two loops to find a name you can do all calculations on db-side and grab only needed rows and columns (in your case just name) with a query that may look like

SELECT name
  FROM Players
 WHERE atBats / hits = 
(
  SELECT MAX(atBats / hits)
    FROM Players
)

Output:

|        NAME |
|-------------|
| Derek Jeter |

Upvotes: 0

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38875

you are missing some object refs:

average = CDbl(dt.Rows(i).item("hits") / dt.Rows(i).item("atBats"))

and like that for the rest of them. VB needs an dt.Rows(i) reference because those are 2 different columns. With "hits/atBats", it cant know those are individual columns.

Shorthand, but kind of masks that they are different cols/items is like you had it:

average = CDbl(dt.Rows(i)("hits") / dt.Rows(i)("atBats"))

Upvotes: 1

Related Questions