vb.net Update database using dataAdapter failing, "Dynamic SQL generation UpdateCommand is not supported..."
Posted on 2004-09-20
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
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"
cmdCommand.Connection = conConnect
cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,FROM CustomerInfo;"
daDataTransfer.SelectCommand = cmdCommand
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)
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
comBuild = New OleDbCommandBuilder(daDataTransfer)
drRows = daDataTransfer.Update(dtChanged)
MsgBox("Updates successful.", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox("Update ERROR: " & ex.Message, MsgBoxStyle.Exclamation)
MsgBox("There was nothing to update.", MsgBoxStyle.Information)