Help needed with Calculated Column with SQL

[Kidneyinacooler]

Please Help

I am attempting to develop a Gradebook that one can use to modify grades and have an average calculated at the end.

When you first open the form, you see the database fields in the DataGrid (socSecNumber, firstExam, secondExam, finalExam)

I have a button to save the changes made here. The exam grades are null in the database.

This seems to work fine, however I have a problem with the calculated column I want to add. I want it to display the socSecNumber and the average for that student.

Instead, I get all four fields in addition to the semAverage field. Also, all the grades come up 250 if I fill all the fields in with 100.

If you need anymore information let me know.
Here is my code.




Public Class frmGradebook

Dim sqlStr As String = "SELECT * FROM Grades"

Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=GRADEBOOK.MDB"
Dim dt As New DataTable()

'Fill the data table and display
Private Sub frmGradebook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
UpdateGrid("Select * From Grades")

End Sub

Private Sub btnClassList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClassList.Click
frmList.Show()
End Sub



Sub UpdateGrid(ByVal sqlStr As String)


Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

dataAdapter.Fill(dt)
dataAdapter.Dispose()
dgvDisplay.DataSource = dt
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim changes As Integer
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
changes = dataAdapter.Update(dt)
dataAdapter.Dispose()

If changes > 0 Then
MessageBox.Show(changes & " student grades were changed in database.")
Else
MessageBox.Show("No changes made.")
End If
End Sub

Private Sub btnDisplayFinal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayFinal.Click
'calculating grade averages
dt.Clear()


Dim sqlStr = "SELECT socSecNumber, Round((firstExam + secondExam) + (finalExam * 2) / 4, 1) AS semAverage FROM Grades"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

dataAdapter.Fill(dt)
dataAdapter.Dispose()


dgvDisplay.DataSource = dt

End Sub

End Class

 

[cjard]

Round((firstExam + secondExam) + (finalExam * 2) / 4, 1)

Is done as:
firstexam
plus second exam
plus finalexam times two divided by four

100
+100
+100*2/4

100
+100
+200/4 (or 100*0.5)

100
+100
+50

this is why you get 250

What on earth is BODMAS?

 

[Kidneyinacooler]

Awesome buttt

Thanks so much! It's now giving me the correct number.

((firstExam + secondExam + (finalExam * 2)) / 4, 1)

However, Does anyone know how to get just one field to show up when the btnDisplayFinal is initiated?

Right now it is showing socSecNumber, firstExam, secondExam, finalExam, and semAverage. I only want the socSecNumber and semAverage.

Also, is there a way to carry the value of semAverage to another procedure? Can i refer to it as semAverage?