OdbcCommandBuilder for MySQL

[adi rusu]

Hi, From C# I'm using an OdbcCommandBuilder to generate DELETE, INSERT and UPDATE commands for updating a MySQL table and I receive the following error: "Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information." I thought this was because the table had no primary key. But I checked and it does. Does anyone know another reason why I get this error? Thanks

[AhsanS]

I think that this error is because you are passing a delete command for select statement. Can you please post your code for me to look into?

[adi rusu]

Nope, I'm pretty sure it's not that. But here's the code anyway. OdbcDataAdapter adapter = new OdbcDataAdapter(); OdbcCommand comm = new OdbcCommand("SELECT * FROM `" + tableName + "`", connection); adapter.SelectCommand = comm; OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); I suppose the insert and update commands don't work either but I get the exception when trying to generate the delete command.

[AhsanS]

adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); In the above lines of code you are trying to get the delete and update commands, but you have not assigned them any where. You have only assigned select command by using adapter.SelectCommand = comm; I hope it helps sort out your problem

Ahsan Ullah Senior Software Engineer

[adi rusu]

The Get{Delete,Insert,Update}Command() methods analyze the select command of the data adapter, and generate OdbcCommand objects according to the data returned by the select command. I've checked their CommandText properties during Debug and they seem ok. If I didn't assign them, the error would be something like "use of unassigned command" or another message, but not the message that I get. If you meant something else by assigning the commands, please give me an example. The problem is not with the code but with the database. I know this because it works on one database but it fails on another database with exactly the same structure but from another computer. Could it be the MySql version? Adrian Junior Developer

[AhsanS]

do a thing comment out the update and delete options and test if select is working alone? If so then the second thing you could check is that you are passing primary key for update and delete commands.

Ahsan Ullah Senior Software Engineer

[adi rusu]

I tried not calling GetDeleteCommand(), GetInsertCommand() and GetUpdateCommand() some time ago and it didn't work. How do I check if I'm passing primary key? Should I adapter.Fill() a table and check if there is a primary key? I can do that. I can generate the commands manually too (but I'd like to avoid that). I'm just trying to understand why the same code works just fine on one computer, but it doesn't on another computer with the same operating system and the same database structure. Would you happen to know if on older versions of MySql, the "select * from <table_name>" command doesn't return key column information even if there are keys in the table?