Why does my vb.net code not change my Access2007 database-CodePudding

I have an Access database2007 and trying to edit the content with the code shown beneath. The code is running without exceptions, but original data are not changed. The data base has a primary key (ID) and further about 20 columns. Using the same data base and adding new rows with Adapter.Update(ds) works ok - (not shown here). Now I have written a minimal code to change content in a specific column (=4) to see whats going wrong, but no error is shown and the database is not altered. I'm using VS 2019 for an .exe application.

Any suggestions what is wrong and/or how to fix it? /Kurt J

 Private Sub Button2_Click_(sender As Object, e As EventArgs) Handles Button2.Click
        'testa att uppdatera databas
        cnnTävl = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="   TPath   AktTävlNamn   "_"   AktTävlDat   ".mdb")
        Dim strSQL = "Select * FROM Deltagare WHERE Patrull='12' AND Figur='01'"
        Dim cmd As New OleDb.OleDbCommand(strSQL, cnnTävl)
        Dim Adapter As New OleDb.OleDbDataAdapter(cmd)
        Dim ds As New DataSet
        Dim cb As New OleDb.OleDbCommandBuilder(Adapter)
        Stop
        Try
            cnnTävl.Open()
            Adapter.Fill(ds)
            If ds.Tables(0).Rows.Count = 1 Then
                ds.Tables(0).Rows(0).BeginEdit()
                ds.Tables(0).Rows(0).Item(4) = "Bo Ek"
            Else
                MsgBox("No rows to edit")
                cnnTävl.Close()
                Exit Sub
            End If
            cb.GetUpdateCommand()
            Adapter.Update(ds)
        Catch Er As Exception
            MsgBox("Er= "   Er.Message)
        End Try
        cnnTävl.Close()
    End Sub

CodePudding user response：

As per @LarsTech's comment:

I've never worked with adapters, but you have a BeginEdit() without a corresponding EndEdit(). Adapter.Update(ds) should return the number of rows affected, so don't ignore that.

EndEdit() solved the problem. All of the examples I saw on web did not use EndEdit(), but maybe the examples were too old.

Thank you Lars for the help.

CodePudding user response：

Connections and commands use unmanaged resources that need to be released. Their Dispose methods do this. The Dispose method must be called; fortunately we have Using...End Using blocks that handle this for us and also closes the connection.

You don't need to call down all the data with a DataAdapter and DataSet. Just update directly.

Private Sub Button2_Click_(sender As Object, e As EventArgs) Handles Button2.Click
    'testa att uppdatera databas
    Dim strSql = "Update Deltagare Set NameOf5thColumn = 'Bo Ek';"
    Dim RecordsEffected As Integer
    Using cnnTävl = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="   TPath   AktTävlNamn   "_"   AktTävlDat   ".mdb"),
            cmd As New OleDb.OleDbCommand(strSql, cnnTävl)
        Debug.Print(cnnTävl.ConnectionString)
        Try
            cnnTävl.Open()
            RecordsEffected = cmd.ExecuteNonQuery()
        Catch Er As Exception
            MsgBox("Er= "   Er.Message)
        End Try
    End Using
    If RecordsEffected = 1 Then
        MessageBox.Show("Success!")
    Else
        MessageBox.Show("Failure!")
    End If
End Sub