[RESOLVED] Update problem

[bezaman]

OleDbException Unhandled
I can't figure out why.

    Private cnxnString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VDM\AIDS Action Committee\AAC047\AAC047.MDB")
    Private cnxn As New System.Data.OleDb.OleDbConnection(cnxnString)
    Private DataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [COMBINED NCOA]", cnxn)
    Private CommandBuilder As New System.Data.OleDb.OleDbCommandBuilder(DataAdapter)
    Private Table As New DataTable

    Private Sub ButtonCheckDigit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonCheckDigit.Click
        DataAdapter.Fill(Table)
        DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand
        DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        For Each row As DataRow In Table.Rows
            row("TEST") = "XXX"
            DataAdapter.Update(Table)
        Next

        MsgBox("Done")
    End Sub

[jmcilhinney]

First up, you don't call Update after each change. You change the lot and then call Update once.

As for the error, perhaps ab bit of information about the error, which the IDE has provided to you to help you diagnose the problem, could be provided to us to help us diagnose the problem. That said, setting MissingSchemaAction after calling Fill is of no use, so that might be a problem. Your setting of the UpdateCommand is also of no use.

[bezaman]

When i wrap the code into a try catch the exception is: No value given for one or more required parameters.

i deleted the setting of the update command
i moved the MissingSchemaAction to before calling the fill
i moved the DataAdapter Update after the For Each loop

still the same exception. i think i'm going crazy. i feel like i've done this a hundred times before without any problems. not a good morning.

[si_the_geek]

There is a generic list of things to check for that error in the article What does this error mean, and how do I fix it? from our Database Development FAQs/Tutorials

I suspect the issue in this case is that one of your field names is a reserved word (or similar).

[bezaman]

OK. i figured it out. At first i thought my field name "test" was a reserved word. I changed the name but still got the same error. then i changed the name of my table from "combined ncoa" to "combinedncoa" and it worked even with a field name of "test". so it did not like the space in the table name.