[RESOLVED] [2005] Update Exisiting Record usi ng Dataset

[jeffcravener]

Hello!

I am able to add new records to a database and then update the access database, but i can't get it to update exisitng rows.

Here is the code i tried:

    Private Sub UpdateCPHeaderFooter()
        Dim dacmt As OleDb.OleDbDataAdapter
        Dim cb As OleDbCommandBuilder
        Dim con As OleDbConnection
        Dim sql As String
        Dim r() As DataRow
        Dim dr As DataRow
        Dim arr(4) As String

        r = dsCPHeadFoot.Tables(My.Settings.CMTCPHeadFoot).Select("ID = '" & CPID & "'")
        If r.Length <> 0 Then
            r(0).Item(1) = txtHeader.Text
            r(0).Item(2) = txtFooterLeft.Text
            r(0).Item(3) = txtFooterMid.Text
            r(0).Item(4) = txtFooterRight.Text
        
            con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Settings.CMTCONSTRING & ";")
            sql = "Select * from " & My.Settings.CMTCPHeadFoot
            dacmt = New OleDb.OleDbDataAdapter(sql, con)
            cb = New OleDbCommandBuilder(dacmt)

            dacmt.Update(dsCPHeadFoot, My.Settings.CMTCPHeadFoot)
            dacmt = Nothing
            con.Close()
            con.Dispose()
            dsCPHeadFoot.AcceptChanges()
    End If
    End Sub

Here is the message:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

[jeffcravener]

I figured it out!

I changed the code to the following and it is working:

            dv = New DataView
            dv.Table = dsCPHeadFoot.Tables(My.Settings.CMTCPHeadFoot)
            dv.RowFilter = "ID = '" & CPID & "'"
            With dv.Item(0)
                .Item(1) = txtHeader.Text
                .Item(2) = txtFooterLeft.Text
                .Item(3) = txtFooterMid.Text
                .Item(4) = txtFooterRight.Text
            End With