DaniWeb Forum Index > Software Development > C#

C# RSS

MySql table data always null values on INSERT

Please support our C# advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jul 2009

Posts: 920

Reputation: DdoubleD is a jewel in the rough

Solved Threads: 147

DdoubleD

Offline

Posting Shark

MySql table data always null values on INSERT

31 Days Ago

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
        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();
                }
            }
        }
        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?

•

Join Date: Sep 2004

Posts: 13

Reputation: Elbudster is an unknown quantity at this point

Solved Threads: 1

Elbudster

Offline

Newbie Poster

31 Days Ago

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
cmd.Parameters.Add(new MySqlParameter("@FieldInt32", SqlDbType.Int, 10));
cmd.Parameters.Add(new MySqlParameter("@FieldInt32", SqlDbType.Int, 10));

•

Join Date: Feb 2009

Posts: 3,264

Reputation: sknake has much to be proud of

Solved Threads: 582

sknake

Offline

.NET Enthusiast

31 Days Ago

MySQL is bad for your eyes.

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
        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();
        }
        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.

Last edited by sknake; 31 Days Ago at 2:08 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2009

Posts: 920

Reputation: DdoubleD is a jewel in the rough

Solved Threads: 147

DdoubleD

Offline

Posting Shark

31 Days Ago

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.

•

Join Date: Feb 2009

Posts: 3,264

Reputation: sknake has much to be proud of

Solved Threads: 582

sknake

Offline

.NET Enthusiast

31 Days Ago

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.

Scott Knake
Custom Software Development
Apex Software, Inc.