Why is my call to LastInsertedId not returning the expected value?

Question

I have MySql Tables with autoinc ID columns, such as "director_id" here:

CREATE TABLE directors (
  director_id Integer NOT NULL AUTO_INCREMENT,
  first_name  VarChar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  middle_name VarChar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  last_name   VarChar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  suffix      VarChar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, 
  PRIMARY KEY (
      director_id
  )
)

I want to store the autoincremented director_id value in the movies_main Table.

So I try to assign the autoincremented value to an int variable:

long director_id = 0;

...in the call to LastInsertedId here (last line):

if (!alreadyExists)
{
    comm.Parameters.Clear();
    comm.CommandText = "INSERT INTO Directors " +
        "(first_name, middle_name, last_name, suffix) " +
        "VALUES " +
        "(@first_name, @middle_name, @last_name, @suffix)";
    comm.Parameters.AddWithValue("@first_name", directorNamePartsList[0]);
    comm.Parameters.AddWithValue("@middle_name", directorNamePartsList[1]);
    comm.Parameters.AddWithValue("@last_name", directorNamePartsList[2]);
    comm.Parameters.AddWithValue("@suffix", directorNamePartsList[3]);
    comm.ExecuteNonQuery();
    director_id = comm.LastInsertedId;
}

...and then assign it to the movies_main Table like so:

if (!alreadyExists)
{
    comm.Parameters.Clear();
    comm.CommandText = "INSERT INTO Movies_Main " +
    "(movie_title, mpaa_rating, imdb_rating, movie_length, director_id, 
      screenwriter_id, year_released) " +
    "VALUES " +
    "(@movie_title, @mpaa_rating, @imdb_rating, @movie_length, @director_id, 
      @screenwriter_id, @year_released)";
    comm.Parameters.AddWithValue("@movie_title", title);
    comm.Parameters.AddWithValue("@mpaa_rating", mpaa_rating);
    comm.Parameters.AddWithValue("@imdb_rating", Math.Round(imdb_rating, 1));
    comm.Parameters.AddWithValue("@movie_length", movie_length);
    comm.Parameters.AddWithValue("@director_id", director_id);
    comm.Parameters.AddWithValue("@screenwriter_id", screenwriter_id);
    comm.Parameters.AddWithValue("@year_released", year_released);
    comm.ExecuteNonQuery();
    movie_id = comm.LastInsertedId;
}

Yet the value assigned to the movies_main Table for director_id is always 0!

Why is LastInsertId (apparently) returning 0, and how can I get it to actually return the value its name claims it does? Will I have to resort to a "SELECT MAX(director_id)" query to actually get the value?

NOTE: The movie_id code does work! I get a non-zero value when assigning the result of the call to LastInsertedId to the movie_id variable, and it is added to other tables just fine. This code works as expected:

foreach (var gen_desc in genreList)
{
    long genreID = Convert.ToInt32(GetGenreIDForDescription(gen_desc));
    alreadyExists = PairAlreadyExistsInMoviesGenresM2Mtable(
                                           movie_id, genreID);
    if (!alreadyExists)
    {
        comm.Parameters.Clear();
        comm.CommandText = "INSERT INTO Movies_Genres " +
            "(movie_id, genre_id) " +
            "VALUES " +
            "(@movie_id, @genre_id)";
        comm.Parameters.AddWithValue("@movie_id", movie_id);
        comm.Parameters.AddWithValue("@genre_id", genreID);
        comm.ExecuteNonQuery();
    }    
}

This should not happen unless the _alreadyExists_ variable is true. Did you try to follow through the code and check if _alreadyExists_ is false? — Steve, Sep 10 '20 at 17:19
alreadyExists is false, otherwise the table would not be populated. The Directors Table has values. — B. Clay Shannon-B. Crow Raven, Sep 10 '20 at 17:22
Could you try with _SELECT LAST_INSERTED_ID()_ added after the main insert separating it with a semicolon and then calling ExecuteScalar? Example here https://stackoverflow.com/questions/405910/get-the-id-of-inserted-row-using-c-sharp — Steve, Sep 10 '20 at 17:25
Great, thanks!: Inserting "select last_insert_id();"; makes all the sense in the world and in fact makes the call to LastInsertedId seem less magical. Make it an answer, and I'll accept it. Still don't quite get why the movie_id assignment *did* work, though... — B. Clay Shannon-B. Crow Raven, Sep 10 '20 at 18:17

score 2 · Answer 1 · edited Sep 10 '20 at 19:35

An alternative way to LastInsertedId property from the MySqlCommand is the native MySql function LAST_INSERT_ID. We can call this function and get its return value adding a simple SELECT statement to your current command text. MySql supports batch statements and so, with a single server call we could execute more than one single command text.

if (!alreadyExists)
{
    comm.Parameters.Clear();
    comm.CommandText = "INSERT INTO Directors " +
        "(first_name, middle_name, last_name, suffix) " +
        "VALUES " +
        "(@first_name, @middle_name, @last_name, @suffix); " +  // semicolon to close the first statement
        "SELECT LAST_INSERT_ID()";
    comm.Parameters.AddWithValue("@first_name", directorNamePartsList[0]);
    comm.Parameters.AddWithValue("@middle_name", directorNamePartsList[1]);
    comm.Parameters.AddWithValue("@last_name", directorNamePartsList[2]);
    comm.Parameters.AddWithValue("@suffix", directorNamePartsList[3]);
    director_id = Convert.ToInt64(comm.ExecuteScalar());
}

Note that we can now use ExecuteScalar because we get back just one record with a single column.

Let me say however that I have tried to reproduce your problem with LastInsertedId. I have recreated your table and written a simple script in LinqPad trying to insert some fixed data in that table.

I have no problem with LastInsertedId property and I get the correct value. I have read that if you have more threads that are concurrently inserting records you could get some problems with that property but I have no proof of any kind of misbehaving

I've added a space at the end of the insert and before the select statement as this would cause an issue. — Simon Price, Sep 10 '20 at 19:36
What issue? The separator is the semicolon. You don't need any space — Steve, Sep 10 '20 at 19:47

Why is my call to LastInsertedId not returning the expected value?

1 Answers1