sql - Update Table and Add New Primary index Column

Question

I have a gradebook DB and when I calculate the averages, I dump them into a table in a different DB using the cSQL Statement below. I have two issues - one, I cannot update the new table because it has no primary key. Two, I am trying to add a AutoNumber Column to fix that, but nothing I do seems to add a new column for me. Here is the code - what am I missing?

    cSQL = "Select [StudentID],[StudentLast] as [Last Name],[StudentFirst] as [First Name], [MI], "
    cSQL = cSQL & "AVG(Grade) AS [Average], MAX([Letter]) as [Letter], COUNT([Grade]) as [# Assign] "
    cSQL = cSQL & ",[Subject],[Term] INTO [SubjectAverages] in '" & AppDir & "averages.mdb'"
    cSQL = cSQL & " FROM [Grades] WHERE [Subject] = '" & FixApost(cboSubject.Text) & "' AND [Term] = " & LastTerm
    cSQL = cSQL & " GROUP BY [StudentLast],[StudentFirst],[MI],[StudentID],[Subject],[Term] ORDER BY [StudentLast],[StudentFirst],[MI] ;"

    Try
        'Use cSQL Statement to calculate Averages into table SubjectAverages in DB: 'Averages.mdb'
        OpenDB2()
        da = New OleDbDataAdapter(cSQL, con)
        dt.Columns.Clear()
        dt.Rows.Clear()
        da.Fill(dt)

        'Add New Column
        dt.Columns.Add("Number", GetType(Integer))

        'Are these needed?
        dt.AcceptChanges()
        da.Update(dt)

        con.Close()

    Catch ex As Exception
        Me.Cursor = Cursors.Default
        MessageBox.Show("Error during Chart Update." + ControlChars.CrLf + ex.Message, "Classroom Grader DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        CloseandDispose2()
        Exit Sub
    End Try

question from:https://stackoverflow.com/questions/65929042/update-table-and-add-new-primary-index-column

Answer 1

There's a lot wrong there. Firstly, you can't modify the schema of a database table by adding a column to a DataTable. You need to execute an ALTER TABLE statement against the database. You would do that by creating an OleDbCommand and calling ExecuteNonQuery. To learn exactly how to write such a SQL statement, you should do some research on Jet SQL and the ALTER TABLE statement in particular.

That said, if it's possible, it would make far more sense to simply open the database in Access and make the schema changes visually.

As for the SQL your actually executing above, you should not be using a data adapter at all. You are moving data directly between databases without intervention by your application so, again, you should be using an OleDbCommand and calling ExecuteNonQuery. If you were pulling the data into your app in one step and then saving it to the other database in another step the a data adapter and a DataTable would be the right choice, although the code structure would need significant changes.