SQL calculation within VB.Net not working

Question

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!

hits is a column name ,and atBats is a column name. I am trying to find batting average by dividing hits by atBats. So it should be a calculation. Should I try creating a new column by writing SELECT name, hits/atBats as batting average FROM Players? — user3029567
– user3029567, Commented Nov 25, 2013 at 0:41
I added what the database looks like so you can see the atBats and hits columns — user3029567
– user3029567, Commented Nov 25, 2013 at 0:43

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2013-11-25 01:02:59Z

1

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"))

edited Nov 25, 2013 at 1:02

answered Nov 25, 2013 at 0:46

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pete Over a year ago

VB.Net does not actually require the Item property. dt.Rows(i)("hits") / dt.Rows(i)("atBats") is perfectly valid syntax.

Ňɏssa Pøngjǣrdenlarp Over a year ago

I know. I was explicit it to help the OP understand the internal structure/contents of the DT and obj refs in general. It was kind of worded wrong though, thanks.

user3029567 Over a year ago

Thank you! I was getting very frustrated

peterm · Accepted Answer · 2013-11-25 01:19:30Z

0

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 |

answered Nov 25, 2013 at 1:19

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

SQL calculation within VB.Net not working

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related