PK of Table 1 as a FK in Table 2 using SQL and C# - breaks with select_identity(), Object cannot be cast from DBNull to other types

Question

C# and SQL beginner here. I have two tables using primary key of table1 as a foreign key in table2. To get an ID, I'm using SELECT MAX and everything works as I want (code below):

        {
            con1.Open();

            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "INSERT INTO dbo.testDriver(name) Values(@name)";
            cmd1.Connection = con1;
            cmd1.Parameters.AddWithValue("@name", name.Text);
            cmd1.ExecuteNonQuery();
            cmd1.Parameters.Clear();

            cmd1.CommandText = "SELECT MAX(PkDriverID) FROM dbo.testDriver";
            int FkDriverID = Convert.ToInt32(cmd1.ExecuteScalar());

            cmd1.CommandText = "INSERT INTO dbo.testCar(brand, model, FkDriverID) Values(@brand, @model, @FkDriverID)";
            cmd1.Connection = con1;
            cmd1.Parameters.AddWithValue("@brand", brand.Text);
            cmd1.Parameters.AddWithValue("@model", model.Text);
            cmd1.Parameters.AddWithValue("@FkDriverID", FkDriverID);
            cmd1.ExecuteNonQuery();
            cmd1.Parameters.Clear();

            con1.Close();
        }
        {
            Response.Redirect(this.Request.Url.ToString());
        }

But colleagues are telling me I should use SCOPE_IDENTITY() instead of SELECT MAX. I do understand why should I use it (in case of multiple users, etc.) but after replacing SELECT MAX with it, I get an error:

Object cannot be cast from DBNull to other types.

In my database, PKs and FK have int datatype, Nulls are not allowed and with SELECT MAX it just works. So what am I doing wrong here?

Answer 1

In race conditions Select MAX may return ID generated by someone else right after your insert. Moreover, identity does not have to grow eternally - it may be reseeded or even turned back to negatives. Also it does inappropriate additional select work.

SCOPE_IDENTITY is the only right way for your (and for most of all other's) case.

Furthermore, I'd recommend to put two queries into single one and surround both inserts with a transaction. This way you'll be able to rollback changes to Driver if something went wrong with a Car.