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

Also.... While working on another part of the project.... Anyone know how to link a second form to a main form with a button? =P

Also.... While working on another part of the project.... Anyone know how to link a second form to a main form with a button? =P

NVM on this part... figured it out =)

This question has already been answered. Start a new discussion instead.