Larry_T
asked on
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.ConnectionStrin g = "Provider=Microsoft.Jet.OL EDB.4.0;Pe rsist Security Info=False;Data Source = " & System.AppDomain.CurrentDo main.BaseD irectory & "\dbOrders.mdb"
conConnect.Open()
cmdCommand.Connection = conConnect
cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,F ROM CustomerInfo;"
daDataTransfer.SelectComma nd = cmdCommand
daDataTransfer.Fill(dtCust omerTemp)
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(daData Transfer)
drRows = daDataTransfer.Update(dtCh anged)
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!
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.ConnectionStrin
conConnect.Open()
cmdCommand.Connection = conConnect
cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,F
daDataTransfer.SelectComma
daDataTransfer.Fill(dtCust
dtCustomerTemp.DefaultView
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(daData
drRows = daDataTransfer.Update(dtCh
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!
Hi Larry_T,
are you sure that this line is executed : SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,F ROM CustomerInfo; ?
i think it should be:
SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo FROM CustomerInfo;
HTH
B..M
are you sure that this line is executed : SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,F
i think it should be:
SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo FROM CustomerInfo;
HTH
B..M
ASKER
SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo FROM CustomerInfo;
Sorry that was a typo on my behalf.... I actually took out a few of the fields for the SQL statement.
I know the SQL statement works, because the datagrid is displaying the information properly. It's in the dataAdapter Command where I think the problem is- it crashes is there's any changes.
Praneetha, how would I change the SELECT statement to include the Primary Key information?
Sorry that was a typo on my behalf.... I actually took out a few of the fields for the SQL statement.
I know the SQL statement works, because the datagrid is displaying the information properly. It's in the dataAdapter Command where I think the problem is- it crashes is there's any changes.
Praneetha, how would I change the SELECT statement to include the Primary Key information?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>You can also do it manually through the oleDbAdapter.InsertCommand .UpdateCommand and .InsertCommand properties.
I forgot to mention that I am using OLEDB. I've read many articles so far but none of the implementations seem to work properly....
any suggestions? :)
I forgot to mention that I am using OLEDB. I've read many articles so far but none of the implementations seem to work properly....
any suggestions? :)
ASKER
Thanks- I got it.
I forgot to include the Table Column that had the Primary key... because I didn't want the viewer to be able to overwrite it.
I forgot to include the Table Column that had the Primary key... because I didn't want the viewer to be able to overwrite it.
change the select command to contain the primary key ...liek customer Id.
good luck