We help IT Professionals succeed at work.

mysql C# error on insert statement. Says column has no value when in debug it shows a value.

Medium Priority
937 Views
Last Modified: 2013-12-16

I have the following code listed below. In the code I'm simply trying to insert a row into table message. When I step through in debug, all @ fields have values; however, when the  result = myCmd.ExecuteNonQuery(); is run, an exception is thrown saying that RoomID cannot be null. It shouldn't be null as I'm giving it a value by the OdbcParameter.

I am using the Mysql 5.1 ODBC driver through a system dsn to make the connection to the database. I can successfully retrieve data from this same table... I apparently cannot insert data though.

By the way, RoomId and all other id's in the table are BIGINT(20). The datatype the value is coming from in C# is a long.
public static readonly String insertMessage =
        @"insert into message
        (RoomID, UserID, ToUserID, Text, Color)
        values
        (@RoomID,@UserID,@ToUserID,@Text, @Color)";
 
public static void insertMessage(Message m)
    {        
        OdbcConnection MyConn = DBConnection.getDBConnection();
        int result = -1;
        try
        {
            MyConn.Open();
            OdbcCommand myCmd = new OdbcCommand(mySqlQueries.insertMessage);
            myCmd.Connection = MyConn;
            myCmd.CommandType = CommandType.Text;
            //(roomid, userid, touserid, text, color)
            OdbcParameter RoomID = new OdbcParameter("@RoomID", m.RoomId);
            OdbcParameter UserID = new OdbcParameter("@UserID", m.UserId);
            OdbcParameter ToUserID = new OdbcParameter("@ToUserID", m.ToUserId);
            OdbcParameter Text = new OdbcParameter("@Text", m.Text);
            OdbcParameter Color = new OdbcParameter("@Color", m.Color);
            myCmd.Parameters.Add(RoomID);
            myCmd.Parameters.Add(UserID);
            myCmd.Parameters.Add(ToUserID);
            myCmd.Parameters.Add(Text);
            myCmd.Parameters.Add(Color);
            
            result = myCmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
 
        }
        finally
        {
            try
            {
                if (MyConn != null) MyConn.Close();
            }
            finally { }
        }
    }

Open in new window

Comment
Watch Question

Commented:
Hello,

you have to specify a parameter type.

Try something like this:

OdbcParameter RoomID = new OdbcParameter("@RoomID",OdbcType.BigInt);
myCmd.Parameters.Add(RoomID).Value = m.RoomId;

Open in new window

Author

Commented:

Hmm...tried that verbatim and still getting error. It says column RoomID cannot be null.

Author

Commented:
My specific error:

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.30-community]Column 'RoomID' cannot be null
Commented:
Maybe there is a problem because of upper case letters. Try to convert all table fields names to lower case. You could also try this:

myCmd.Parameters.Add(RoomID).Value = Convert.ToInt32(m.RoomId);

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:

Man...tried that too and no dice.

Man, its the simple things that get me. Apparently MySql ODBC doesn't support named parameters in queries.

I changed my query from:

public static readonly String insertMessage =
        @"insert into message
        (RoomID, UserID, ToUserID, Text, Color)
        values
        (@RoomID,@UserID,@ToUserID,@Text, @Color)";

to

public static readonly String insertMessage =
        @"insert into message
        (RoomID, UserID, ToUserID, Text, Color)
        values
        (?,?,?,?,?)";

And it worked. Also worth mentioning is that it is now the order in which you add parameters to your command object that matters. You can still put names for your parameters in the OdbcParameter object, but they won't be used. You could also blank the names out and leave them with "".
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.