I'm aware that if I don't have a primary key defined, the commandbuilder cannot generate the Update command. This makes sense... But how can I override this so I can do a dataAdapter.Update(_dataTa
ble) when I don't have access to the database?
The situation is that I'm trying to update an existing database with new data, and I can't change the structure of the tables. The table currently doesn't have any primary keys. I am doing a racetrack comparison of the existing data vs. the new data and applying changes and adding rows where necessary. This all works fine in memory, when when I try to Update the datatable back to the database, it fails *1 (obviously because it can't create the update statement dynamically.
So, my initial queistion is: Can I tell the commandbuilder what columns to use as the primary key? If so, How?
Also, I tried to generate the SQL myself (which was easy enough *2) and I added it to the dataAdapter. But, it is telling me that I don't have the scalar variables for the sql defined *3. How can I build the sql so the parameters are just taken "order specific" so the datatable can use my SQL?
Here is the code I'm trying to get working *4. You can assume that there are pending changes to the datatable, which was populated from a table without a primary key defined. You can also assume that the GenSqlUpdate() statement generates valid SQL... The SQL just doesn't have the parameters defined...
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
UPDATE tablename SET code = @code, full_code = @full_code WHERE id = @id AND type = @type
"ERROR  [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@code"."
Public Sub ApplyChanges()
Dim builder As New Odbc.OdbcCommandBuilder(_oda)
Catch ex As Exception
_oda.UpdateCommand = New OdbcCommand(GenSqlUpdate())