CODEWITHSUNDEEP
c#mysql
Exclusified
Exclusified

Reputation: 35

Cannot add or update a child row: a foreign key constraint fails - stops at 1 insertion

What I'm trying to do is that, once I insert the data, it won't insert the same user TWICE due to having multiple trials. However, I'm getting the above error. My insert statement for inserting into user is

bool userisExist = checkUser(personName, age, gender, handedness, comments);
                if (!userisExist)
                {
                  MySqlCommand insertUser = new MySqlCommand();
                  insertUser.Connection = c;
                  insertUser.CommandText = "INSERT INTO user (userID, name, age, gender, handedness, comments) VALUES(NULL, @personName , @age , @gender , @handedness , @comments)";


                  insertUser.Parameters.Add("@personName", MySqlDbType.VarChar).Value = personName;
                  insertUser.Parameters.Add("@age", MySqlDbType.Int64).Value = age;
                  insertUser.Parameters.Add("@gender", MySqlDbType.VarChar).Value = gender;
                  insertUser.Parameters.Add("@handedness", MySqlDbType.VarChar).Value = handedness;
                  insertUser.Parameters.Add("@comments", MySqlDbType.VarChar).Value = comments;
                  insertUser.ExecuteNonQuery();

                  Console.WriteLine("User Data has been inserted.");
              }
              else
              {
                  Console.WriteLine("User Data already exist.");
              }

And my insert statement for gazeperiod is 

insertGazePeriod.Connection = c;
              insertGazePeriod.CommandText = "INSERT INTO gazeperiod (gazeID,  duration, userID) VALUES(NULL, @duration , LAST_INSERT_ID())";


              insertGazePeriod.Parameters.Add("@duration", MySqlDbType.Int64).Value = duration;

              insertGazePeriod.ExecuteNonQuery();
              Console.WriteLine("Gaze Period data has been inserted.");
                //END INSERT gazeperiod 
            }

And my checkUser is

private bool checkUser(String personName, String age, String gender, String handedness, String comments)
  {
       try
        {
            int returnValue = -1;
            MySqlCommand selectUser = new MySqlCommand();
            selectUser.Connection = c;
            selectUser.CommandText = "SELECT userID from user WHERE name= @personName AND age = @age AND gender = @gender AND handedness = @handedness AND comments = @comments";

            selectUser.CommandType = CommandType.Text;

            selectUser.Parameters.Add("@personName", MySqlDbType.VarChar).Value = personName;
            selectUser.Parameters.Add("@age", MySqlDbType.Int64).Value = age;
            selectUser.Parameters.Add("@gender", MySqlDbType.VarChar).Value = gender;
            selectUser.Parameters.Add("@handedness", MySqlDbType.VarChar).Value = handedness;
            selectUser.Parameters.Add("@comments", MySqlDbType.VarChar).Value = comments;

             returnValue = (int)selectUser.ExecuteScalar();
             Console.WriteLine("returnValue-" +returnValue);
             return true;

        }
        catch (Exception e)
        {
            Console.WriteLine("returnValue Exception-" + e.ToString());
            return false;
        }
  }

Right now, it's only printing the first user, and stops to print the other data as there is already a user with the current ID. I don't want multiple insertions of the same user.

Upvotes: 2

Views: 90

Answers (1)

KaeL
KaeL

Reputation: 3659

I think the problem is in your INSERT sql statement:

insertUser.CommandText = "INSERT INTO user (userID, name, age, gender, handedness, comments) VALUES(NULL, @personName , @age , @gender , @handedness , @comments)";

I'm pretty sure you do not want to insert NULL in the userID column. If it is an AUTO_INCREMENT column, you should leave that behind.

Upvotes: 1

Related Questions