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

score 0 · Answer 1 · answered Jan 28 '21 at 00:25

0

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.

answered Jan 28 '21 at 00:25

jmcilhinney

50,448
5
26
46

Thanks for the suggestions - I am not an expert by far. I just love coding and am teaching myself as I go along. My program is a Gradebook, and in the code above, I am just trying to summarize the grade data as averages to display on a datagridview. I don't need to save it since it will always change as more grades get added. I just need to capture the data, display it (and/or print it via Crystal Reports) and that's it. I was dumping it into a "temp" database that I could delete when the "dataviewgrid" form is closed. Any suggestions are ALWAYS welcome! – Michael Allen Jan 29 '21 at 04:30
Is that temp database really of any use? You can just query the existing database and populate a `DataTable` in your app, then use that local cache in whatever way you see fit, e.g. bind to a `DataGridView`. Unless you need to query the temp database from an application than the the current one, it seems not to serve a useful purpose. – jmcilhinney Jan 29 '21 at 06:35
Thanks! I'll look into that and see how works. I'll have to do some research! – Michael Allen Jan 29 '21 at 14:00

Update Table and Add New Primary index Column

1 Answers1