Following SQL statement is creating an error with message : "Message: Fatal error encountered during command execution." "Inner exception: Parameter '@LastUserID' must be defined."
If I directly use LAST_INSERT_ID() instead of LastUserID, it always returns zero (hence fails at second insert) when executed like this.
I don't see my syntax is different than in mySQL document.
Could some one help me ?
string Query = @"INSERT INTO login (" +
"LOGIN_EMAIL," +
"LOGIN_PASSWORD," +
"LOGIN_SALT," +
"LOGIN_LAST_LOGIN_DATE," +
// "LOGIN_LAST_LOGIN_LOCATION," +
"LOGIN_ACCOUNT_STATUS," +
"LOGIN_LOGIN_ATTEMPTS," +
"LOGIN_CREATED_DATE) " +
"VALUES (" +
"@Parameter2," +
"@Parameter3," +
"@Parameter4," +
"@Parameter5," +
// "@Parameter6," +
"@Parameter6," +
"@Parameter7," +
"@Parameter8); " +
"SET @LastUserID = LAST_INSERT_ID(); " +
"INSERT INTO user_role (" +
"USER_ROLE_USER_ID," +
"USER_ROLE_ROLE," +
"USER_ROLE_STATUS," +
"USER_ROLE_CREATED_DATE) " +
"SELECT " +
"@LastUserID," +
"@Parameter9," +
"@Parameter10," +
"@Parameter11 " +
"FROM dual WHERE NOT EXISTS (SELECT USER_ROLE_USER_ID FROM user_role " +
"WHERE USER_ROLE_USER_ID = @LastUserID AND USER_ROLE_ROLE = @Parameter9)";
MySqlCommand oCommand = new MySqlCommand(Query, oMySQLConnecion);
oCommand.Transaction = tr;
Simple fix : Replace "$LastUserID" with "$'LastUserID'". The ephostophy makes the difference.