Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

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


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

0
baijajusav
Asked:
baijajusav
  • 4
  • 2
2 Solutions
 
zstapicCommented:
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

0
 
baijajusavAuthor Commented:

Hmm...tried that verbatim and still getting error. It says column RoomID cannot be null.
0
 
baijajusavAuthor Commented:
My specific error:

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.30-community]Column 'RoomID' cannot be null
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
zstapicCommented:
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

0
 
baijajusavAuthor Commented:

Man...tried that too and no dice.
0
 
baijajusavAuthor Commented:

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 "".
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now