Your ExecuteScalar command is returning Null (see the comment from @Richard-Deeming)
You should check for the null before attempting to cast the return value.
A neat way of doing that is as follows:
object result = cmdSELECT.ExecuteScalar();
result = (result == DBNull.Value) ? null : result;
int CustomerId = Convert.ToInt32(result);
Which works because
Convert.ToInt32(null)
returns 0
Solution adapted from Nikhil Vartak's response
here[
^]
Now to address the fundamental problem in your code.
string SQLselect = "SELECT @@IDENTITY FROM Customers";
SqlCommand cmdSELECT = new SqlCommand(SQLselect, connection);
is not going to return you the Id of the row you just inserted. Even if it did then what if another user had inserted a row in the split second between your two sql statements?
Change your SQL insert to return the id generated e.g. (assuming your identity column is called
ID
)
string SQLcreateQuery = "INSERT INTO Customers (CustomerId, FirstName, LastName, Address, City, State, ZipCode, EmailAddress) OUTPUT INSERTED.ID VALUES(@id, @fn, @ln, @ad, @ci, @st, @zc, @ea)";
You will then need to use
ExecuteScalar
to run the SQL not
ExecuteNonQuery
object result = cmdSQLcreateQuery.ExecuteScalar();
result = (result == DBNull.Value) ? null : result;
int CustomerId = Convert.ToInt32(result);