vb.net Update database using dataAdapter failing, "Dynamic SQL generation UpdateCommand is not supported..."

Hi experts,

I have a datagrid that displays the contents of a database table via a dataAdapter.  I am under the impression that ADO.NET in disconnected mode allows dynamically changing the datagrid contents and writing it back to the original database via the dataAdapter....  However, I keep getting an error: "Dynamic SQL generation UpdateCommand is not supported against a SelectCommand that does not return key column information".

Could someone please take a look at my code and let me know where I went astray?  The initial datagrid view works fine.  Clicking the "SaveChanges" button illicits the correct message box as well, which means the tables and temporary tables are being accessed properly as well....

"dgCustomers" is the DataGrid on the Windows Form.

Windows Form variables:

   Dim daDataTransfer As New OleDbDataAdapter
   Dim dtCustomerTemp As New DataTable

OnLoad():

  Try
            Dim cmdCommand As New OleDbCommand
            Dim conConnect As New OleDbConnection

            conConnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source = " & System.AppDomain.CurrentDomain.BaseDirectory & "\dbOrders.mdb"

            conConnect.Open()

            cmdCommand.Connection = conConnect
            cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,FROM CustomerInfo;"

            daDataTransfer.SelectCommand = cmdCommand
            daDataTransfer.Fill(dtCustomerTemp)

            dtCustomerTemp.DefaultView.AllowNew = False
            Me.dgCustomers.DataSource = dtCustomerTemp
            Me.btnSave.Visible = True

        Catch ex As Exception
            MsgBox("Unable to create DataGrid view.  ERROR: " & ex.ToString, MsgBoxStyle.Information)
        End Try


On Clicking the SaveChanges button():

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim dtChanged As DataTable
        Dim comBuild As OleDbCommandBuilder
        Dim drRows As Integer

        'check to see if there were any changes made
        dtChanged = dtCustomerTemp.GetChanges()

        If Not IsNothing(dtChanged) Then
            Try
                comBuild = New OleDbCommandBuilder(daDataTransfer)
                drRows = daDataTransfer.Update(dtChanged)
                MsgBox("Updates successful.", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("Update ERROR:  " & ex.Message, MsgBoxStyle.Exclamation)
            End Try
        Else
            MsgBox("There was nothing to update.", MsgBoxStyle.Information)
        End If

    End Sub

Thanks!Start Free Trial