Error allocating memory from MySql ODBC 5.1 driver in C # application in insert statement

Question

I have a .NET Wndows application in C #. This is a simple Windows application using the MySql 5.1 database community version. I downloaded the ODBC MySql driver and created dsn in my database on my local machine. In my application, I can execute type queries without problems, but when I do the insert job (not that I tried to do any others), I get the following error:

{"ERROR [HY001] [MySQL] [ODBC 5.1 driver] [mysqld-5.0.27-community-nt] Error allocating memory"}

I am running on a computer running Windows XP. My machine has 1 GB of memory. Does 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

This is because some fields are null, I passed them as null, where they should be passed as DBNull.Value. For all fields that allow null, you must check the null value and if null is found, DBNull.Value must be passed.

Answer 2

Just for completeness, the Chinjoo SQL statement is likely to be something like this:

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

This is called a parameterized insert, where each question mark represents one of its parameters. In this simple example, the order of the parameters in the parameter collection in the code should match the order of the column names in the SQL statement.

Although it is less elegant than using a function, a fix for its zero problem would look something like this for one of its parameters:

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

replaced by

// if the value "null" returns DBNull, otherwise 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) I need an internal cast to type object, because otherwise the compiler is not sure which type the operator should return ?:

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

No criticism of Jinjo was implied at all - his publication helped me! Just thought that I would share for the less experienced. I am by no means an expert, so take everything I say with salt.