Adapter Update Fails, Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'

[William Speirs]

Hello-

I'm new to C#, but I have created an app with a DataGrid control. I've also created a DataSet and DataAdapter, and filled the DataSet using the DataAdapter with a table in my MySQL 4.1 database. I have a button so that I can add a test column to my table. First, is there any good way to do this, or only by typing out the SQL for it? Here is what I have now:

MySqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "alter table testtable add test float";
myCommand.ExecuteNonQuery();
myAdapter.Fill(myDataSet, "testtable");

That part works, but then when I call update (myAdapter.Update(myDataSet, "testtable"); ) I get the following error message:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<=>'

I think it has to do with when I make the new column it is of a different character set and collation then the other columns in the table. However, I haven't a clue as to how to set/change it so that everything is the same. This is a simple app, only using English, so I don't really care about how it does its comparisons.

What is this all about? What is the easiest way to make this work? I'm using a MySqlCommandBuilder so I don't have to write the update command by hand.

I'm on a Windows XP box, using MySQL 4.1 and the .NET connector 1.1.

Thanks in advanace...