MySqlCommand : impossible to read inside a transaction

[Hubert CANEVET]

Hello everybody,

Let us suppose I want to get the id of a user, knowing his name, that will be done like that :

conn.Open();

MySqlCommand cmd =
new MySqlCommand("SELECT PKID FROM Users WHERE UserName = '" + username + "'", conn);

MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
reader.Read();
string strPkId = reader.GetString(0);
reader.Close();

But in the ProfileProvider that you find on Code Project, this is done in a transaction, so the MySqlCommand receives a third argument, with the transaction.

I presume that worked initially, but now, on Windows XP Home SP3, with MySql installed with mysql-installer-community-5.6.12.0.msi and mysql-connector-net-6.7.4.msi (oh, nevertheless Visual Studio 2005 reports using Connector Net 5.2), that does not work. reader.Read() returns false, and reader.GetString(0) throughs an exception that tells "Invalid attempt to access a field before calling Read()".

Same problem with cmd.ExecuteScalar(), that was used initially.

It works properly if called before opening a transaction, so with only two arguments to MySqlCommand, this is why I ask the question here : I imagine it is possible to read data inside a transaction, what must I modify in the code ?