Memory allocation error from MySql ODBC 5.1 driver in C# application on insert statement

Question

I have a .NET Wndows application in C#. It's a simple Windows application that is using the MySql 5.1 database community edition. I've downloaded the MySql ODBC driver and have created a dsn to my database on my local machine. On my application, I can perform get type queries without problems, but when I execute a given insert statement (not that I've tried doing any others), I get the following error:

{"ERROR [HY001] [MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Memory allocation error"}

I'm running on a Windows XP machine. My machine has 1 GB of memory. Anyone have any ideas? See code below

OdbcConnection MyConn = DBConnection.getDBConnection();
    int result = -1;
    try
    {
        MyConn.Open();
        OdbcCommand myCmd = new OdbcCommand();
        myCmd.Connection = MyConn;
        myCmd.CommandType = CommandType.Text;
        OdbcParameter userName = new OdbcParameter("@UserName", u.UserName);
        OdbcParameter password = new OdbcParameter("@Password", u.Password);
        OdbcParameter firstName = new OdbcParameter("@FirstName", u.FirstName);
        OdbcParameter LastName = new OdbcParameter("@LastName", u.LastName);
        OdbcParameter sex = new OdbcParameter("@sex", u.Sex);
        myCmd.Parameters.Add(userName);
        myCmd.Parameters.Add(password);
        myCmd.Parameters.Add(firstName);
        myCmd.Parameters.Add(LastName);
        myCmd.Parameters.Add(sex);
        myCmd.CommandText = mySqlQueries.insertChatUser;
        result = myCmd.ExecuteNonQuery();
    }
    catch (Exception e)
    {
             //{"ERROR [HY001] [MySQL][ODBC 5.1 Driver][mysqld-5.0.27-community-nt]Memory  
             // allocation error"} EXCEPTION ALWAYS THROWN HERE 
    }
    finally
    {
        try
        {
            if (MyConn != null) MyConn.Close();
        }
        finally { }
    }

Answer 1

It was because some fields accept null, I had passed them as null where they should be passed as DBNull.Value. For all the fields which allow null should be checked for null and if found null, DBNull.Value should be passed.

Answer 2

Just for the sake of completeness, Chinjoo's SQL statement would likely be something like this:

mySqlQueries.insertChatUser = "insert into ChatUsers (UserName, Password, FirstName, LastName, sex) values (?,?,?,?,?);";

This is known as a parameterized insert where each question mark represents one of his parameters. In this simple example the order of the parameters in the parameter collection in code must match the order of the column names in the SQL statement.

While less elegant than using a function, the fix for his null problem would look something like this for one of his parameters:

OdbcParameter LastName = new OdbcParameter("@LastName", u.LastName);

is replaced with

// if the value is "null" return DBNull, else just the value
OdbcParameter LastName = new OdbcParameter("@LastName", 
(u.LastName == null) ? System.DBNull.Value : (object)u.LastName);

At least in my code (which is slightly different) the inner cast to type object is required since otherwise the compiler isn't sure what type the ?: operator should return.

Hope this helps anyone who is relatively new to parameterization, etc.

No criticism of Chinjoo implied at all--his posting helped me out! Just thought I'd share for the less-experienced. I'm by no means expert so take everything I say with a grain of salt.