MySql table data always null values on INSERT

[DdoubleD]

A few days ago I decided to install MySql v1.2.17 and I downloaded a client class library: MySql.Data.dll v5.0.9.0 (by MySQL AB). I included the client classes inside my wrapper class and had it creating, dropping, inserting, and deleting tables..., or so I thought. I guess I never actually looked at the records it was creating from my test class.

Here is the problem: All my row data is always NULL values and I cannot figure out why this is! I broke out the wrapper code and placed base class calls into a simple set of statements as represented here:

public static void MySqlTestInsert()
        {
            string connStr = TestDbWrapper.BuildConnectionString(DbWrapperType.MySql);

            string insertCmd = "INSERT INTO TestDbWrapper " +
                "(FieldInt32, FieldVarchar_50, FieldBoolean, FieldDateTime) Values " +
                "(@FieldInt32, @FieldVarchar_50, @FieldBoolean, @FieldDateTime)";
            using (MySqlConnection conn = (MySqlConnection)DbWrapper.GetDbConnection(DbWrapperType.MySql, connStr))
            {
                try
                {
                    conn.Open();

                    using (MySqlCommand cmd = new MySqlCommand(insertCmd, conn))
                    {
                        cmd.Parameters.Add(new MySqlParameter("@FieldInt32", 10));
                        cmd.Parameters.Add(new MySqlParameter("@FieldVarchar_50", "some text..."));
                        cmd.Parameters.Add(new MySqlParameter("@FieldBoolean", true));
                        cmd.Parameters.Add(new MySqlParameter("@FieldDateTime", DateTime.Now));

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (DbException ex)
                {
                    Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

I don't get any errors, just NULL values in my columns. I have full schema privileges for the database and am able drop, create, and select just fine--though all the values are NULL because that is how they are being inserted.

Any ideas?

[Elbudster]

Have you tried using one of the other overloaded versions of Adding parameters where you actually specify the data type of what you are inserting? Might be worth a try.

Ex:

cmd.Parameters.Add(new MySqlParameter("@FieldInt32", SqlDbType.Int, 10));

[sknake]

:( MySQL is bad for your eyes.

I don't know anything about it but typically you add parameters like this:

conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@MemberId", SqlDbType.Int)).Value = MemberId;
          cmd.Parameters.Add(new SqlParameter("@MemberSname", SqlDbType.VarChar)).Value = MemberSname;
          cmd.Parameters.Add(new SqlParameter("@MemberFName", SqlDbType.VarChar)).Value = MemberFName;
          cmd.Parameters.Add(new SqlParameter("@Picture", SqlDbType.VarChar)).Value = Picture;
          cmd.Parameters.Add(new SqlParameter("@FName", SqlDbType.VarChar)).Value = FName;
          cmd.ExecuteNonQuery();
        }

Notice how the ctor is (Name, DataType) and the value is set from a result of the Add() method returning an instance of the parameter? I googled around a little and saw other mysql'ers doing it the same way but I don't have those libraries to test with.

[DdoubleD]

Thanks for the suggestions, and I did try them. Neither worked, so I looked a little more on the inet and decided to try using the "?" delimiter in place of the "@" symbol--that fixed the problem.

[sknake]

Oh. ODBC driver's don't support named parameters (i'm 99% sure) and only limited OleDb drivers do. Unless it is a microsoft driver usually @Param is not supported. I forgot about that.