My first task in this assignment is to create a datagrid view that can be used to fill in grades for students after each exam.... This is what I've coded so far - no Visual Studio assist used in this - and it works until I go to save the changes back to the database. It errors at
dataAdapter.Update(dt)with a System.InvalidOperationException. Dynamic SQL generation is not supported against multiple base tables. I believe I need to edit my dataAdapter, but not quite sure how. Anyone have any ideas?
Public Class Form1
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=GB.MDB"
Dim sqlStr As String = "SELECT Students.socSecNumber AS SSN, Students.firstName AS [First Name], Students.lastName AS [Last Name], Grades.firstExam AS [Exam 1], " & _
"Grades.secondExam AS [Exam 2], Grades.finalExam AS [Final Exam]" & _
"FROM (Grades INNER JOIN Students ON Grades.socSecNumber = Students.socSecNumber)"
Dim dt As New DataTable()
'This button will load the database into the DataGridView display.
Private Sub btnLoadDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadDB.Click
dt.Clear() 'Clear contents
'Fill table with database data
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
dgvDisplay.DataSource = dt 'Display table
End Sub
'This button will save changes made in DGV back to the database
Private Sub btnSaveDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveDB.Click
Dim changes As Integer
'Open connection for updating
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)
'Update database with changes
changes = dataAdapter.Update(dt)
dataAdapter.Dispose()
'Display number of changes made
If changes > 0 Then
MessageBox.Show(changes & " changed rows were successfully stored.")
Else
MessageBox.Show("No changes were made.")
End If
End Sub
End Class