CODEWITHSUNDEEP
c#mysqldatabase
Kyle Meyer
Kyle Meyer

Reputation: 21

C#.NET - Cannot add or update a child row: a foreign key constraint fails

I noticed this question has been asked a lot but I couldn't find an explanation that seemed to address my specific situation. I have a C#.NET program that connects to a local database. The program is supposed to take a flat file and add rows of data to a table in the database if it isn't already there.

Here is the method I am using to add rows to the database

//Add a row to the database passed in as db
 public bool AddRow(OdbcConnection db)
 {
String sql = "INSERT INTO item "
           + "(item_id, invent_id, itemsize, color, curr_price, qoh) "
           + "VALUES( ?, ?, ?, ?, ?, ?)";
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add("@ID", OdbcType.Int).Value = this.Item_ID;
Command.Parameters.Add("@INVID", OdbcType.Int).Value = this.Invent_id;
Command.Parameters.Add("@SZ", OdbcType.VarChar).Value = this.Itemsize;
Command.Parameters.Add("@COL", OdbcType.VarChar).Value = this.Color;
Command.Parameters.Add("@PR", OdbcType.Double).Value = (double)this.Curr_price;
Command.Parameters.Add("@QOH", OdbcType.Int).Value = this.Qoh;

int result = Command.ExecuteNonQuery();  //Returns 1 if successful
if (result > 0)
    return true;  //Was successful in adding
else
    return false;  //failed to add
} //end of AddRow

Once the method reaches the Command.ExecuteNonQuery() it throws the following error:

Additional information: ERROR [HY000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.10-log]Cannot add or update a child row: a foreign key constraint fails (labdb.item, CONSTRAINT item_ibfk_1 FOREIGN KEY (invent_id) REFERENCES inventory (invent_id))

I know the idea here is that we are adding a row to the Item table. I know that this table has a relationship with the Inventory table where the Invent_ID attribute is a foreign key. This value seems to be what is causing the error, though it should conform to all constraints.

Can anyone suggest to me what I might be overlooking? I should state that this class is my first interaction with C# and most of the program is a template provided by the class.

Upvotes: 2

Views: 1157

Answers (1)

Ehsan
Ehsan

Reputation: 367

You are getting that error because you are passing the wrong order of parameters. Instead of '?' in your INSERT statement you need to pass the parameter name as you defined them: 

{
        String sql = "INSERT INTO item "
                   + "(item_id, invent_id, itemsize, color, curr_price, qoh) "
                   + "VALUES( @ID, @INVID, @SZ,@COL, @PR, @QOH)";
        OdbcCommand Command = new OdbcCommand(sql, db);
        Command.Parameters.Add("@ID", OdbcType.Int).Value = this.Item_ID;
        Command.Parameters.Add("@INVID", OdbcType.Int).Value = this.Invent_id;
        Command.Parameters.Add("@SZ", OdbcType.VarChar).Value = this.Itemsize;
        Command.Parameters.Add("@COL", OdbcType.VarChar).Value = this.Color;
        Command.Parameters.Add("@PR", OdbcType.Double).Value = (double)this.Curr_price;
        Command.Parameters.Add("@QOH", OdbcType.Int).Value = this.Qoh;

        int result = Command.ExecuteNonQuery();  //Returns 1 if successful
        if (result > 0)
            return true;  //Was successful in adding
        else
            return false;  //failed to add
    } //end of AddRow

Upvotes: 1

Related Questions