Strange exception executing command

From: Felix Lelchuk <FelixLelchuk(at)web.de>
Date: Fri Jun 22 2007 - 07:13:57 EDT

Hi all,
I'm new to this list, nevertheless I've (unfortunately) discovered some strange behaviour. When I try to create a MySQL user with GRANT command the ExecuteNonQuery function fails with System.FormatException (!?).
This is the code I'm using:

public InsertResult CreateUserAccount(UserAccount account, out Exception except) {

    MySqlCommand command = conn.CreateCommand();

    MySqlParameter p1 = new MySqlParameter("?username", MySqlDbType.VarChar, 16);     p1.Value = account.UserName;
    MySqlParameter p2 = new MySqlParameter("?fullname", MySqlDbType.VarChar, 100);     p2.Value = account.FullName;
    MySqlParameter p3 = new MySqlParameter("?password", MySqlDbType.LongText);     p3.Value = account.NewPassword;
    MySqlParameter p4 = new MySqlParameter("?isroot", MySqlDbType.Byte, 1);     p4.Value = account.IsRoot;

    bool committed = false;
    except = null;

    command.Parameters.AddRange(new MySqlParameter[] { p1, p2, p3, p4 });

    command.Transaction = command.Connection.BeginTransaction();     try
{

	command.Prepare();
	command.CommandText = "SELECT COUNT(*) FROM `accounts` WHERE `username`=?username";

	object result;
	result = command.ExecuteScalar();
	if (result == null || !Helper.IsNumber(result))
	    return InsertResult.ErrorUnkown;
	else if (Convert.ToUInt64(result) > 0)
	    return InsertResult.ErrorAccountUsernameAlreadyExists;

	command.CommandText = "SELECT COUNT(*) FROM `mysql`.`user` WHERE `User`=?username AND `Host`='%'";
	result = command.ExecuteScalar();
	if (result == null || !Helper.IsNumber(result))
	    return InsertResult.ErrorUnkown;
	else if (Convert.ToUInt64(result) > 0)
	    return InsertResult.ErrorSQLUsernameAlreadyExists;

	if (account.IsRoot)
	    command.CommandText = "GRANT ALL ON * TO ?username@'%' IDENTIFIED BY ?password";
	else
	    command.CommandText = "GRANT SELECT,UPDATE,INSERT ON * TO ?username@'%' IDENTIFIED BY ?password";

	command.ExecuteNonQuery();  // <--- System.FormatException

	command.CommandText = "INSERT INTO `accounts` (`username`, `fullname`, `password`, `isroot`) VALUES (?username, ?fullname, ?password, ?isroot)";

	if (command.ExecuteNonQuery() != 1)
	    return InsertResult.ErrorUnkown;

	account.Id = (ulong)command.LastInsertedId;

	command.Transaction.Commit();
	committed = true;

	return InsertResult.Done;

    }
    catch (Exception e)
{

	except = e;
	return InsertResult.ErrorThrown;

    }
    finally
{

	if (!committed)
	    command.Transaction.Rollback();
	command.Transaction.Dispose();
	command.Dispose();

    }

}

When I call the function passing a UserAccount object (isRoot = true) that exception occurs. I've also tried to replace ExceuteNonQuery with ExecuteReader or ExecuteScalar but without any success. Probably the problem is that ExecuteReader() positions the reader on the first result, while GRANT does not return any...

Well, I hope you can help me to get that code working. Thanks in advance.

Felix Lelchuk

---

Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! http://smartsurfer.web.de/?mc=100071&distributionid=000000000066

-- 
MySQL on .NET Mailing List
For list archives: 
http://lists.mysql.com/dotnet
To unsubscribe:    
http://lists.mysql.com/dotnet?unsub=lists@pantek.com

Received on Fri Jun 22 11:28:20 2007